Split a comma delimited column values into multiple rows with XML 2


DECLARE @s VARCHAR(50)='1,2,13,4'
DECLARE @myXML AS XML = N'<H><r>' + REPLACE(@s, ',', '</r><r>') + '</r></H>'

SELECT Vals.id.value('.', 'NVARCHAR(50)') AS val
FROM @myXML.nodes('/H/r') AS Vals(id)
DECLARE @s VARCHAR(50)= 'abcd,efg,hij,klmn,opqrstu,vwxyz'

DECLARE @myXML AS XML = N'<H><r>' + REPLACE(@s, ',', '</r><r>') + '</r></H>'

Select val FROM (SELECT Vals.id.value('.', 'NVARCHAR(50)') AS val
,row_number() Over(Order By getdate()) as rn
FROM @myXML.nodes('/H/r') AS Vals(id) ) t
Where rn=2

Another sample:

create table #aTest (id int, [key] varchar(4000),)

INSERT INTO #aTest select 1,'session,state'
INSERT INTO #aTest select 2,'a,b,c'
INSERT INTO #aTest select 3,'hi, hello'

SELECT id,S.a.value('.', 'VARCHAR(100)') AS splitKey
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE([key], ',', '</r><r>') + '</r></H>' AS XML) AS [keys]
FROM #aTest) d

CROSS APPLY d.[keys].nodes('/H/r') S(a)

drop table #aTest
Advertisements


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