CODEWITHSUNDEEP
sql-serverxmlt-sqlshred
Tom Kev
Tom Kev

Reputation: 146

get values from xml by sql query when several attributes

There is xml with several attributes "Num" 

DECLARE @XML XML = '
<FileId global_id="1234">
  <file id="12aa">
  </file>
  <file id="12bb">
    <Number Num = "1"/>
    <Number Num = "2"/>
  </file>
</FileId>';

With this sql query only one attribute can be get

SELECT F.[File].value(N'../@global_id','varchar(100)') as id_payment,
  F.[File].value('@id', 'varchar(4)') AS id,
  F.[File].value('(Number/@Num)[1]', 'int') as [Num]
FROM (VALUES (@XML)) V (X)
  CROSS APPLY V.X.nodes('/FileId/file') F([File])

How to get all attributes -- Num = 1 and Num = 2. Can be a variable amount of attributes.

id_payment  id      Num
1234        12aa    NULL
1234        12bb    1
1234        12bb    2

Upvotes: 0

Views: 60

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22177

Much simpler version. (1) No need to use the VALUES clause. (2) The OUTER APPLY simulates LEFT OUTER JOIN. (3) Most efficient way to retrieve the global_id attribute. The credit goes to Shnugo.

SQL

DECLARE @XML XML = N'
<FileId global_id="1234">
    <file id="12aa">
    </file>
    <file id="12bb">
        <Number Num="1"/>
        <Number Num="2"/>
    </file>
</FileId>';

SELECT @xml.value('(/FileId/@global_id)[1]','INT') AS id_payment
    , c.value('@id', 'VARCHAR(4)') AS id
    , n.value('@Num', 'INT') AS [Num]
FROM @xml.nodes('/FileId/file') AS t(c)
    OUTER APPLY t.c.nodes('Number') AS t2(n);

Output

+------------+------+------+
| id_payment |  id  | Num  |
+------------+------+------+
|       1234 | 12aa | NULL |
|       1234 | 12bb | 1    |
|       1234 | 12bb | 2    |
+------------+------+------+

Upvotes: 3

lptr
lptr

Reputation: 6788

DECLARE @XML XML = '
<FileId global_id="1234">
  <file id="12aa">
  </file>
  <file id="12bb">
    <Number Num = "1"/>
    <Number Num = "2"/>
    <Number Num = "3"/>
    <Number Num = "4"/>
    <Number Num = "5"/>
    <Number Num = "6"/>
  </file>
</FileId>';



SELECT F.[File].value(N'../@global_id','varchar(100)') as id_payment,
  F.[File].value('@id', 'varchar(4)') AS id,
  F.[File].value('(Number/@Num)[1]', 'int') as [Num],
  n.num.value('(@Num)[1]', 'int') as [Numxyz]

FROM (VALUES (@XML)) V (X)
  CROSS APPLY V.X.nodes('/FileId/file') F([File]) 
  outer apply F.[File].nodes('Number') as n(num)

Upvotes: 2

Related Questions