How to split comma separated values stored in XML node Without using function- SQL Server 2012

Question

How to split comma separated values stored in XML node Without using function- SQL Server 2012

Patchai001

SSC Veteran

Points: 290
More actions
December 1, 2018 at 12:34 am

#151612

I have a requirement to split a csv data inside a xml node column. I am using SQL server 2012. I want a query without creating a function.
The data is as follows for example
ID : 1 XMLvalue : <BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=1,ASSESSMENT=2,ASSESSMENT=3</SERIAL><SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL></BETA> ID : 2 XMLvalue : <BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=5,ASSESSMENT=6</SERIAL><SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL></BETA> ID : 3 XMLvalue : <BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL><SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL></BETA>
The output should be as follows
ID Assessment PROJECT 1 ASSESSMENT=1 PROJECT=1 1 ASSESSMENT=2 PROJECT=2 1 ASSESSMENT=3 PROJECT=3 2 ASSESSMENT=4 PROJECT=4 2 ASSESSMENT=5 PROJECT=5 2 ASSESSMENT=6 PROJECT=6 3 ASSESSMENT=7 PROJECT=7 3 ASSESSMENT=8 PROJECT=8 3 ASSESSMENT=9 PROJECT=9
I want to achieve the above output without creating a function and only using a query.
I was not able to create a SQL Fiddle, so pasted the create and insert statement below.
CREATE TABLE Chart( StoreID INT PRIMARY KEY, XMLvalue XML );
INSERT INTO Chart Values (1,<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=1,ASSESSMENT=2,ASSESSMENT=3</SERIAL><SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL></BETA>) INSERT INTO Chart Values (2,<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=5,ASSESSMENT=6</SERIAL><SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL></BETA>) INSERT INTO Chart Values (3,<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL><SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL></BETA>)
Any Help to this would be greatly appreciated.
Thanks in advance.
Patchai

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Why can't you use a function?
I'd use DelimitedSplit8K and be done... Why the arbitrary requirement? Is this a class assignment or something?.

Thom A SSC Guru Points: 99130 More actions · Answer 2

If you don't want to use a function, I suppose you could embed the code for something like DelimitedSplit8K or an XML splitter (if ordering doesn;t matter) in your code. But I second pietlinden's question; why can't you use a function?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Patchai001 SSC Veteran Points: 290 More actions · Answer 3

This was the requirement from client not to use function as this query will be used is different programming language.

I got couple of solution from a different forum and it is as follows,

Solution 1

CREATE TABLE #Chart( StoreID INT PRIMARY KEY, XMLvalue XML );

INSERT INTO #Chart Values (1,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT="1",ASSESSMENT=2,<"ASSESSMENT=3" ></SERIAL><SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL></BETA>') INSERT INTO #Chart Values (2,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=5,ASSESSMENT=6</SERIAL><SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL></BETA>') INSERT INTO #Chart Values (3,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL><SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL></BETA>') INSERT INTO #Chart Values (4,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=10,ASSESSMENT=11,ASSESSMENT=12,ASSESSMENT=13,ASSESSMENT=14,ASSESSMENT=15</SERIAL><SERIAL NAME="PROJECT"> PROJECT=10,PROJECT=11,PROJECT=12,PROJECT=13</SERIAL></BETA>')

