Oracle递归动态查询生成层级XML输出需求求助

1. 创建并填充关联关系表 #

先把父子表的关联数据存入专门的关系表，示例SQL如下：

CREATE TABLE TableRelations (
    TableName VARCHAR(50) PRIMARY KEY,
    ParentTable VARCHAR(50),
    PKColumn VARCHAR(50),
    ParentPKColumn VARCHAR(50)
);

INSERT INTO TableRelations VALUES
('HR', NULL, 'Id', NULL),
('Department', 'HR', 'Deptid', 'Id'),
('Emp', 'Department', 'Depid', 'depid');

2. 编写生成XML的递归函数 #

这个函数会自动遍历表的层级关系，动态生成嵌套XML：

CREATE FUNCTION dbo.GenerateHierarchicalXML()
RETURNS XML
AS
BEGIN
    DECLARE @XML XML;
    DECLARE @SQL NVARCHAR(MAX);

    -- 递归遍历从HR开始的所有表层级
    WITH TableHierarchy AS (
        SELECT 
            TableName,
            ParentTable,
            PKColumn,
            ParentPKColumn,
            1 AS Level
        FROM TableRelations
        WHERE TableName = 'HR'
        UNION ALL
        SELECT 
            tr.TableName,
            tr.ParentTable,
            tr.PKColumn,
            tr.ParentPKColumn,
            th.Level + 1 AS Level
        FROM TableRelations tr
        JOIN TableHierarchy th ON tr.ParentTable = th.TableName
    )
    -- 动态拼接生成XML的SQL语句
    SELECT @SQL = STRING_AGG(
        CASE Level
            WHEN 1 THEN 'SELECT (SELECT * FROM ' + TableName + ' FOR XML PATH(''' + TableName + '''), TYPE)'
            ELSE ', (SELECT * FROM ' + TableName + ' WHERE ' + ParentPKColumn + ' = ' + ParentTable + '.' + PKColumn + ' FOR XML PATH(''' + TableName + '''), TYPE)'
        END, ' '
    ) WITHIN GROUP (ORDER BY Level)
    FROM TableHierarchy;

    SET @SQL = 'SELECT ' + @SQL + ' FOR XML PATH(''''), ROOT(''Root'')';

    -- 执行动态SQL得到XML结果
    EXEC sp_executesql @SQL, N'@XML XML OUTPUT', @XML = @XML OUTPUT;

    -- 把默认的Root根节点替换成HR
    SET @XML = CAST(REPLACE(CAST(@XML AS NVARCHAR(MAX)), '<Root>', '<HR>') AS XML);
    SET @XML = CAST(REPLACE(CAST(@XML AS NVARCHAR(MAX)), '</Root>', '</HR>') AS XML);

    RETURN @XML;
END;

3. 调用函数生成XML #

执行以下语句即可得到预期的层级XML：

SELECT dbo.GenerateHierarchicalXML();

关键说明 #

• 后续新增任意层数的子表，只要在TableRelations里维护好正确的父子关系，函数都能自动适配
• 生成的XML严格按照表的层级嵌套，和示例结构完全一致
• 用TYPE关键字保证子节点的XML结构不会被转义，保留正确的嵌套格式

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