如何利用FOR XML PATH或其他方法按列值生成层级XML结构？

基于以下DDL创建临时表并插入数据：

CREATE TABLE #Records
(
    ID INT IDENTITY PRIMARY KEY,
    ResourceKey VARCHAR(500),
    [Value] VARCHAR(500)
)

INSERT INTO #Records ([ResourceKey], [Value])
VALUES 
('Root/Person/Age', '30'),
('Root/Person/Gender', 'Male'),
('Root/Person/Education/ElementarySchool', 'Sunnyside'),
('Root/Person/Education/HighSchool', 'Green Acres')

需要生成如下格式的XML输出：

<Root>
    <Person>
        <Age>30</Age>
        <Gender>Male</Gender>
        <Education>
            <ElementarySchool>Sunnyside</ElementarySchool>
            <HighSchool>Green Acres</HighSchool>
        </Education>
    </Person>
</Root>

已知针对单个节点的查询可行，但无法适配多节点场景：

SELECT
    [Value] AS "Root/Person/Age"
FROM #Records
WHERE ID = 1
FOR XML PATH('')

补充说明：ResourceKey值保证唯一，实际场景中该表存储本地化字符串，需转换为指定XML格式。接受逐行迭代并插入XML文档的解决方案，但不熟悉SQL Server中XML文档的insert语法写法。

方法一：递归CTE拆分路径 + FOR XML PATH 聚合生成 #

无需迭代，通过拆分ResourceKey的层级结构，利用SQL Server的XML特性聚合生成嵌套XML：

WITH RecursivePaths AS (
    -- 初始化：拆分每个ResourceKey的第一个节点和剩余路径
    SELECT
        ID,
        ResourceKey,
        [Value],
        CAST(LEFT(ResourceKey, CHARINDEX('/', ResourceKey) - 1) AS VARCHAR(500)) AS NodeName,
        CAST(SUBSTRING(ResourceKey, CHARINDEX('/', ResourceKey) + 1, LEN(ResourceKey)) AS VARCHAR(500)) AS RemainingPath,
        1 AS Level
    FROM #Records
    WHERE CHARINDEX('/', ResourceKey) > 0

    UNION ALL

    -- 递归拆分剩余路径，直到路径为空
    SELECT
        ID,
        ResourceKey,
        [Value],
        CASE WHEN CHARINDEX('/', RemainingPath) > 0 THEN
            LEFT(RemainingPath, CHARINDEX('/', RemainingPath) - 1)
        ELSE
            RemainingPath
        END AS NodeName,
        CASE WHEN CHARINDEX('/', RemainingPath) > 0 THEN
            SUBSTRING(RemainingPath, CHARINDEX('/', RemainingPath) + 1, LEN(RemainingPath))
        ELSE
            ''
        END AS RemainingPath,
        Level + 1 AS Level
    FROM RecursivePaths
    WHERE RemainingPath <> ''
),
LeveledXML AS (
    -- 生成底层节点的XML片段
    SELECT
        ID,
        ResourceKey,
        Level,
        NodeName,
        [Value],
        CAST('<' + NodeName + '>' + [Value] + '</' + NodeName + '>' AS XML) AS XMLFragment
    FROM RecursivePaths
    WHERE RemainingPath = ''

    UNION ALL

    -- 递归向上包裹上层节点，生成完整路径的XML片段
    SELECT
        rp.ID,
        rp.ResourceKey,
        rp.Level,
        rp.NodeName,
        rp.[Value],
        CAST('<' + rp.NodeName + '>' + CAST(lx.XMLFragment AS VARCHAR(MAX)) + '</' + rp.NodeName + '>' AS XML) AS XMLFragment
    FROM RecursivePaths rp
    JOIN LeveledXML lx ON rp.ID = lx.ID AND rp.Level = lx.Level - 1
    WHERE rp.RemainingPath <> ''
)
-- 提取根层级的XML并合并输出
SELECT XMLFragment
FROM LeveledXML
WHERE Level = 1
FOR XML PATH(''), TYPE;

方法二：逐行迭代构建XML文档 #

使用SQL Server的XML数据类型和modify方法，通过游标迭代每条记录，动态插入节点：

DECLARE @ResultXML XML = '<Root/>';
DECLARE @ResourceKey VARCHAR(500), @Value VARCHAR(500);

-- 声明游标遍历所有记录
DECLARE record_cursor CURSOR FOR
SELECT ResourceKey, [Value] FROM #Records;

OPEN record_cursor;
FETCH NEXT FROM record_cursor INTO @ResourceKey, @Value;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 提取当前路径的最后一个节点名称
    DECLARE @NodeName NVARCHAR(500) = REVERSE(LEFT(REVERSE(@ResourceKey), CHARINDEX('/', REVERSE(@ResourceKey)) - 1));
    -- 转换ResourceKey为XML XPath格式
    DECLARE @ParentPath NVARCHAR(MAX) = REPLACE(LEFT(@ResourceKey, LEN(@ResourceKey) - LEN(@NodeName) - 1), '/', '/');
    
    -- 构建动态XML插入语句
    DECLARE @InsertStmt NVARCHAR(MAX) = N'
        SET @ResultXML.modify(''
            insert element ' + @NodeName + ' {sql:variable("@Value")}
            into (' + @ParentPath + ')[1]
        '');
    ';

    -- 执行动态语句插入节点
    EXEC sp_executesql @InsertStmt, 
        N'@ResultXML XML OUTPUT, @Value VARCHAR(500)', 
        @ResultXML OUTPUT, @Value;

    FETCH NEXT FROM record_cursor INTO @ResourceKey, @Value;
END

-- 清理游标
CLOSE record_cursor;
DEALLOCATE record_cursor;

-- 输出最终XML
SELECT @ResultXML;

两种方法对比 #

• 方法一性能更优，适合数据量不大的场景，通过递归拆分后聚合生成XML，无需游标迭代。
• 方法二更直观，逐行构建XML，适合复杂路径或需要自定义节点处理的场景，但游标在大数据量下性能会有所下降。

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