My Technical Notes

Wednesday, 30 January 2013

Sending Xml to a Stored Procedure in SQL Server 2005

To send Xml to a stored procedure (hereinafter "sp"), the sp must accept an XML parameter. e.g:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- test sp
alter PROCEDURE dbo.MyTestSp(@myXmlData XML)
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT 
        p.value('ID[1]', 'varchar(50)') AS ID, 
        p.value('Name[1]', 'varchar(50)') AS Name 
    FROM 
        @myXmlData.nodes('/Persons/Person') AS Persons(p)

END
GO

Thereafter the C# app must be able to call this stored procedure and send the Xml data in. For this we use the System.Xml.Linq to create an XmlReader instance:


using (SqlConnection con = new SqlConnection(@"Server=.\SQLEXPRESS;Database=TestDB;Integrated Security=True;"))
{
    SqlCommand cmd = new SqlCommand("dbo.MyTestSp", con);
    cmd.CommandType = System.Data.CommandType.StoredProcedure;

    XDocument xmlDoc = new XDocument(
        new XElement("Persons",
            new XElement("Person", new XElement("ID", "1"), new XElement("Name", "Shoyeb")),
            new XElement("Person", new XElement("ID", "2"), new XElement("Name", "Nasir"))));

    // xml is:
    // <Persons>               
    //   <Person>
    //     <ID>1</ID>
    //     <Name>Shoyeb</Name>
    //   </Person>
    //   <Person>
    //     <ID>2</ID>
    //     <Name>Nasir</Name>
    //   </Person>
    // </Persons>

    cmd.Parameters.Add(new SqlParameter("myXmlData", new SqlXml(xmlDoc.CreateReader())));

    con.Open();
    var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(string.Format("ID: {0}, Name: {1}", reader[0], reader[1]));
    }

    Console.ReadKey();
}

No comments: