converting flat xml data into heirarchy structure

Hi all,

I have a source document that is currently being
generated from an Oracle database - it is effectively
a table dump in xml format of the "scope" table..  It
has the following structure.

<scopes>
  <scope id="1" parent_id="0" details="some data"/>
  <scope id="2" parent_id="1" details="more data"/>
  <scope id="3" parent_id="1" details="info"/>
  <scope id="4" parent_id="2" details="blah"/>
  <scope id="5" parent_id="1" details="foo"/>
  <scope id="6" parent_id="2" details="log"/>
  <scope id="7" parent_id="4" details="ogg"/>
  <scope id="8" parent_id="4" details="abcd"/>
  <scope id="9" parent_id="2" details="xyz"/>
</scopes>

What I want to do is to re-organise this data so that
the xml properly represents its structure.  It forms a
tree using the parent_id to identify the parent scope
in a tree structure.

What I want to wind up with from the above data is
this:


<scopes>
 <scope id="1" parent_id="0" details="some data"/>
 <scopes>
  <scope id="2" parent_id="1" details="more data"/>
   <scopes>
    <scope id="4" parent_id="2" details="blah"/>
     <scopes>
      <scope id="7" parent_id="4" details="ogg"/>
      <scope id="8" parent_id="4" details="abcd"/>
     </scopes>
    <scope id="6" parent_id="2" details="log"/>
    <scope id="9" parent_id="2" details="xyz"/>
   </scopes>
  <scope id="3" parent_id="1" details="info"/>
  <scope id="5" parent_id="1" details="foo"/>
 </scopes>
</scopes>

Where each <scope> record has an associated <scopes>
that lists each of the <scope>
records that are related to the first scope as a
child/parent in the following format:


<scope>
  <scopes>
    ...list of child scopes here...
  </scopes>


I have been trying to fathom how to go about this,
mucking around with parameters, trying to write some
kind of recursive routine that will automatically sort
the scope records into this format, but thus far have
not been successful.  Could anyone give any hints as
to how I would go about this?

thanks heaps

David Buddrige



__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list