Oracle SQL: How to get xml from hierarchical table

The query you have provided will give you result you are expecting. You just have a couple of syntactical errors. First. parent_id and id_parent column names have a different position of id in your query. Make them the same. Second. remove last parenthesis ) from XMLElement(evalname(Name), text))

SQL> set long 300SQL> 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 createdSQL> 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>