This may be common knowledge to those among us that are handy with XML (I'm not), but since I just uncovered this nice little trick I thought I'd pass it along either way. It is a very easy way to process a CSV string and turn it into multiple rows:
declare @idoc int -- the handle for the XML document
declare @csv varchar(8000) -- the CSV we are being passed
set @csv = '1,2,3,7,4,8,10' -- our list of CSV values
-- With a simple edit of the CSV string:
set @csv = ''
-- We can now do this:
EXEC sp_xml_preparedocument @idoc OUTPUT, @csv
-- And then:
SELECT *
FROM OPENXML (@idoc, '/root/id',1)
WITH (value int)
This raises some interesting possibilities about ways we can process regular text strings (like CSV's) by using simple
replace techniques to turn that string into an XML value. It is certainly shorter that the usual looping technique to split
a CSV string into each value and then insert that value into a temp table or a table variable. I'm not sure if it's more or
less efficient, though.Anyone else have any other nice tricks like this?
