SQL Server如何动态解析XML列的全部属性及节点文本值

完整实现代码 #

SELECT 
    'Step' AS [Node],
    xmlData.o.value('@No', 'varchar(32)') AS [Step],
    attr.value('local-name(.)', 'varchar(64)') AS [Key],
    attr.value('.', 'varchar(128)') AS [Value]
FROM [dbo].[dw_mrd_vss_rundetail_stg] S
CROSS APPLY S.[rundata_detail].nodes('Step') xmlData(o)
-- 拆分当前Step节点的所有属性
CROSS APPLY xmlData.o.nodes('@*') AS attrs(attr)
UNION ALL
-- 拼接Step节点的文本值
SELECT 
    'Step' AS [Node],
    xmlData.o.value('@No', 'varchar(32)') AS [Step],
    'Action' AS [Key],
    xmlData.o.value('(text())[1]', 'varchar(128)') AS [Value]
FROM [dbo].[dw_mrd_vss_rundetail_stg] S
CROSS APPLY S.[rundata_detail].nodes('Step') xmlData(o)
WHERE xmlData.o.exist('(text())[1]') = 1
ORDER BY [Step], [Key]

代码说明 #

• 第一层CROSS APPLY ... nodes('Step')逻辑和原有写法一致，将XML列中所有节点拆分为独立行，每行对应一个Step节点，xmlData.o为当前Step节点的引用。
• 第二层CROSS APPLY ... nodes('@*')使用XPath通配符@*匹配当前Step节点的所有属性，将每个属性拆分为独立行，无需提前指定属性名。
• local-name(.)是XPath内置函数，用于获取当前属性的名称，作为结果的Key列值。
• UNION ALL部分用于将Step节点的文本值统一加入结果集，固定Key为Action，WHERE条件过滤无文本的空Step节点，避免返回无效NULL行。
• 可根据实际业务中属性的长度、编码要求，调整varchar为合适长度，或替换为nvarchar支持中文属性值。

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