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:
DECLARE @xmlData XML
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)
ORDER BY [Text]
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.
