<p>My table has the following schema:</p>

<p>id, parent_id, text</p>

<p>Given the following data I would like to return an xml hierarchy:</p>

<p>Data: (1,null,'x'), (2,1,'y'), (3,1,'z'), (4,2,'a')</p>

<p>XML:<br>
[row text="x"]<br>
 [row text="y"]<br>
  [row text="a"/]<br>
 [/row]<br>
 [row text="z"/]<br>
[/row]  </p>

<hr>

<p>Added: the hierachy has no maximum depth</p>

This requires a "transitive closure".  You need to process the data recursively to find all children under a given parent.
Roughly the algorithm looks like this.
```
```
for top in cursor( nodes where  each parent==null ):
    build_tree( top )

def build_tree( parent ):
    emit opening tag
    for child in cursor( nodes where parent == parent ):
        build_tree( child )
    emit closing tag
```
```
Note that some SQL interpreters may have trouble with the recursion -- they may not open a fresh, new cursor as necessary.  Each cursor, however, must be distinct, since you will have as many open cursors as your tree has levels.