OpenXML parsing with elements that are also siblings of the same name

Question

I have an XML data structure that currently looks like this and is saved in the XML_STLD table (which I cannot change, its output from a proprietary system):

<rootnode>
    <group id="00001" status="online">
        <order OrdId="42" Type="Sale">
            <Item code="1234" qty="1" unitprice="38.00" rank="0" level="0">
                <Item code="5678" qty="1" unitprice="11.00" rank="0" level="1">
                    <Item code="9876" qty="1" unitprice="8.00" rank="0" level="2">
                    <Tax percent="12"/></Item>
                <Tax percent="12"/></Item>
            <Tax percent="12"/></Item>
            <Item code="7654" qty="1" unitprice="98.00" rank="1" level="0">
                <Item code="3211" qty="1" unitprice="8.00" rank="1" level="1">
                <Tax percent="12"/></Item>
            <Tax percent="12"/></Item>
        </order>
    </group>
</rootnode>

My OpenXML request is as follows:

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XML_STLD

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT GroupId, GroupStatus, OrdId, OrdType, ICode, IQty, IPrice, 
IRank, ILevel, ITax

FROM OPENXML(@hDoc, 'rootnode/group/order/Item/Tax')
WITH 
(
GroupId int '../../../@id',
GroupStatus [varchar](100) '../../../@status',
OrdId int '../../@OrdId',
OrdType [varchar](100) '../../@Type',
ICode int '../@code',
IQty int '../@qty',
IPrice numeric(18,2) '../@unitprice',
IRank int '../@rank',
ILevel int '../@level',
ITax int '@percent'
) 

EXEC sp_xml_removedocument @hDoc
GO

Now my query works very well, but it has a big problem, it returns only the first element and the tax brother associated with it. To give a brief description of the item:

. , , "0" . "0" , "1" .. , ( ). "1", "0" .

:

GroupId | GroupStatus | OrdId | OrdType | ICode | IQty | IPrice | IRank | ILevel | ITax
------- | ----------- | ----- | ------- | ----- | ---- | ------ | ----- | ------ | ----
1       | online      | 42    | Sale    | 1234  | 1    | 38.00  | 0     | 0      | 12
1       | online      | 42    | Sale    | 7654  | 1    | 98.00  | 1     | 0      | 12

:

GroupId | GroupStatus | OrdId | OrdType | ICode | IQty | IPrice | IRank | ILevel | ITax
------- | ----------- | ----- | ------- | ----- | ---- | ------ | ----- | ------ | ----
1       | online      | 42    | Sale    | 1234  | 1    | 38.00  | 0     | 0      | 12
1       | online      | 42    | Sale    | 5678  | 1    | 11.00  | 0     | 1      | 12
1       | online      | 42    | Sale    | 9876  | 1    | 8.00   | 0     | 2      | 12
1       | online      | 42    | Sale    | 7654  | 1    | 98.00  | 1     | 0      | 12
1       | online      | 42    | Sale    | 3211  | 1    | 8.00   | 1     | 1      | 12

, - XQuery. SQL Server 2012.

Answer 1

XQuery, XML group Item, group. , :

select 
    grp.value('@id', 'int') AS GroupId,
    grp.value('@status', 'varchar(100)') AS GroupStatus,
    grp.value('(order/@OrdId)[1]', 'varchar(100)') AS OrdId,
    grp.value('(order/@Type)[1]', 'varchar(100)') AS OrdType,
    item.value('@code', 'int') AS ICode,
    item.value('@qty', 'int') AS IQty
from @xml.nodes('rootnode/group') A(grp)
outer apply grp.nodes('.//Item') AS B(item)

, @xml - XML, :

declare @xml AS XML = '<rootnode>
    <group id="00001" status="online">
        <order OrdId="42" Type="Sale">
            <Item code="1234" qty="1" unitprice="38.00" rank="0" level="0">
                <Item code="5678" qty="1" unitprice="11.00" rank="0" level="1">
                    <Item code="9876" qty="1" unitprice="8.00" rank="0" level="2">
                        <Tax percent="12" />
                    </Item>
                    <Tax percent="12" />
                </Item>
                <Tax percent="12" />
            </Item>
            <Item code="7654" qty="1" unitprice="98.00" rank="1" level="0">
                <Item code="3211" qty="1" unitprice="8.00" rank="1" level="1">
                    <Tax percent="12" />
                </Item>
                <Tax percent="12" />
            </Item>
        </order>
    </group>
</rootnode>'

:

IPrice, IRank, ILevel ITax :)