SQL Server 2012如何使用XML方案拆分表中逗号分隔的字符串字段

实现方案（纯XML实现，无自定义函数/循环） #

你可以直接用以下查询对表的LIST字段做拆分，完全符合你的要求：

SELECT 
    t.ID,
    LIST = LTRIM(RTRIM(s.n.value('.', 'VARCHAR(1000)')))
FROM #EXAMPLE t
CROSS APPLY (
    -- 将逗号分隔的字符串转换为XML结构
    SELECT CAST('<X>' + REPLACE(t.LIST, ',', '</X><X>') + '</X>' AS XML) AS xml_data
) x
CROSS APPLY xml_data.nodes('/X') AS s(n)

逻辑说明 #

1. 先将LIST字段中的逗号替换为XML的节点闭合+开启标签</X><X>，前后拼接根节点标签<X>/</X>，把普通字符串转换为合法的XML片段
2. 用CROSS APPLY调用XML的nodes()方法，将每个<X>节点拆分为独立的行
3. 用value()方法取出节点内的文本，trim掉两端可能存在的空格即可得到拆分后的单个值

如果你的业务场景中LIST字段可能存在&、<、>这类XML特殊字符，会导致转换报错，可以用以下兼容版本的查询，自动处理特殊字符转义：

SELECT 
    t.ID,
    LIST = LTRIM(RTRIM(s.n.value('.', 'VARCHAR(1000)')))
FROM #EXAMPLE t
CROSS APPLY (
    SELECT CAST('<X>' + REPLACE((SELECT t.LIST AS [*] FOR XML PATH('')), ',', '</X><X>') + '</X>' AS XML) AS xml_data
) x
CROSS APPLY xml_data.nodes('/X') AS s(n)

用你提供的测试数据执行上述查询，输出结果和你需要的目标数据集完全一致。

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