You are here

sql csv xml

SQL: comma separated values to table

With MS-SQL it is possible to convert a string with comma separated values (e.g "one,two,three,four") to a table with multiple records.

The trick is to convert the string to XML and then use the XML nodes and value methods to get the list.

DECLARE @string varchar(max) = 'one,two,three,a,b,c'
DECLARE @xml XML = CONVERT(xml, '<root><row>' + REPLACE(@string, ',', '</row><row>') + '</row></root>')

SELECT c.value('.', 'varchar(255)')
FROM @xml.nodes('/root/row') T(c)

Note that you are free to choose the "<root>" and "<row>" tag names. Likewise for the "T(c)" definition, I just used these because they are used in the Microsoft examples.

Subscribe to RSS - sql csv xml