如何对SQL Server表XML列中的指定节点进行分组计数？

• 报错是由于子查询 select fd.Data from formdata fd where fd.FormType = '1X' 返回了多行结果，XML变量@Data只能接收单个XML值，无法批量存储多行XML数据。
• 原有写法仅能读取单个XML的第一个treatmentDiscussed节点，无法覆盖所有行的所有节点，也无法完成分组统计需求。

无需使用变量或临时表中转，直接通过SQL Server XML的nodes()方法拆解节点后分组统计即可，代码如下：

SELECT 
    TreatmentValue,
    COUNT(*) AS OccurrenceCount
FROM (
    -- 解析所有行的XML，提取所有treatmentDiscussed节点的取值
    SELECT 
        node.value('text()[1]', 'NVARCHAR(200)') AS TreatmentValue
    FROM formdata fd
    -- 将每行XML中匹配到的treatmentDiscussed节点拆分为独立行
    CROSS APPLY fd.stdFormData.nodes('//treatmentDiscussed') AS t(node)
    WHERE fd.FormType = '1X'
) AS AllValues
GROUP BY TreatmentValue
ORDER BY OccurrenceCount DESC

• 若实际存储XML的字段名为Data，将代码中的fd.stdFormData替换为fd.Data即可。
• 如果需要统计不含treatmentDiscussed节点的行，把CROSS APPLY替换为OUTER APPLY即可，此时缺失节点的行对应的TreatmentValue为NULL。
• 若XML存在命名空间，需要在查询开头添加WITH XMLNAMESPACES(DEFAULT '命名空间地址')声明命名空间后再解析。
• 节点值的返回长度NVARCHAR(200)可以根据实际业务场景调整。

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