get values from xml by sql query when several attributes

Question

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

Yitzhak Khabinsky · Answer 1 · 2020-02-20T15:58:55.560

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    |
+------------+------+------+

Shnugo, I adjusted the answer based on the hint. Thanks. – Yitzhak Khabinsky Feb 20 '20 at 15:50 — Yitzhak Khabinsky, Feb 20 '20 at 15:50

score 2 · Accepted Answer · answered Feb 20 '20 at 15:03

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)

Good answer, +1 from my side. A tiny hint: *Backward* navigation `../` is very slow. In this case it would be better to read the `@global_id` from the `@xml` variable directly (and there's no need for `FROM (VALUES)` as Ytzhak has pointed out already. — Shnugo, Feb 20 '20 at 15:45

get values from xml by sql query when several attributes

2 Answers2