xml层级工具,以XML显示层次结构

I have the following table:

Employee (id int,name varchar,managerid int)

ID NAME MANAGERID

1 A 2

2 B 4

3 C 4

4 D NULL

Desired Output:

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\\

And output of above query is :

ID NAME MANAGERID

4 D NULL

2 B 4

1 A 2

3 C 4

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

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

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

解决方案

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

output