Generating Hierarchical XML using CONNECT BY with Oracle 9i

From: <Tim_at_McHale.info>
Date: 22 Mar 2006 04:26:00 -0800
Message-ID: <1143030360.762224.226020@i39g2000cwa.googlegroups.com>

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.. Received on Wed Mar 22 2006 - 06:26:00 CST