如何将DB2分组生成XML列的查询转换为SQL Server 2019代码？

问题描述 #

现有一段可在DB2中正常运行的SELECT语句，按APPLICATION字段分组，生成ACCESS列及XML格式的USERLIST列，需要转换为SQL Server 2019兼容的版本，实现相同输出。

DB2原代码：

SELECT CASE
    WHEN APPLICATION = 'APP1' THEN 'APPLICATION1'
    WHEN APPLICATION = 'APP2' THEN 'APPLICATION2'
    ELSE NULL
END AS "ACCESS",
XMLSERIALIZE (
             CONTENT XMLELEMENT(
              NAME "Users",
              XMLAGG(
               XMLELEMENT(
                NAME "User",
                XMLELEMENT (NAME "NAME", USERID),
                XMLELEMENT (NAME "FIRST", RTRIM(FIRST)),
                XMLELEMENT (NAME "LAST", RTRIM(LAST)),
                XMLELEMENT (NAME "FROM", RTRIM(SUBMITTEDBY_ID)),
                XMLELEMENT (NAME "DISPLAYNAME", RTRIM(DISPLAYNAME))
               )
               ORDER BY APPLICATION
              )
             )
             AS CLOB (30K)
) AS "USERLIST"

GROUP BY APPLICATION
From RequestTable

预期输出包含两列：ACCESS和USERLIST（XML格式），示例如下：

SQL Server 2019等效实现 #

SQL Server具备类似的XML生成与聚合能力，可通过FOR XML PATH和STUFF函数实现分组聚合XML，替代DB2的XMLAGG和XMLELEMENT。转换后的代码如下：

SELECT 
    CASE
        WHEN APPLICATION = 'APP1' THEN 'APPLICATION1'
        WHEN APPLICATION = 'APP2' THEN 'APPLICATION2'
        ELSE NULL
    END AS [ACCESS],
    -- 生成外层Users节点，聚合组内User节点
    CAST('<Users>' + STUFF((
        SELECT 
            '<User>' +
            '<NAME>' + USERID + '</NAME>' +
            '<FIRST>' + RTRIM(FIRST) + '</FIRST>' +
            '<LAST>' + RTRIM(LAST) + '</LAST>' +
            '<FROM>' + RTRIM(SUBMITTEDBY_ID) + '</FROM>' +
            '<DISPLAYNAME>' + RTRIM(DISPLAYNAME) + '</DISPLAYNAME>' +
            '</User>'
        FROM RequestTable t2
        WHERE t2.APPLICATION = t1.APPLICATION
        ORDER BY t2.APPLICATION
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 0, '') + '</Users>' AS NVARCHAR(MAX)) AS [USERLIST]
FROM RequestTable t1
GROUP BY APPLICATION

关键差异说明 #

1. XML聚合方式：DB2用XMLAGG直接聚合XML元素，SQL Server通过FOR XML PATH('')生成无节点包裹的XML片段，再用STUFF拼接外层Users节点。
2. 类型转换：DB2用XMLSERIALIZE转为CLOB，SQL Server用CAST将XML片段转为NVARCHAR(MAX)（默认支持大文本，无需指定固定长度）。
3. 关联分组：通过自关联WHERE t2.APPLICATION = t1.APPLICATION实现分组内的XML元素聚合，替代DB2的GROUP BY配合XMLAGG的逻辑。

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