How to fetch data from XML with different nodes as column in SQL Server

Question

I have a XML from which I am trying to select rows by the table name from the XML but the columns count is different on each node.

Below is my XML:

<tables>
    <table>
      <name>Table1</name>
       <attr>
         <id>1</id>
         <col1>123</col1>
          <col2>345</col2>
       </attr>
        <attr>
         <id>2</id>
         <col3>123</col3>
       </attr>
        <attr>
         <id>4</id>
          <col2>123</col2>
       </attr>
    </table>
</tables>

Now I am querying by below query:

SELECT T.N.VALUE('id','nvarchar(100)') as id,
       T.N.VALUE('col1','nvarchar(100)') as col1,
       T.N.VALUE('col2','nvarchar(100)') as col2,
       T.N.VALUE('col3','nvarchar(100)') as col3 
FROM @input.nodes('/tables/table/attr') AS T(N) 
CROSS APPLY @input.nodes('/tables/table/name') AS S(N) 
WHERE S.N.value('name','varchar(100)')='Table1'

But I get an error

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

But I am expecting to get:

id | col1 | col2 | col3
---+------+------+------
 1 | 123  | 345  | NULL
 2 | NULL | NULL | 123
 4 | NULL | 123  | NULL

David Browne - Microsoft · Accepted Answer · 2020-10-31 16:46:47Z

3

Like this:

declare @input xml = '<tables>
    <table>
      <name>Table1</name>
       <attr>
         <id>1</id>
         <col1>123</col1>
          <col2>345</col2>
       </attr>
        <attr>
         <id>2</id>
         <col3>123</col3>
       </attr>
        <attr>
         <id>4</id>
          <col2>123</col2>
       </attr>
    </table>
</tables> 
'


SELECT a.N.value('(id)[1]','nvarchar(100)') as id,
       a.N.value('(col1)[1]','nvarchar(100)') as col1,
       a.N.value('(col2)[1]','nvarchar(100)') as col2,
       a.N.value('(col3)[1]','nvarchar(100)') as col3 
FROM @input.nodes('/tables/table') AS t(N) 
CROSS APPLY T.N.nodes('attr') AS a(N) 
WHERE t.N.value('(name)[1]','varchar(100)')='Table1'

answered Oct 31, 2020 at 16:46

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

How to fetch data from XML with different nodes as column in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related