using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;
using System.Xml.Serialization;
//using System.Data;
using System.Data.SqlClient;
namespace zSharp2010.XMLToDB
{
public class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime DOB { get; set; }
}
public class cXMLToDB
{
public cXMLToDB(string serverName, string dbaseName)
{
List<Employee> emps = new List<Employee>();
Employee emp = new Employee();
emp.ID = 1;
emp.Name = "George";
emp.DOB = Convert.ToDateTime("1/1/1990");
emps.Add(emp);
emp = new Employee();
emp.ID = 2;
emp.Name = "John";
emp.DOB = Convert.ToDateTime("2/2/1990");
emps.Add(emp);
emp = new Employee();
emp.ID = 3;
emp.Name = "Thomas";
emp.DOB = Convert.ToDateTime("3/3/1990");
emps.Add(emp);
StringWriter sw = GetDataInXMLFormat(emps);
SqlConnection cnn = new SqlConnection("SERVER=" + serverName + ";Integrated Security=true;DATABASE=" + dbaseName);
SqlCommand cmd = new SqlCommand("zTest", cnn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@xmlData", sw.ToString());
cnn.Open ();
SqlDataReader dr = cmd.ExecuteReader();
cnn.Close();
//dr can then be used
}
private StringWriter GetDataInXMLFormat(List<Employee> emps)
{
XmlSerializer xSerializer = new XmlSerializer(emps.GetType());
StringWriter sw = new StringWriter();
XmlTextWriter tw = new XmlTextWriter(sw);
xSerializer.Serialize(tw, emps);
return sw;
}
}
}
___________________________________________
The Stored Procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE zTest
@xmlData nVarchar (max)
AS
BEGIN
DECLARE @hdoc INT
CREATE TABLE #Emp (ID Int, Name VarChar(50), DOB DateTime)
IF NOT (@xmlData IS NULL)
BEGIN
EXEC @xmlData = sp_xml_preparedocument @hdoc OUTPUT, @xmlData
IF @xmlData <> 0
BEGIN RAISERROR('System was unable to parse the xml data', 16, 1) RETURN END
END
INSERT INTO #Emp (ID, Name, DOB)
SELECT ID, Name, DOB
FROM OPENXML(@hdoc, '/ArrayOfEmployee/Employee', 2)
WITH (ID INT, Name VARCHAR(50), DOB DateTime) --The fields from XML are case sensitive
SELECT *
FROM #Emp
DROP TABLE #Emp
END
GO
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;
using System.Xml.Serialization;
//using System.Data;
using System.Data.SqlClient;
namespace zSharp2010.XMLToDB
{
public class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime DOB { get; set; }
}
public class cXMLToDB
{
public cXMLToDB(string serverName, string dbaseName)
{
List<Employee> emps = new List<Employee>();
Employee emp = new Employee();
emp.ID = 1;
emp.Name = "George";
emp.DOB = Convert.ToDateTime("1/1/1990");
emps.Add(emp);
emp = new Employee();
emp.ID = 2;
emp.Name = "John";
emp.DOB = Convert.ToDateTime("2/2/1990");
emps.Add(emp);
emp = new Employee();
emp.ID = 3;
emp.Name = "Thomas";
emp.DOB = Convert.ToDateTime("3/3/1990");
emps.Add(emp);
StringWriter sw = GetDataInXMLFormat(emps);
SqlConnection cnn = new SqlConnection("SERVER=" + serverName + ";Integrated Security=true;DATABASE=" + dbaseName);
SqlCommand cmd = new SqlCommand("zTest", cnn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@xmlData", sw.ToString());
cnn.Open ();
SqlDataReader dr = cmd.ExecuteReader();
cnn.Close();
//dr can then be used
}
private StringWriter GetDataInXMLFormat(List<Employee> emps)
{
XmlSerializer xSerializer = new XmlSerializer(emps.GetType());
StringWriter sw = new StringWriter();
XmlTextWriter tw = new XmlTextWriter(sw);
xSerializer.Serialize(tw, emps);
return sw;
}
}
}
___________________________________________
The Stored Procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE zTest
@xmlData nVarchar (max)
AS
BEGIN
DECLARE @hdoc INT
CREATE TABLE #Emp (ID Int, Name VarChar(50), DOB DateTime)
IF NOT (@xmlData IS NULL)
BEGIN
EXEC @xmlData = sp_xml_preparedocument @hdoc OUTPUT, @xmlData
IF @xmlData <> 0
BEGIN RAISERROR('System was unable to parse the xml data', 16, 1) RETURN END
END
INSERT INTO #Emp (ID, Name, DOB)
SELECT ID, Name, DOB
FROM OPENXML(@hdoc, '/ArrayOfEmployee/Employee', 2)
WITH (ID INT, Name VARCHAR(50), DOB DateTime) --The fields from XML are case sensitive
SELECT *
FROM #Emp
DROP TABLE #Emp
END
GO
Add a comment