How do I retreive hierarchic XML in t-sql?

Question

My table has the following schema:

id, parent_id, text

Given the following data I would like to return an xml hierarchy:

Data: (1,null,'x'), (2,1,'y'), (3,1,'z'), (4,2,'a')

XML:
[row text="x"]
[row text="y"]
[row text="a"/]
[/row]
[row text="z"/]
[/row]

---

Added: the hierachy has no maximum depth

Answer 1

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.

Answer 2

If you have a finite depth the there's a quickie that looks like this:

SELECT T.*, T2.*, T3.* /*, ...*/ FROM myTable T 
INNER JOIN myTable T2 ON T2.parent_id=T.id
INNER JOIN myTable T3 ON T3.parent_id=T2.id
/* ... */
WHERE T.parent_id IS NULL
FOR XML AUTO

I'm not sure but it might be possible to devise a similar result using recursive queries. Of course, it's much easier (and probably makes more sense) in the application level.