tsql - How do I retrieve hierarchic XML in t-sql? -

my table has following schema:

id, parent_id, text

given following data return 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: hierachy has no maximum depth

if have finite depth there's quickie looks this:

select t.*, t2.*, t3.* /*, ...*/ mytable t  inner join mytable t2 on t2.parent_id=t.id inner join mytable t3 on t3.parent_id=t2.id /* ... */ t.parent_id null xml auto 

i'm not sure might possible devise similar result using recursive queries. of course, it's easier (and makes more sense) in application level.