使用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 多个 子 节点），此时这会变成一个停止并需要 1 到 2 分钟来执行。我认为这可能是因为我从最低级别 () 开始，然后遍历每次出现的 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 APPLY 我可以选择顶级 grandparent 节点并使用它来选择子 < code>parent 节点等等。使用此方法，我将查询的执行时间从大约 1 分 30 秒减少到大约6 秒。

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

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