Michael J. Swart

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#).