1. 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
    0

    Add a comment

Blog Archive
Topics
Topics
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.