使用nodes()方法在SQL中展平分层XML

我有一个存储过程，它采用 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 个块（每个需要从中获取数据的节点一个）会提高性能吗？鉴于这些节点上都没有我可以用来连接备份的“密钥”，任何人都可以提供任何指示，我如何能够做到这一点？

经过一番网上搜索后，我似乎已经回答了我自己的问题：

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)

Using CROSS APPLY我可以选择顶级grandparent节点并使用它来选择子节点parent节点等。使用这种方法，我已经从执行中获取了查询1分30秒下降到大约6秒.

有趣的是，如果我使用“旧”OPEN XML方法检索相同的数据，查询在1 second!

看来您可能必须根据传入文档的预期大小/复杂性，根据具体情况来使用这两种技术。