以XML显示层次结构

以XML显示层次结构

如果您模拟

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 order1SELECt lvl, xmlelement("Node", xmlattributes(name AS "name", id AS "id"))FROM   t1ORDER 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>