如何从多列同结构XML中按对应节点提取值？

问题描述 #

现有表myTable包含三个结构相同的XML列，表结构及插入数据如下：

CREATE TABLE myTable 
(
    Field1 XML,
    Field2 XML,
    Field3 XML
);

INSERT INTO myTable (Field1, Field2, Field3) 
VALUES 
    ('<div class="Class1">
          <p>123</p>
          <p>456</p>
      </div>',
     '<div class="Class2">
          <p>abc</p>
          <p>def</p>
          </div>',
     '<div class="Class3">
          <p>XYZ</p>
          <p>AEIOU</p>
      </div>')

需要提取各列<p>标签内的值，单列使用nodes()函数可实现，但同时提取多列时会生成所有值的交叉组合（错误输出如下）：

Field1 Field2 Field3
----------------------
123    abc    XYZ
123    abc    AEIOU
123    def    XYZ
123    def    AEIOU
456    abc    XYZ
456    abc    AEIOU
456    def    XYZ
456    def    AEIOU

期望输出为按节点对应位置匹配的结果：

Field1 Field2 Field3
----------------------
123    abc    XYZ
456    def    AEIOU

已知单行列内XML节点数一致，但行间节点数可变化，是否可通过单个查询实现该需求？

解决方案 #

可以通过单个查询实现，核心是利用节点位置索引关联各列的对应节点，避免交叉组合。

实现代码 #

WITH NodesWithIndex AS (
    SELECT 
        f1.p.value('.', 'VARCHAR(100)') AS Field1Val,
        ROW_NUMBER() OVER(PARTITION BY mt.Field1 ORDER BY f1.p) AS NodeIndex,
        mt.Field2,
        mt.Field3
    FROM myTable mt
    CROSS APPLY mt.Field1.nodes('/div/p') f1(p)
)
SELECT 
    n.Field1Val AS Field1,
    f2.p.value('.', 'VARCHAR(100)') AS Field2,
    f3.p.value('.', 'VARCHAR(100)') AS Field3
FROM NodesWithIndex n
CROSS APPLY n.Field2.nodes('/div/p[position()=sql:column("n.NodeIndex")]') f2(p)
CROSS APPLY n.Field3.nodes('/div/p[position()=sql:column("n.NodeIndex")]') f3(p);

逻辑说明 #

1. 生成带位置索引的节点集：先对Field1使用nodes()拆分所有<p>节点，通过ROW_NUMBER()为每个节点生成在当前行内的位置序号NodeIndex，同时保留另外两列的XML数据。
2. 按索引匹配对应节点：对Field2和Field3使用nodes()时，通过position()=sql:column("n.NodeIndex")精准筛选出与Field1节点位置对应的<p>节点，这样就不会产生交叉组合，得到按位置匹配的结果。

该方案支持行间节点数变化的场景，只要单行列内三个XML的<p>节点数量一致，就能正确提取对应位置的值。

内容的提问来源于stack exchange，提问作者Sam CD