Thursday, 29 January 2009

Passing XML as arguments to a Sql Server stored procedure

Since the release of Sql Server 2005, Microsoft added a whole raft of features for storing, reading and manipulating XML data to their enterprise database platform.

If you are writing a stored procedure and want to pass a collection of data as an argument, one way to achieve this is to pass the data in as an XML string, whose contents can then be parsed into a rowset, like so:


SET @xmlData = '<root><data id="1" text="Dave"/><data id="2" text="Neil"/><data id="3" text="Anthony"/></root>'

SELECT    ParamValues.myData.value('./@id','int') as ID, 
          ParamValues.myData.value('./@text','nvarchar(50)') as [Text] 
FROM     @xmlData.nodes('/root/data') as ParamValues(myData) 

This results in the following output:

3 Anthony
1 Dave
2 Neil

The resultant rowset can be used as part of an IN clause or can be iterated through via a cursor or as part of an INSERT...SELECT statement, for example.

This approach can also be used in Sql Server 2008.