Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Using OPENXML to process CSV (comma-separated value) strings

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?

 

Print | posted on Sunday, January 25, 2004 10:01 AM | Filed Under [ T-SQL ]

Feedback

Gravatar

# re: Using OPENXML to process CSV (comma-separated value) strings

obviously we don't NEED XML -- I never tried to imply that. It is just another way to parse a CSV into multiple rows, as opposed to looping through a string or joing to a tally table. obvisouly i'm familiar with other methods of putting CSV's into multiple rows since I've posted them many times myself !

Hopefully, you read the whole post and not just the subject, and saw that it was just temporarily put into XML so it can immediately be transformed back into a regular SQL Server recordset. At no point was the "goal" to generate an XML file !!!
1/25/2004 4:04 PM | Jeff
Gravatar

# re: Using OPENXML to process CSV (comma-separated value) strings

"Hopefully, you read the whole post and not just the subject, and saw that it was just temporarily put into XML so it can immediately be transformed back into a regular SQL Server recordset"

I did, and I can't help but wonder what use that would be. It's certainly no easier to create XML and have SQL Server parse it than it is to parse the CSV directly and create a resultset out of it. I don't see any advantage to putting it into XML at all for something this simple.
1/25/2004 7:38 PM | robvolk
Gravatar

# re: Using OPENXML to process CSV (comma-separated value) strings

Ok. thanks for the feedback !
1/25/2004 8:10 PM | Jeff
Gravatar

# re: Using OPENXML to process CSV (comma-separated value) strings

Instead of passing in VARCHAR(8000) parameters I am now passing in TEXT parameters that I then load up into a standard function that extracts the rows into a table. This works much better than trying to parse a CSV that is limited to 8000 characters.
1/26/2004 8:39 AM | Adam Weigert
Gravatar

# re: Using OPENXML to process CSV (comma-separated value) strings

This is neat trick. You also need to ensure that all the XML escape characters are parsed.
9/20/2004 8:31 AM | Amitabh Mathur
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET