SQL Server parse XML to table - multiple node with the same name

Question

I would like to parse XML into a table in SQL Server 2012 where my XML has nodes with the same name.

My SQL query which return only the first row:

SELECT
    [date] = Node.Data.value('(date)[1]', 'NVARCHAR(MAX)'),
    name = Node.Data.value('(name)[1]', 'VARCHAR(MAX)')
FROM    
    @xml.nodes('result/subject') Node(Data)

XML sample

<result>
  <subject>
    <date>2019-06-03</date>
    <name>AZGREX</name>
    <name>ABGDFC</name>
    <name>WWGDFW</name>
    <name>FDSFSD</name>
    <name>FSDWEW</name>
    <name>CXZCXZ</name>
    <name>GWGRE</name>
  </subject>
</result>

Possible duplicate of XML to SQL - Selecting multiple nodes with the same name — user9517769
– user9517769, Commented Sep 2, 2019 at 13:33

Thom A · Accepted Answer · 2019-09-02 13:36:34Z

4

You need to use nodes in the FROM:

DECLARE @XML xml = '<result>
  <subject>
    <date>2019-06-03</date>
    <name>AZGREX</name>
    <name>ABGDFC</name>
    <name>WWGDFW</name>
    <name>FDSFSD</name>
    <name>FSDWEW</name>
    <name>CXZCXZ</name>
    <name>GWGRE</name>
  </subject>
</result>';

SELECT r.[subject].value('(date/text())[1]','date') AS [date],
       s.[name].value('(./text())[1]','varchar(6)') AS [name] --obviously, you'll likely need a larger length
FROM (VALUES(@XML))V(X)
     CROSS APPLY V.X.nodes('result/subject') r([subject])
     CROSS APPLY r.[subject].nodes('name') s([name]);

answered Sep 2, 2019 at 13:36

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

SQL Server parse XML to table - multiple node with the same name

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related