Working with comma separated string using XML


SQL DBAs and Database Developers often require parsing or splitting the comma separated string into rows. I guess, you might have already written or used any Split FUNCTION in your database. Here, I would like to demonstrate a different technique to split the delimited string; I used this technique while working with LINQ technology.


IF OBJECT_ID('Table1') IS NOT NULL
      DROP TABLE Table1
GO

CREATE TABLE Table1(KeyID INT)
GO

INSERT Table1
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
GO

DECLARE @p2 VARCHAR(100)
SET @p2= '1,3'

SELECT      *
FROM  Table1
WHERE KeyID IN (
            SELECT  Split.PId
            FROM  (SELECT CAST('<r>' + REPLACE(@p2, ',', '</r><r>') + '</r>' AS XML) AS P ) xmlP
            CROSS APPLY (SELECT P.PId.value('.', 'varchar(10)') PId FROM xmlP.P.nodes('r') AS P(PId) ) Split
)
GO
Advertisements

One thought on “Working with comma separated string using XML

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s