I need to create structured XML based on a simple parent/child
relationship. Take a table with three columns as an example:

Objects Table:
Unique_ID (primary key)
Parent_ID (foreign key to objects table unique_id)
Name

Data might look something like this:
1, NULL, 'Parent'
2, 1, 'Child 1'
3, 1, 'Child 2'
4, 2, 'Grandchild 1'
5, 2, 'Grandchild 2'
6, 3, 'Grandchild 3'
7, 3, 'Grandchild 4'

I want to turn this data into XML that looks like this:
<OBJECT id="1">
<OBJECT_NAME>Parent</OBJECT_NAME>
<OBJECT id="2">
<OBJECT_NAME>Child 1</OBJECT_NAME>
<OBJECT id="4">
<OBJECT_NAME>Grandchild 1</OBJECT_NAME>
</OBJECT>
<OBJECT id="5">
<OBJECT_NAME>Grandchild 2</OBJECT_NAME>
</OBJECT>
</OBJECT>
<OBJECT id="3">
<OBJECT_NAME>Child 1</OBJECT_NAME>
<OBJECT id="6">
<OBJECT_NAME>Grandchild 3</OBJECT_NAME>
</OBJECT>
<OBJECT id="7">
<OBJECT_NAME>Grandchild 4</OBJECT_NAME>
</OBJECT>
</OBJECT>
</OBJECT>

....using a single query. I know I can do this in Oracle 10, using
dbms_xmlgen.newcontextFromHierarchy and CONNECT BY. Does anyone have
any idea how do do this in Oracle 9i?? (specifically 9.2.0.7)

My only though so far is to manually constuct the XML in a CLOB using a
PLSQL function, but I'm loathed to do something so ugly.

Many thanks in advance for any contributions..

For anyone who is interested, I solved this using a recursive PLSQL
procedure.
It's not pretty, but it wasn't too much effort to write, and it works.

Hey Tim,

I found your thread from Google and I would like to see your code. I have run into the same issue - we are not upgrading to 10g until December and we need this context creation done in 9i by September. Any help would be greatly appreciated. Thank you!