SQLite树形结构查询：如何自动生成XML式开闭标签？

我来帮你搞定这个需求！要把原来的逗号分隔列表改成嵌套的XML标签，核心是要让递归查询不仅遍历节点，还要为每个节点生成对应的开闭标签，并且正确拼接子节点的内容。

思路解析 #

你的原查询已经通过OrderedOrg处理了兄弟节点的顺序，这个部分可以保留——它通过sibling字段计算每个节点在兄弟中的排序值，确保我们能按预期的顺序（比如Cindy在Bob之前，Gail在Fred之前）排列子节点。

接下来我们需要调整递归逻辑，从叶子节点开始向上构建XML：

1. 叶子节点（没有子节点的节点）直接生成<name></name>的闭合标签。
2. 非叶子节点则拼接自己的开始标签、所有子节点的XML内容（按兄弟顺序排序），再加上自己的结束标签。

完整查询语句 #

WITH RECURSIVE
  -- 维持原逻辑：计算每个节点的兄弟顺序，保证子节点排列符合预期
  OrderedOrg(id, name, boss, SiblingOrder) AS (
    SELECT id, name, boss, 0
    FROM org
    WHERE sibling IS NULL
    UNION ALL
    SELECT org.id, org.name, org.boss, o.SiblingOrder + 1
    FROM org
    JOIN OrderedOrg o ON org.boss = o.boss AND org.sibling = o.id
  ),
  -- 递归生成XML标签：从叶子节点向上构建
  OrgXML(id, xml) AS (
    -- 第一步：处理叶子节点，生成闭合标签
    SELECT o.id, '<' || o.name || '></' || o.name || '>' AS xml
    FROM org o
    WHERE NOT EXISTS (SELECT 1 FROM org WHERE boss = o.id)
    UNION ALL
    -- 第二步：处理非叶子节点，拼接子节点的XML内容
    SELECT 
      parent.id,
      '<' || parent.name || '>' || GROUP_CONCAT(child.xml, '') || '</' || parent.name || '>' AS xml
    FROM org parent
    JOIN OrderedOrg child_ord ON parent.id = child_ord.boss
    JOIN OrgXML child ON child_ord.id = child.id
    WHERE parent.id NOT IN (SELECT id FROM OrgXML)
    GROUP BY parent.id, parent.name
    -- 按兄弟顺序降序排列，保证Cindy在Bob前、Gail在Fred前
    ORDER BY child_ord.SiblingOrder DESC
  )
-- 查询根节点（Alice）的最终XML结果
SELECT xml FROM OrgXML WHERE id = 0;

结果验证 #

执行这个查询后，你会得到完全符合预期的结果：

<Alice><Cindy><Gail></Gail><Fred></Fred></Cindy><Bob><Dave></Dave><Emma></Emma></Bob></Alice>

关键细节说明 #

• OrderedOrg的作用是确保子节点的排列顺序和你原查询一致，通过SiblingOrder降序实现兄弟节点的预期排序。
• OrgXML从叶子节点开始递归，这样每个父节点都能收集到所有子节点已经生成好的XML内容，再拼接自己的开闭标签，自然形成嵌套结构。

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