从执行计划中分解 XML [英] Shredding XML From Execution Plans

问题描述

在此之前我会说我讨厌 XML，它使用起来很糟糕，但有时是必要的.

I'll preface this by saying that I hate XML, horrible stuff to work with, but necessary sometimes.

我当前的问题是我试图从执行计划(由用户提供，因此可以是任何大小)中获取 XML 并将其切碎到表中以进行进一步操作.目前我只有两个选择；

My current issue is that I'm trying to take the XML from an execution plan (supplied by a user, so could be any size) and shred this into a table for further manipulation. I'm down to two options at the moment;

1. 我可以计算出可用于执行计划的最大节点数(我怀疑这会很多)并创建可用于任何 XML 输入的整个脚本.这将是一次性的，所以不是问题.
2. 另一种方法是动态计算节点数量并根据要求创建输出.

过去有没有人做过类似的练习?我发现的所有示例查询都已经知道输出字段.

Has anybody done a similar exercise in the past? All of the sample queries I've found have known the output fields already.

推荐答案

一个非常直接的方法可能是这样(而 @x 是你的 XML 执行计划):

A very straight way could be this (while @x is your XML-execution-plan):

DECLARE @x XML=
N'<root>
    <ElementE1 AttributA1="A1-text belongs to E1[1]" OneMore="xyz">E1-Text 2</ElementE1>
    <ElementE1 AttributA1="A1-text belongs to E1[2]">E1-Text 2</ElementE1>
    <ElementParent>
      <subElement test="sub"/>
      Free text
    </ElementParent>
  </root>';

DECLARE @idoc INT;
EXEC sp_xml_preparedocument @idoc OUTPUT, @x;   
SELECT * FROM OPENXML (@idoc, '*');   
EXEC sp_xml_removedocument @idoc;  

结果(不是所有列)

+----+----------+----------+--------------+------+--------------------------+
| id | parentid | nodetype | localname    | prev | text                     |
+----+----------+----------+--------------+------+--------------------------+
| 0  | NULL     | 1        | root         | NULL | NULL                     |
+----+----------+----------+--------------+------+--------------------------+
| 2  | 0        | 1        | ElementE1    | NULL | NULL                     |
+----+----------+----------+--------------+------+--------------------------+
| 3  | 2        | 2        | AttributA1   | NULL | NULL                     |
+----+----------+----------+--------------+------+--------------------------+
| 13 | 3        | 3        | #text        | NULL | A1-text belongs to E1[1] |
+----+----------+----------+--------------+------+--------------------------+
| 4  | 2        | 2        | OneMore      | NULL | NULL                     |
+----+----------+----------+--------------+------+--------------------------+
| 14 | 4        | 3        | #text        | NULL | xyz                      |
+----+----------+----------+--------------+------+--------------------------+
| 5  | 2        | 3        | #text        | NULL | E1-Text 2                |
+----+----------+----------+--------------+------+--------------------------+
| 6  | 0        | 1        | ElementE1    | 2    | NULL                     |
+----+----------+----------+--------------+------+--------------------------+
| 7  | 6        | 2        | AttributA1   | NULL | NULL                     |
+----+----------+----------+--------------+------+--------------------------+
| 15 | 7        | 3        | #text        | NULL | A1-text belongs to E1[2] |
+----+----------+----------+--------------+------+--------------------------+
| 8  | 6        | 3        | #text        | NULL | E1-Text 2                |
+----+----------+----------+--------------+------+--------------------------+
| 9  | 0        | 1        | ElementParent| 6    | NULL                     |
+----+----------+----------+--------------+------+--------------------------+
| 10 | 9        | 1        | subElement   | NULL | NULL                     |
+----+----------+----------+--------------+------+--------------------------+
| 11 | 10       | 2        | test         | NULL | NULL                     |
+----+----------+----------+--------------+------+--------------------------+
| 16 | 11       | 3        | #text        | NULL | sub                      |
+----+----------+----------+--------------+------+--------------------------+
| 12 | 9        | 3        | #text        | 10   | Free text                |
+----+----------+----------+--------------+------+--------------------------+

id 清楚地表明，该算法是广度优先，没有 id=1(为什么)和 nodetype 允许区分元素、属性和(浮动)文本.prev 列指向链中的同级.缺少的列与命名空间有关...

The id shows clearly, that the algorithm is breadth first, there is no id=1 (why ever) and the nodetype allows to distinguish between elements, attributs and (floating) text. The prev column points to a sibling up in the chain. The missing columns are related to namespaces...

FROM OPENXML 的方法已经过时，但这是极少数情况之一，它可能仍然非常有用......

The approach with FROM OPENXML is outdated, but this is one of the rare situations it might still be very usefull...

您会得到一个包含 ID 和 ParentID 的列表，您可能会使用递归 CTE 进行查询……这取决于您之后想用它做什么……

You get a list with IDs and ParentIDs you might query with an recursive CTE... This depends on what you want to do with this afterwards...

这篇关于从执行计划中分解 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！