我有一个 XML 数据结构，目前看起来像这样并保存在表 XML_STLD 中(我无法更改，它是专有系统的输出):

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

我的 OpenXML 查询如下所示:

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

现在我的查询工作得很好，但它有一个大问题，它只返回第一个项目及其相关的税收兄弟。给出元素的简短描述:

每个项目都有等级和级别。如您所见，前三个是彼此的 sibling ，等级为“0”。但是，祖 parent 的级别为“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。

最佳答案

使用 XQuery，您可以分解 group 元素和 Item 元素在 group 中的任何位置的 XML。然后您可以从这两个元素开始选择所需的值:

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 列的选择留作练习:)

关于sql-server - 使用同名元素解析 OpenXML，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/41370534/