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.

This query didn't work when I called it from PHP. I was getting the following error:

SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

To fix this, I had to prepend the following to the query:

SET QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET CONCAT_NULL_YIELDS_NULL ON