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