sql-serverxmlt-sqlshred

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

Solution

  • 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)