nodes | Michael J. Swart

September 29, 2008

Passing XML Data from the App to the Database

Filed under: Technical Articles — Tags: "sql server 2005", "sql server 2008", "sql server", nodes, shredding, shredding xml, xml, xml shredding — Michael J. Swart @ 5:27 pm

Passing xml data from the app to the database

So I recently talked about how SQL Server can take xml data and shred it into something more relational.

I wanted to follow it up with this post and show an example of how to use ADO.Net to make use of SQL’s xml data type and pass it to an application.

First say that you have a stored procedure with this signature:

CREATE PROCEDURE ShredThis(@p1 XML)
AS

Then you can use that procedure from within C# code with something like this:

private void button1_Click(object sender, EventArgs e)
{
    SqlCommand cmd = null;
    try
    {
        cmd = new SqlCommand();
        cmd.CommandText = "ShredThis";
        cmd.CommandType = CommandType.StoredProcedure;
        XmlReader xr = XmlReader.Create(new StringReader(@"
        <book author=""J K Rowling"" Title=""Harry Potter"">
            <chapter number =""1"" name=""the boy who lived""><chapter>
            <chapter number =""2"" name=""diagon alley""><chapter>
            <chapter number =""3"" name=""quidditch""><chapter>
            <chapter number =""4"" name=""dementors""><chapter>
            <chapter number =""5"" name=""voldemort's back""><chapter>
        <book>"));
        cmd.Parameters.Add("@p1", SqlDbType.Xml).Value = new SqlXml(xr);
        cmd.Connection = new SqlConnection(@"Data Source=mswart\SQL2008;Initial Catalog=tempdb;Trusted_Connection=True");
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
    finally
    {
        if (cmd != null && cmd.Connection != null && (cmd.Connection.State == ConnectionState.Open))
        {
            cmd.Connection.Close();
        }
    }
}

You can cut and paste this into any app and adjust it to how you need it. Remember a couple things:

Don’t forget the using clauses to make sure the code compiles
Check out the SqlXml class.
The SqlDbType enumeration has an Xml entry. This is what it’s used for

Cheers,

Michael J. Swart

-- Comments (0)

A better XML shredding example

Filed under: SQL Scripts,Technical Articles — Tags: "sql server 2005", "sql server 2008", "sql server", nodes, shredding, shredding xml, xml, xml shredding — Michael J. Swart @ 12:41 pm

Passing data into the database as a set has always been a challenge. There have been a number of approaches used for various purposes. And those approaches are discussed in many different places already.

If you only have to support SQL 2008, then table valued parameters are definitely the way to go.

If you have to support SQL 2005 (like myself) then other methods have to be used. Such as by parsing CSVs. Or my new favorite method of shredding xml.

Shredding XML
I like this method because it maintains data/script separation which is important from a security point of view.

One way of shredding xml is by using the nodes() method of the xml data type. The official documentation is here, but it wasn’t clear how to use this method for the business case I mentioned.

Here’s my example which I use as a template. Maybe you’ll find it useful too:

DECLARE @data XML
SET @data = '<root xmlns="http: //www.MySampleCompany.com">
 <book author="J K Rowling" title="Philosopher''s Stone">
  <chapter number="1" name="the boy who lived"/>
  <chapter number="2" name="the rest"/>
 </book>
</root>';

WITH XMLNAMESPACES ('http: //www.MySampleCompany.com' AS MY) 
SELECT 
   chapters.node.value('../@title', 'nvarchar(50)') AS bookTitle,
   chapters.node.value('../@author', 'nvarchar(50)') AS bookAuthor,
   chapters.node.value('@number', 'int') AS chapterNumber,
   chapters.node.value('@name', 'nvarchar(50)') AS chapterName
FROM @data.nodes('//MY:chapter') AS chapters(node)

The results look like this

bookTitle             bookAuthor  chapterNumber chapterName 
--------------------- ----------- ------------- ------------------ 
Philosopher's Stone   J K Rowling 1             the boy who lived 
Philosopher's Stone   J K Rowling 2             the rest

Also, if you don’t use namespaces with your XML, you just omit the WITH clause.

Update!
Check out this post to see how this feature looks from the app side (using c#).

-- Comments (2)