如何将SQL Server表中XML列解析为多行数据？

环境：SQL Server Standard 2017 on Windows Server 2019

现有存储自定义数据的表CustomerCrossReferences，结构如下：

create table CustomerCrossReferences 
(
    customer BigInt, 
    product BigInt, 
    customColumns XML
)

customColumns列的XML数据示例：

insert into CustomerCrossReferences 
values (69731, 157,  
'<CustomColumnsCollection>
  <CustomColumn>
    <Name>MOI</Name>
    <DataType>0</DataType>
    <Value>10% max</Value>
  </CustomColumn>
  <CustomColumn>
    <Name>PRO</Name>
    <DataType>0</DataType>
    <Value>26% min</Value>
  </CustomColumn>
  <CustomColumn>
    <Name>FAT</Name>
    <DataType>0</DataType>
    <Value />
  </CustomColumn>
  <CustomColumn>
    <Name>WAT</Name>
    <DataType>0</DataType>
    <Value />
  </CustomColumn>
  <CustomColumn>
    <Name>FMT</Name>
    <DataType>0</DataType>
    <Value>None</Value>
  </CustomColumn>
</CustomColumnsCollection>')

需要为每个Customer/Product配对提取对应的Name、DataType和Value，期望数据集格式：

Customer    Product    Name   DataType   Value
-------------------------------------------------
69731       157        MOI    0          10% max
69731       157        PRO    0          26% min
69731       157        FAT    0          
69731       157        WAT    0          
69731       157        FMT    0          

目前通过多次UNION ALL拼接查询实现，但当CustomColumn元素多达20个时操作繁琐，寻求更简洁高效的查询方式，或能否直接在Report Builder中处理XML数据。

1. SQL端高效拆分XML节点 #

使用SQL Server的nodes()方法可直接将XML中的每个<CustomColumn>节点拆分为行，无需手动拼接UNION ALL，支持任意数量节点：

select
    c.customer,
    c.product,
    col.value('(Name/text())[1]', 'varchar(max)') as Name,
    col.value('(DataType/text())[1]', 'varchar(max)') as DataType,
    col.value('(Value/text())[1]', 'varchar(max)') as Value
from CustomerCrossReferences c
cross apply c.customColumns.nodes('/CustomColumnsCollection/CustomColumn') as t(col)

• nodes()方法将XML路径匹配到的每个节点生成一行记录
• cross apply将原表每行数据与拆分后的XML节点行关联
• 使用text()提取节点文本值，可减少XML解析开销，提升性能

2. Report Builder中直接处理XML数据 #

若不想修改SQL查询，可在Report Builder内处理XML：

• 步骤1：将完整customColumns列数据纳入数据集
• 步骤2：通过列表控件遍历XML节点：
  1. 添加列表控件，绑定到包含customColumns的数据集
  2. 编辑列表分组，使用表达式=Fields!customColumns.Value.SelectNodes("/CustomColumnsCollection/CustomColumn")遍历每个节点
  3. 在列表内添加文本框，分别绑定以下表达式提取值：
     • 客户：=Fields!customer.Value
     • 产品：=Fields!product.Value
     • Name：=Fields!customColumns.Value.SelectSingleNode("Name").Value
     • DataType：=Fields!customColumns.Value.SelectSingleNode("DataType").Value
     • Value：=IIF(Fields!customColumns.Value.SelectSingleNode("Value") Is Nothing, "", Fields!customColumns.Value.SelectSingleNode("Value").Value)

注：Report Builder处理XML的性能略逊于SQL端，数据量较大时优先推荐SQL端nodes()方法。

内容的提问来源于stack exchange，提问作者Stuart Riches