使用nodes（）方法展平SQL中的分层XML

Question

我有一个存储过程，该存储过程将XML文档作为参数，其结构类似于以下内容：

<grandparent name="grandpa bob">
  <parent name="papa john">
    <children>
      <child name="mark" />
      <child name="cindy" />
    </children>
  </parent>
  <parent name="papa henry">
    <children>
      <child name="mary" />
    </children>
  </parent>
</grandparent>

我的要求是“展平”此数据，以便可以将其插入临时表中并在该过程中进行进一步的操作，因此上述XML变为：

Grandparent Name Parent Name     Child Name
---------------- --------------- ---------------
grandpa bob      papa john       mark
grandpa bob      papa john       cindy
grandpa bob      papa henry      mary

当前，这是使用SQL Server XML节点完成的：

SELECT
    VIRT.node.value('../../../@name','varchar(15)') 'Grandparent Name',
    VIRT.node.value('../../@name','varchar(15)') 'Parent Name',
    VIRT.node.value('@name','varchar(15)') 'Child Name'
FROM
    @xmlFamilyTree.nodes('/grandparent/parent/children/child') AS VIRT(node)

直到我开始在该过程中抛出大量数据（即1000个以上的child节点）时，此方法才有效，这时该过程停止了，并需要1至2分钟的时间来执行。我认为这可能是由于我从最低级别（<child）开始，然后每次出现时都遍历XML文档。将单个查询分成3个块（每个节点我需要数据）会提高此处的性能吗？鉴于这些节点上都没有可用于加入备份的“键”，任何人都可以提供任何指针来说明如何执行此操作吗？

Answer 1

在网上浏览了一下之后，我似乎已经回答了自己的问题：

SELECT
    grandparent.gname.value('@name', 'VARCHAR(15)'),
    parent.pname.value('@name', 'VARCHAR(15)'),
    child.cname.value('@name', 'VARCHAR(15)')
FROM
    @xmlFamilyTree.nodes('/grandparent') AS grandparent(gname)
CROSS APPLY
    grandparent.gname.nodes('*') AS parent(pname)
CROSS APPLY
    parent.pname.nodes('children/*') AS child(cname)

使用CROSS APPLYI可以选择顶级grandparent节点，然后使用它来选择子parent节点，依此类推。使用这种html" target="_blank">方法，我的查询从大约
1分钟30秒的 执行 时间 减少到大约 6秒 。

但是，有趣的是，如果我使用“旧”OPEN XML方法检索相同的数据，则查询将在 1秒内 执行！

似乎您可能必须根据传入的文档的预期大小/复杂性，逐案处理这两种技术。