Database Software

Hi All,
I have a self-referential relational table (ie, fk to self) to establish hierarchical, parent-child relationships (item_id is PK and item_id_parent references item_id as FK) between items. There can be an arbitrary level of depth down any branch of a tree. I can issue a basic "connect by" query and get back a correct hierarchical representation along the lines of the following (for a chosen root):

root
sub1
sub1,1
sub1,1,1
sub1,1,2
sub1,2
sub2
sub3
sub3,1
sub3,2
sub3,2,1

I need to generate a hierarchical XML document to represent this along the lines of:

<base>
<name>root</name>
<subs>
_{<name>sub1</name>
<subs>
_{<name>sub1,1</name>
<subs>
_{<name>sub1,1,1</name>}
_{<name>sub1,1,2</name>}
</subs>}
_{<name>sub1,2</name>}
</subs>}
_{<name>sub2</name>}
...
</subs>
</base>

I originally anticipated using XMLELEMENT, XMLForest, XMLAGG, etc, but I can't quite figure out how to handle the arbitrary nature of the depth of a branch. I can build a 'flat' xml document (containing all subs, in the right order, but not hierarchically nested below parents) or I can build the base and some pre-determined level of depth of the subs, but not an arbitrary level of depth. I next tried using types with dbms_xmlgen.newContext and cast(multiset(select...)), but again, I can only do it for a predetermined level of depth (ie, not in a recursive manner).

Can someone please give me a hint or two on this??

Thanks Much!!!!

Jim Stoll