以XML显示层次结构 [英] Display the hierarchy in XML

问题描述

我有下表:

员工(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>

现在，我知道这不过是深度优先搜索，所以我已经完成了以下操作:

Now, I know this is nothing but Depth First Search so I've done following:

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版本.

Now, I don't know how to convert this output into xml version displayed earlier.

我知道有XMLElement，XMLAGG等功能，但是我不知道如何在这种分层查询中使用它.

I know there are functions like XMLElement, XMLAGG etc but i don't know how to use that in this sort of hierarchical query.

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

Note : Currently, I am doing this in Oracle but if another RDBMS has easier way to solve this then I am all for it.

推荐答案

如果模拟connect by的level列，则可以使用DBMS_XMLGEN.newcontextfromhierarchy和CTE来完成此任务:

You can accomplish this with DBMS_XMLGEN.newcontextfromhierarchy and CTE if you emulate level column of connect by:

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>

这篇关于以XML显示层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！