您提供的查询将会给您期望的结果。只是您有几个语法错误。首先,
parent_id
和
id_parent
列名在您的查询中
id
的位置不同。请将它们改为相同。其次,请从
XMLElement(evalname(Name), text))
中删除最后一个括号
)
。
SQL> set long 300
SQL> create table t1 (id1, parent_id, Name1, Text) as(
2 select 0, null, 'body_text', null from dual union all
3 select 1, 0, 'text', 'something' from dual union all
4 select 2, 0, 'blank', null from dual union all
5 select 3, 2, 'text' , 'something' from dual union all
6 select 4, null, 'info', null from dual union all
7 select 5, 4, 'text', 'something' from dual
8 );
Table created
SQL> select DBMS_XMLGEN.getXML(DBMS_XMLGEN.newcontextfromhierarchy('SELECT level,
2 XMLElement(evalname(Name1), text)
3 from t1 t
4 start with parent_id is null
5 connect by prior id1 = parent_id')) xml
6 from dual
7 ;
xml
<body_text>
<text>something</text>
<blank>
<text>something</text>
</blank>
</body_text>
<info>
<text>something</text>
</info>
SQL> select '<?xml version="1.0" ?>'
2 || chr(10)
3 || DBMS_XMLGEN.getXML(DBMS_XMLGEN.newcontextfromhierarchy('SELECT level,
4 XMLElement(evalname(Name1), text)
5 from t1 t
6 start with parent_id is null
7 connect by prior id1 = parent_id')) as xml
8 FROM dual
9 ;
XML
<?xml version="1.0" ?>
<body_text>
<text>something</text>
<blank>
<text>something</text>
</blank>
</body_text>
<info>
<text>something</text>
</info>