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