本质上，我正在尝试使用 SQL 将我拥有的 XML 转换为平面数据结构。

我的 XML 采用以下格式(我已将 XML 更改为一个子集，以简化我的示例):

<Actions>
    <AddComponent>
        <Action>
            <DataItem>
                <GroupId>1</GroupId>
                <Data>
                    <Id>100</Id>
                    <Value>Value A</Value>
                    <Children>
                        <Data>
                            <Id>200</Id>
                            <Value>Value B</Value>
                            <Children>
                                <Data>
                                    <Id>300</Id>
                                    <Value>Value C1</Value>
                                </Data>
                                <Data>
                                    <Id>301</Id>
                                    <Value>Value C2</Value>
                                    <Children />
                                </Data>
                            </Children>
                        </Data>
                    </Children>
                </Data>
            </DataItem>
            <DataItem>
                <GroupId>2</GroupId>
                <Data>
                    <Id>101</Id>
                    <Value>Value A</Value>
                    <Children>
                        <Data>
                            <Id>200</Id>
                            <Value>Value B</Value>
                            <Children>
                                <Data>
                                    <Id>302</Id>
                                    <Value>Value C3</Value>
                                </Data>
                            </Children>
                        </Data>
                    </Children>
                </Data>
            </DataItem>
        </Action>
    </AddComponent>
</Actions>

我正在寻找的输出如下:

+---------+-----+----------+----------+
| GroupId | Id  |  Value   | ParentId |
+---------+-----+----------+----------+
|       1 | 100 | Value A  | NULL     |
|       1 | 200 | Value B  | 100      |
|       1 | 300 | Value C1 | 200      |
|       1 | 301 | Value C2 | 200      |
|       2 | 101 | Value A  | NULL     |
|       2 | 200 | Value B  | 101      |
|       2 | 302 | Value C3 | 200      |
+---------+-----+----------+----------+

我不确定递归遍历“子”元素的最佳方法。因为 child 的数量可以无限增加。

最佳答案

select DI.X.value('(GroupId/text())[1]', 'int') as GroupId,
       D.X.value('(Id/text())[1]', 'int') as Id,
       D.X.value('(Value/text())[1]', 'nvarchar(50)') as Value,
       D.X.value('(../../Id/text())[1]', 'int') as ParentID
from @XML.nodes('/Actions/AddComponent/Action/DataItem') as DI(X)
  cross apply DI.X.nodes('.//Data') as D(X)

.//Data 将递归地为您提供所有 Data 节点。

注意:使用父轴 (../../Id/text())[1] 获取 ParentID 可能会降低性能.用您的数据尝试一下，看看它的性能是否可以接受。

更新:

在 nodes() 调用中执行父轴看起来会给您一个更好的查询计划。

select DI.X.value('(GroupId/text())[1]', 'int') as GroupId,
       D.X.value('(Id/text())[1]', 'int') as Id,
       D.X.value('(Value/text())[1]', 'nvarchar(50)') as Value,
       P.X.value('text()[1]', 'int') as ParentID
from @XML.nodes('/Actions/AddComponent/Action/DataItem') as DI(X)
  cross apply DI.X.nodes('.//Data') as D(X)
  outer apply D.X.nodes('../../Id') as P(X)

关于sql - XML 到 SQL 结果集。多级嵌套，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/27815821/