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

Transact-SQL
,
1

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

2

hi

please look into this link

hope it helps

if you are not able to
I can give it a SHOT
:slight_smile:
:slight_smile:

3

Thanks for your help Michael, I got the answer from a different forum and the solution is as follows,

Solution 1

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

INSERT INTO #Chart Values (1,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=0,&lt;/Value&gt;&lt;Value&gt;="1",ASSESSMENT=2,&lt;"ASSESSMENT=3" &gt;</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

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

INSERT INTO #Chart Values (1,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT="1",ASSESSMENT=2,&lt;"ASSESSMENT=3" &gt;</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