PLSQL: create XML from "hierarchical" table

Question

I have a table like this:

id | id_parent | tag_name
0  |           | TRANSACTION
1  |         0 | NAME
2  |         0 | ADDRESS
3  |         2 | CITY

and so on.

I want to create an xml structure with it so I'll have something like this:

<TRANSACTION>
  <NAME />
  <ADDRESS>
    <CITY />
  </ADDRESS>
</TRANSACTION>

I found some interesting features but I didn't find what I need...

PS: I don't need a "real" XML, the result structure can be even a varchar2 (but it could become very big) or a clob.

Answer 1

Try this:

select DBMS_XMLGEN.getXML(DBMS_XMLGEN.newcontextfromhierarchy('SELECT level,
XMLElement(evalname(TAG_NAME))
from my_table t
START WITH id_parent is null
CONNECT BY PRIOR id = id_parent'))
  FROM dual

my_table is the table name