Michael J. Swart

September 29, 2008

A better XML shredding example

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:

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"/>

WITH XMLNAMESPACES ('http: //www.MySampleCompany.com' AS MY) 
   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.

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


  1. Thanks for the xml shredding solution you have up there, it actually helped with a problem I faced for two days. Thank you thank you.

    Comment by Bernie — December 2, 2011 @ 12:49 pm

  2. Thanks Bernie! I love that kind of feedback!
    I certainly learn a lot more from peoples’ websites than I teach. So it’s nice to know I’m giving back (at least a little bit).

    Comment by Michael J. Swart — December 2, 2011 @ 12:58 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress