使用递归SQL获取选中节点及上级记录的XML树形结构

示例数据表 #

id  name    manager_id
1   Alice   NULL
2   Bob     1
3   Charlie 2
4   David   3
5   Eve     1
6   Frank   5  

需求说明 #

选中表中id为4的记录时，需生成该节点及其所有上级节点的XML树形结构，将选中节点与上级节点以嵌套树形的XML形式返回。

现有函数问题 #

提供的递归SQL函数dbo.GetChildOrganisations逻辑是向下递归子节点，与需求的向上递归父节点方向相反，同时存在节点名称拼写错误（Orgainisations应为Organisations），无法满足需求。

CREATE FUNCTION dbo.GetChildOrganisations (@ParentID INT)
RETURNS XML
AS
BEGIN
    RETURN 
    (
        SELECT  id [@OrgID],
                name [@Name],
                manager_id [@Index],
                dbo.GetChildOrganisations(manager_id) [ChildOrganisations] 
        FROM    employees 
        WHERE   id = @ParentID
        FOR XML PATH('Orgainisations')
    )
END

修正后的递归函数 #

以下函数从指定节点开始向上递归，构建正确的上级树形XML结构：

CREATE FUNCTION dbo.GetParentHierarchy (@NodeID INT)
RETURNS XML
AS
BEGIN
    RETURN 
    (
        SELECT  id [@OrgID],
                name [@Name],
                manager_id [@ManagerID],
                -- 存在父节点时递归获取上级结构
                CASE WHEN manager_id IS NOT NULL THEN dbo.GetParentHierarchy(manager_id) END [SubOrganisation]
        FROM    employees 
        WHERE   id = @NodeID
        FOR XML PATH('Organisation'), TYPE
    )
END

使用方法 #

调用函数获取id=4节点的上级树形结构：

SELECT dbo.GetParentHierarchy(4)

返回结果示例 #

<Organisation OrgID="4" Name="David" ManagerID="3">
  <SubOrganisation OrgID="3" Name="Charlie" ManagerID="2">
    <SubOrganisation OrgID="2" Name="Bob" ManagerID="1">
      <SubOrganisation OrgID="1" Name="Alice" ManagerID="NULL" />
    </SubOrganisation>
  </SubOrganisation>
</Organisation>

关键修正点 #

• 调整递归方向：从目标节点向上遍历父节点，而非向下找子节点
• 修正节点名称拼写错误，统一使用Organisation作为节点标签
• 加入TYPE关键字保证XML结构的有效性，避免转义问题
• 通过CASE判断终止递归，当父节点为NULL时停止遍历

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