如何在SQL Server中无需指定标签名通用提取XML列数据

示例XML字符串 #

<XML>
 <xml_line>
   <col1>1</col1>
   <col2>foo 1</col2>
 </xml_line>
 <xml_line>
   <col1>2</col1>
   <col2>foo 2</col2>
 </xml_line>    
</XML>

当前解析代码 #

目前将存储在@data_xml中的XML字符串存入SQL Server表并解析的代码如下：

-- 创建临时表并插入XML字符串
CREATE TABLE table1 (data_xml XML)

INSERT table1 
    SELECT @data_xml

-- 解析XML到临时表
SELECT
    N.C.value('col1[1]', 'int') col1_name,
    N.C.value('col2[1]', 'varchar(31)') col2_name
FROM 
    table1
CROSS APPLY 
    data_xml.nodes('//xml_line') N(C)

需求 #

想知道是否存在无需指定列名（如col1[1]、col2[1]）的通用方法，完成上述XML数据提取操作。

通用解决方案 #

在SQL Server中，可以通过动态SQL结合XML元数据识别实现通用解析，步骤如下：

1. 自动提取XML中的所有列名 #

先从XML里抓取xml_line节点下的所有唯一子节点名称（即目标列名）：

DECLARE @cols NVARCHAR(MAX)
SELECT @cols = STRING_AGG(QUOTENAME(c.value('local-name(.)', 'sysname')), ', ')
FROM table1
CROSS APPLY data_xml.nodes('//xml_line/*') AS t(c)
GROUP BY c.value('local-name(.)', 'sysname')

2. 生成并执行动态解析SQL #

利用提取到的列名，动态拼接通用解析语句并执行：

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT ' + @cols + '
FROM table1
CROSS APPLY data_xml.nodes(''//xml_line'') AS N(C)
CROSS APPLY (
    SELECT 
        C.value(''local-name(.)'', ''sysname'') AS col_name,
        C.value(''.'', ''nvarchar(max)'') AS col_value
    FROM N.C.nodes(''*'') AS T(C)
) AS src
PIVOT (
    MAX(col_value)
    FOR col_name IN (' + @cols + ')
) AS pvt'

EXEC sp_executesql @sql

补充说明 #

• 该方法自动适配xml_line下的所有子节点，无论列名、列数量如何变化都能解析
• 示例中统一将列类型设为nvarchar(max)，如果需要精准类型，可以额外添加逻辑判断（比如根据节点值尝试转换为int、datetime等）
• 若同一xml_line下存在同名节点，PIVOT会取最大值，这种场景需根据实际需求调整处理逻辑

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