SELECT SToreID, MAX(CASE WHEN Name = 'ASSESSMENT' THEN LTRIM(RTRIM(Value)) END) AS ASSESSMENT, MAX(CASE WHEN Name = 'PROJECT' THEN LTRIM(RTRIM(Value)) END) AS PROJECT FROM ( SELECT StoreID,u.value('../@NAME','varchar(100)') AS Name,u.query('.').value('.','varchar(max)') AS Value, ROW_NUMBER() OVER (PARTITION BY SToreID,u.value('../@NAME','varchar(100)') ORDER BY u.query('.').value('.','varchar(max)') ) AS Seq

FROM ( SELECT StoreID,CAST(REPLACE(REPLACE(REPLACE(CAST(XMLValue AS varchar(max)),',','</Value><Value>'),'</SERIAL>','</Value></SERIAL>'),'">','"><Value>')AS xml) AS XMLValue FROM #Chart ) c CROSS APPLY XMLValue.nodes('/BETA/SERIAL/Value')t(u) )m GROUP BY StoreID,Seq ORDER BY SToreID,Seq

DROP TABLE #Chart

Solution 2
CREATE TABLE #Chart( StoreID INT PRIMARY KEY, XMLvalue XML );

INSERT INTO #Chart Values (1,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=0,</Value><Value>="1",ASSESSMENT=2,<"ASSESSMENT=3" ></SERIAL><SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL></BETA>') INSERT INTO #Chart Values (2,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=65,ASSESSMENT=16</SERIAL><SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL></BETA>') INSERT INTO #Chart Values (3,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL><SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL></BETA>') INSERT INTO #Chart Values (4,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=10,ASSESSMENT=11,ASSESSMENT=12,ASSESSMENT=13,ASSESSMENT=14,ASSESSMENT=15</SERIAL><SERIAL NAME="PROJECT"> PROJECT=10,PROJECT=11,PROJECT=12,PROJECT=13</SERIAL></BETA>')

go SELECT * FROM #Chart go WITH lists AS ( SELECT StoreID, assessments = ltrim(A.a.value('(./text())[1]', 'nvarchar(MAX)') COLLATE Latin1_General_BIN), projects = ltrim(P.p.value('(./text())[1]', 'nvarchar(MAX)') COLLATE Latin1_General_BIN) FROM #Chart CROSS APPLY XMLvalue.nodes('/BETA/SERIAL[@NAME="ASSESSMENT"]') AS A(a) CROSS APPLY XMLvalue.nodes('/BETA/SERIAL[@NAME="PROJECT"]') AS P(p) ), assess_unwind AS ( SELECT StoreID, assessments, listpos = 1, start = convert(bigint, 1), stop = charindex(',', assessments + ',') FROM lists UNION ALL SELECT StoreID, assessments, listpos + 1, start = stop + 1, stop = charindex(',', assessments + ',', stop + 1) FROM assess_unwind WHERE stop > 0 ), proj_unwind AS ( SELECT StoreID, projects, listpos = 1, start = convert(bigint, 1), stop = charindex(',', projects + ',') FROM lists UNION ALL SELECT StoreID, projects, listpos + 1, start = stop + 1, stop = charindex(',', projects + ',', stop + 1) FROM proj_unwind WHERE stop > 0 ) SELECT a.StoreID, substring(a.assessments, a.start, CASE WHEN a.stop > 0 THEN a.stop - a.start ELSE 0 END), substring(p.projects, p.start, CASE WHEN p.stop > 0 THEN p.stop - p.start ELSE 0 END) FROM assess_unwind a JOIN proj_unwind p ON a.StoreID = p.StoreID AND a.listpos = p.listpos WHERE a.stop > 0 AND p.stop > 0 ORDER BY a.StoreID, a.listpos OPTION (MAXRECURSION 0) go DROP TABLE #Chart

Thom A SSC Guru Points: 99130 More actions · Answer 4

Patchai001 - Monday, December 3, 2018 8:26 AM
This was the requirement from client not to use function as this query will be used is different programming language.

Why does the language of the program matter? You can call the same SQL query from VB.net, C#, Python, Java, Ruby, etc, etc. The code you use to execute said query will differ each time, however the SQL itself can be identical everytime.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Patchai001 - Monday, December 3, 2018 8:26 AM
This was the requirement from client not to use function as this query will be used is different programming language.
I got couple of solution from a different forum and it is as follows,
Solution 1
CREATE TABLE #Chart( StoreID INT PRIMARY KEY, XMLvalue XML );
INSERT INTO #Chart Values (1,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=1,ASSESSMENT=2,ASSESSMENT=3</SERIAL><SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL></BETA>')
INSERT INTO #Chart Values (2,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=5,ASSESSMENT=6</SERIAL><SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL></BETA>')
INSERT INTO #Chart Values (3,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL><SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL></BETA>')
INSERT INTO #Chart Values (4,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=10,ASSESSMENT=11,ASSESSMENT=12,ASSESSMENT=13,ASSESSMENT=14,ASSESSMENT=15</SERIAL><SERIAL NAME="PROJECT"> PROJECT=10,PROJECT=11,PROJECT=12,PROJECT=13</SERIAL></BETA>')
SELECT SToreID, MAX(CASE WHEN Name = 'ASSESSMENT' THEN LTRIM(RTRIM(Value)) END) AS ASSESSMENT, MAX(CASE WHEN Name = 'PROJECT' THEN LTRIM(RTRIM(Value)) END) AS PROJECT
FROM
(SELECT StoreID,u.value('../@NAME','varchar(100)') AS Name,u.query('.').value('.','varchar(max)') AS Value,ROW_NUMBER() OVER (PARTITION BY SToreID,u.value('../@NAME','varchar(100)') ORDER BY u.query('.').value('.','varchar(max)') ) AS Seq--,n.query('.').value('.','varchar(max)') AS PROJECT--u.query('SERIAL[@NAME = "PROJECT"]').value('.','varchar(max)') AS PROJECT
FROM
(SELECT StoreID,CAST(REPLACE(REPLACE(REPLACE(CAST(XMLValue AS varchar(max)),',','</Value><Value>'),'</SERIAL>','</Value></SERIAL>'),'">','"><Value>') AS xml) AS XMLValue FROM #Chart) cCROSS APPLY XMLValue.nodes('/BETA/SERIAL/Value')t(u))m GROUP BY StoreID,SeqORDER BY SToreID,Seq
DROP TABLE #chart
Solution 2
CREATE TABLE #Chart( StoreID INT PRIMARY KEY, XMLvalue XML );
INSERT INTO #Chart Values (1,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=1,ASSESSMENT=2,ASSESSMENT=3</SERIAL><SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL></BETA>')
INSERT INTO #Chart Values (2,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=5,ASSESSMENT=6</SERIAL><SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL></BETA>')
INSERT INTO #Chart Values (3,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL><SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL></BETA>')
INSERT INTO #Chart Values (4,'<BETA> <SERIAL NAME="ASSESSMENT"> ASSESSMENT=10,ASSESSMENT=11,ASSESSMENT=12,ASSESSMENT=13,ASSESSMENT=14,ASSESSMENT=15</SERIAL><SERIAL NAME="PROJECT"> PROJECT=10,PROJECT=11,PROJECT=12,PROJECT=13</SERIAL></BETA>')
go
SELECT * FROM #Chart
go
WITH lists AS ( SELECT StoreID,     assessments = ltrim(A.a.value('(./text())[1]', 'nvarchar(MAX)') COLLATE Latin1_General_BIN),    projects = ltrim(P.p.value('(./text())[1]', 'nvarchar(MAX)') COLLATE Latin1_General_BIN) FROM #Chart CROSS APPLY XMLvalue.nodes('/BETA/SERIAL[@NAME="ASSESSMENT"]') AS A(a) CROSS APPLY XMLvalue.nodes('/BETA/SERIAL[@NAME="PROJECT"]') AS P(p)), assess_unwind AS ( SELECT StoreID, assessments, listpos = 1,
start = convert(bigint, 1),    stop = charindex(',', assessments + ',') FROM lists UNION ALL SELECT StoreID, assessments, listpos + 1,
start = stop + 1,    stop = charindex(',', assessments + ',', stop + 1) FROM assess_unwind WHERE stop > 0), proj_unwind AS
( SELECT StoreID, projects, listpos = 1,    start = convert(bigint, 1),    stop = charindex(',', projects + ',') FROM lists UNION ALL SELECT StoreID, projects, listpos + 1, start = stop + 1,    stop = charindex(',', projects + ',', stop + 1) FROM proj_unwind WHERE stop > 0)
SELECT a.StoreID,    substring(a.assessments, a.start, CASE WHEN a.stop > 0 THEN a.stop - a.start ELSE 0 END),
substring(p.projects, p.start, CASE WHEN p.stop > 0 THEN p.stop - p.start ELSE 0 END)FROM assess_unwind a
JOIN proj_unwind p ON a.StoreID = p.StoreID       AND a.listpos = p.listposWHERE a.stop > 0 AND p.stop > 0ORDER BY a.StoreID, a.listpos OPTION (MAXRECURSION 0)
go
DROP TABLE #Chart

You're concatenating or expansively replacing data in either case and that'll cost you quite a bit for performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)