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

  • Patchai001

    SSC Enthusiast

    Points: 180

    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

  • pietlinden

    SSC Guru

    Points: 62365

    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: 98212

    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.

  • Patchai001

    SSC Enthusiast

    Points: 180

    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,&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

    Solution 2
    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

  • Thom A

    SSC Guru

    Points: 98212

    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.

  • Jeff Moden

    SSC Guru

    Points: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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