以XML显示层次结构

小编典典

以XML显示层次结构

sql

我有下表：

员工（id int，名称varchar，managerid int）

   ID   NAME   MANAGERID
    1    A       2
    2    B       4
    3    C       4
    4    D      NULL

所需的输出：

<Node name="D" id="4">
   <Node name="B" id="2">
    <Node name="A" id="1">
    </Node>
   </Node>  
  <Node name="C" id="3">
  </Node>
</Node>

现在，我知道这不过是 深度优先搜索， 因此我完成了以下工作：

WITH t1(id,name,managerid) AS (
  -- Anchor member.
  SELECT id,
         name,
         managerid
  FROM   employee
    WHERE  managerid IS NULL
  UNION ALL
  -- Recursive member.
  SELECT t2.id,
         t2.name,
         t2.managerid
  FROM   employee t2, t1
  WHERE  t2.managerid = t1.id
)
SEARCH DEPTH FIRST BY id SET order1
SELECT id,
       name,
       managerid
FROM   t1
ORDER BY order1\\

和输出上述查询的是：

   ID  NAME MANAGERID
    4   D    NULL
    2   B    4
    1   A    2
    3   C    4

现在，我不知道如何将此输出转换为先前显示的xml版本。

我知道有诸如之类的函数XMLElement，XMLAGG但是我不知道如何在这种层次查询中使用它。

注意：当前，我正在 Oracle中 进行此操作，但是如果另一个RDBMS有更简单的方法来解决此问题，那么我将全力以赴。

阅读 183

2021-04-22

共1个答案

小编典典

如果您模拟level以connect by下列，则可以使用DBMS_XMLGEN.newcontextfromhierarchy和CTE完成此操作：

SELECT DBMS_XMLGEN.getXML(DBMS_XMLGEN.newcontextfromhierarchy('
with employee as (
  select 1 id, ''A'' name, 2 managerid from dual union all
  select 2 id, ''B'' name, 4 managerid from dual union all
  select 3 id, ''C'' name, 4 managerid from dual union all
  select 4 id, ''D'' name, null managerid from dual 
)
, t1(lvl,id,name,managerid) AS (
  -- Anchor member.
  SELECT 1 as lvl,
         id, 
         name,
         managerid
  FROM   employee
    WHERE  managerid IS NULL
  UNION ALL
  -- Recursive member.
  SELECT t1.lvl+1 as lvl, 
         t2.id, 
         t2.name,
         t2.managerid
  FROM   employee t2, t1
  WHERE  t2.managerid = t1.id
)
SEARCH DEPTH FIRST BY id SET order1
SELECT lvl, xmlelement("Node", xmlattributes(name AS "name", id AS "id"))
FROM   t1
ORDER BY order1
')) 
FROM dual

输出

<?xml version="1.0"?>
<Node name="D" id="4">
  <Node name="B" id="2">
    <Node name="A" id="1"/>
  </Node>
  <Node name="C" id="3"/>
</Node>

2021-04-22