Parse XML with comma-separated entries into table in SQL Server

Question

I have a XML file which is something like this

    declare @X XML
     Set @X  ='<A> 
        <B> 
        <C>161</C> 
        <D>190</D> 
        <E>43 ,44 ,48 ,49</E> 
        </B>
    <B> 
    <C>162</C> 
    <D>190</D> 
    <E>100</E> 
    </B>
    </A>
'

I want to parse it into a table:

    C         D       E
    161      190     43
    161      190     48
    161      190     49
    162      190     100

I tried using

select  
    x.r.value('(A)[1]', 'int') as C,
    x.r.value('(D)[1]', 'int') as D,
    x.r.value('(E)[1]', 'varchar(max)') as E
from    
    @X.nodes('/A/B') as x(r)

DECLARE @xml as xml, @str as varchar(100), @delimiter as varchar(10)

SET @str = '43,48,49'
SET @delimiter = ','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
        SELECT T.N.value('.', 'int') as value FROM @xml.nodes('X') as T(N)

for separating the comma-separated column E into table.

But I am not able to combine both these in one query i.e. parse it into the table

Gottfried Lesigang · Accepted Answer · 2017-12-07 15:27:34Z

2

Next time you should really avoid "chameleon" questions. Especially when changes to the initial questions invalidate correct answers this is considered to be impolite...

What you've done instead: Close this question by accepting the most helpful answer and start a new question.

Nevertheless here's a working approach:

DECLARE @X XML =
N'<A>
  <B>
    <C>161</C>
    <D>190</D>
    <E>43 ,44 ,48 ,49</E>
  </B>
  <B>
    <C>162</C>
    <D>190</D>
    <E>100</E>
  </B>
</A>';

WITH ReturnYourListAsXml AS
(
    SELECT CAST(N'<x>' + REPLACE(B.value(N'(E/text())[1]',N'nvarchar(max)'),',','</x><x>') + '</x>' AS XML) AS E_As_Xml
          ,B.value(N'(C/text())[1]',N'int') AS C
          ,B.value(N'(D/text())[1]',N'int') AS D
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS B_Nr 
    FROM @x.nodes(N'/A/B') AS AllNodes(B)
)
SELECT cte.B_Nr
      ,cte.C
      ,cte.D
      ,YourList.Member.value(N'text()[1]',N'int') AS E
FROM ReturnYourListAsXml AS cte
CROSS APPLY E_As_Xml.nodes(N'/x') AS YourList(Member);

edited Dec 7, 2017 at 15:27

answered Dec 6, 2017 at 19:59

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user7416279 Over a year ago

Hi, No XML is not under my control it should be comma seperated list only. The above query will work only if there is one node of <A> in case of multiple xml nodes of <A> this will result only first instance

Katherine Elizabeth Lightsey Over a year ago

I like your solution Shnugo. It's very succinct. I've upvoted it! Katherine

Katherine Elizabeth Lightsey · Accepted Answer · 2017-12-07 16:28:10Z

1

I modified my original answer to work with your modified question where you have multiple subordinate [B] nodes. I also took the liberty of modifying the data so that the result would be obvious (all the "1"s together, etc.). I do like the other answer here from Shnugo with the XML split string version. I haven't seen that in a while but I think it's cleaner so I used it.

DECLARE @builder AS TABLE
  (
       [C]   [INT]
       , [D] [INT]
       , [E] [XML]
  );
DECLARE @X XML ='<A> 
    <B> 
        <C>161</C> 
        <D>191</D> 
        <E>41 ,51 ,61 ,71</E> 
    </B>
    <B> 
        <C>162</C> 
        <D>192</D> 
        <E>102</E> 
    </B>
    <B> 
        <C>163</C> 
        <D>193</D> 
        <E>43, 53, 63</E> 
    </B>
</A>';

WITH [row_builder]
     AS (SELECT t.c.query(N'.') AS [b_row]
         FROM   @X.nodes(N'/A/B') AS [t]([c]))
   , [shredder]
     AS (SELECT [b_row].value(N'(./B/C/text())[1]', N'[INT]')             AS [C]
                , [b_row].value(N'(./B/D/text())[1]', N'[INT]')           AS [D]
                , [b_row].value(N'(./B/E/text())[1]', N'[NVARCHAR](MAX)') AS [E]
         FROM   [row_builder])
   , [splitter]
     AS (SELECT [C]
                , [D]
                , cast(( '<E>' + replace([E], N',', '</E><E>') + '</E>' ) AS XML) AS [E]
         FROM   [shredder])
SELECT [C]
       , [D]
       , [column].query(N'.').value(N'(/E/text())[1]', N'[INT]') AS [E]
FROM   [splitter] AS [entry]
       CROSS APPLY [E].[nodes]('/*') AS [table] ( [column] )
ORDER  BY [C]
          , [D]
          , [E];

edited Dec 7, 2017 at 16:28

answered Dec 6, 2017 at 18:27

Katherine Elizabeth Lightsey

8535 silver badges12 bronze badges

5 Comments

user7416279 Over a year ago

The above query will work only if there is one node of <A> in case of multiple xml nodes of <A> this will result only first instance

Katherine Elizabeth Lightsey Over a year ago

That is correct. That is what the original poster asked for based on the reference XML.

user7416279 Over a year ago

SELECT @X.value(N'(/A/B/C/text())[1]', N'[INT]') AS [C], @X.value(N'(/A/B/D/text())[1]', N'[INT]') AS [D] is selecting only first value

user7416279 Over a year ago

Same goes for Splitter CTE

Katherine Elizabeth Lightsey Over a year ago

See my modification.

Collectives™ on Stack Overflow

Parse XML with comma-separated entries into table in SQL Server

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related