如何在SQL Server中将层级关系数据转换为指定格式XML

假设你的数据表（示例表名GeoData）包含Continent、Country、State三个字段，存储着大洲-国家-州的层级关联数据，可通过嵌套FOR XML PATH的方式生成目标格式XML，具体实现如下：

实现代码 #

SELECT
    Continent AS '@Name',
    (
        -- 生成当前大洲下的所有Country节点
        SELECT
            Country AS '@Name',
            (
                -- 生成当前国家下的所有State节点
                SELECT State AS '@Name'
                FROM GeoData c
                WHERE c.Continent = g.Continent AND c.Country = g.Country
                FOR XML PATH('State'), TYPE
            )
        FROM GeoData g
        WHERE g.Continent = main.Continent
        GROUP BY Country -- 确保每个Country只生成一个节点
        FOR XML PATH('Country'), TYPE
    )
FROM GeoData main
GROUP BY Continent -- 确保每个Continent只生成一个节点
FOR XML PATH('Continent'), ROOT('Geo'), TYPE

代码逻辑说明 #

1. 最内层查询：针对指定大洲和国家，生成带Name属性的<State>节点，TYPE参数用于保留XML原生结构，避免特殊字符被转义。
2. 中间层查询：按Country分组，为每个国家生成<Country>节点，并嵌套对应的<State>节点集合。
3. 最外层查询：按Continent分组，生成<Continent>节点，嵌套对应的<Country>节点集合，最后通过ROOT('Geo')添加根节点<Geo>。

补充说明 #

• 如果数据中存在重复的大洲/国家/州记录，GROUP BY会自动去重，确保每个层级节点唯一；若需保留重复节点，可去掉对应层级的GROUP BY，改用DISTINCT或根据业务需求调整过滤逻辑。
• 执行上述SQL后，输出的XML格式将完全匹配你给出的预期结果。

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