As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column. Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this. Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.
Example:
I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.
Proposed Solution:
Convert VARCHAR variable to XML as follows:
SET @xmlIDs = '' ' + REPLACE(@str, ',', ' ') + ' ' + '
So this statement will generate XML from VARCHAR value as follows:
1 6 7 8 20
So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:
SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)
This will give me result set as: ( as separate table)
So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.
So my Procedure looks like:
CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
@str VARCHAR(100)
AS
DECLARE @XmlIDs XML
SET @xmlIDs = '
' + REPLACE(@str, ',', ' ') + ' ' +
' '
UPDATE TableName
SET Flag = 1
WHERE ID IN(
SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)
)
So, it is very easy to read values from Comma separated value.
Let me know if it helps you.