如何使用SQL将键值对表数据转换为XML格式

嘿，这个需求我碰到过好多次啦！不同的SQL数据库有各自的XML生成方式，我给你整理了几种主流数据库的实现方案，你可以根据自己用的数据库来选：

SQL Server 实现 #

SQL Server里用FOR XML PATH是最方便的方式，刚好能满足你按RecordID分组生成嵌套XML的需求：

SELECT 
    RecordID,
    (
        SELECT 
            KeyText AS '@Key',
            ValueText AS '@Value'
        FROM YourTable t2
        WHERE t2.RecordID = t1.RecordID
        FOR XML PATH('Field'), ROOT('LegacyDataItem'), TYPE
    ) AS LegacyDataXML
FROM YourTable t1
GROUP BY RecordID;

简单解释下：

• 内层子查询为每个RecordID生成<Field>节点，用@Key和@Value来设置属性
• FOR XML PATH('Field')指定每个行生成<Field>标签，ROOT('LegacyDataItem')把这些Field节点包裹在根标签里
• TYPE关键字确保输出的是真正的XML类型，而不是字符串

MySQL 实现 #

MySQL没有原生的XML聚合函数，不过我们可以用GROUP_CONCAT来拼接XML字符串，记得处理特殊字符转义：

SELECT 
    RecordID,
    CONCAT(
        '<LegacyDataItem>',
        GROUP_CONCAT(
            CONCAT(
                '<Field Key="', REPLACE(KeyText, '"', '&quot;'), '" Value="', REPLACE(ValueText, '"', '&quot;'), '" />'
            ) SEPARATOR ''
        ),
        '</LegacyDataItem>'
    ) AS LegacyDataXML
FROM YourTable
GROUP BY RecordID;

这里的REPLACE是为了避免KeyText或ValueText里的双引号破坏XML结构，把双引号转成XML实体"。

PostgreSQL 实现 #

PostgreSQL有专门的XML处理函数，用xmlagg来聚合XML节点会更规范：

SELECT 
    RecordID,
    xmlelement(
        name "LegacyDataItem",
        xmlagg(
            xmlelement(
                name "Field",
                xmlattributes(KeyText AS "Key", ValueText AS "Value")
            )
        )
    ) AS LegacyDataXML
FROM YourTable
GROUP BY RecordID;

• xmlelement用来生成单个XML元素，xmlattributes给元素添加属性
• xmlagg把同一个RecordID下的所有Field元素聚合起来，再外层包裹LegacyDataItem元素

你只需要把上面代码里的YourTable替换成你实际的表名就可以用啦！

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