Hi all,

I have a table as follows:

ID PARENT NAME
1 - root
2 1 superuser
3 1 basicuser
4 1 blockeduser
5 2 ian
6 2 bob
7 3 tim
8 3 chaz
9 4 dave
10 4 seymour

I would like to convert this to an XML tree structure using the parent column to know which is the parent id, forming something like this:

<manager name="root">
<team_member name="superuser">
<team_member name="ian" />
<team_member name="bob" />
</team_member>
<team_member name="basicuser">
<team_member name="tim" />
<team_member name="chaz" />
</team_member>
<team_member name="blockeduser">
<team_member name="dave" />
<team_member name="seymour" />
</team_member>
</manager>

I do not know how many levels the tree has, so I need to recursively traverse it. I can get down to a known amount of levels by using and extending something like the following structure:

SELECT XML2CLOB(XMLELEMENT(name "manager", XMLATTRIBUTES(name AS "name"), (SELECT XMLAGG(XMLELEMENT(NAME "team_member",XMLATTRIBUTES(name AS "name"))) from temp.parents p where p.parent=i.id))) from temp.parents i where parent is null'

I'm not worried about the element names, as obviously I can't name these recusively if I don't know how many levels there are, but if anyone can give any pointers on where to start with the recursion I'd really appreciate it!

Many thanks in advance,
Ian