Stack Exchange
Oracle SQL Hierarchy to specific XML
I'm looking for a query which can turn a hierarchy table to an XML format. Now i've been looking online for several solutions but I can't seem to get the correct output.
What I have is the following:
INSERT INTO "NODES" (ID, NAME) VALUES (1, 'test parent');
INSERT INTO "NODES" (ID, PARENTID, NAME) VALUES (2,1,'test child 1');
INSERT INTO "NODES" (ID, PARENTID, NAME) VALUES (3,1,'test child 2');
INSERT INTO "NODES" (ID, PARENTID, NAME) VALUES (4,3,'test child 3');

And now i'm trying to get the following:
<ns:nodes xmlns:ns="test" xmlns:ns1="test1" xmlns:ns2="test2">
    <ns1:node>
        <ns2:name>test parent</name>
        <!-- extra info -->
        <nodes>
            <node>
                <name>test child 1</name>
                <!-- extra info and child nodes -->
            </node>
            <node>
                <name>test child 2</name>
                <nodes>
                    <node>
                        <name>test child 3</name>
                        <!-- extra info and child nodes -->
                    </node>
                </nodes>
            </node>
        </nodes>
    </node>
</nodes>

Note that the nodes element is an element in the parent node element.
Here is what I've tried so far:
SELECT DBMS_XMLGEN.getXMLType(
     DBMS_XMLGEN.newContextFromHierarchy('
        SELECT LEVEL,
           case
             when CONNECT_BY_ISLEAF = 1 then
              XMLELEMENT("nodes", XMLELEMENT("ns1:node",
                    xmlattributes(''test'' as "xmlns:ns", 
                                ''test1'' as "xmlns:ns1",
                                ''test2'' as "xmlns:ns2"),
                xmlelement("ns2:name", n.name)))
             when CONNECT_BY_ISLEAF = 0 then
              XMLELEMENT("nodes", XMLELEMENT("ns1:node",
                    xmlattributes(''test'' as "xmlns:ns", 
                                ''test1'' as "xmlns:ns1",
                                ''test2'' as "xmlns:ns2"),
                xmlelement("ns2:name", n.name)), null)
           end
          FROM NODES n
         START WITH n.id = 1
        CONNECT BY PRIOR n.ID = n.PARENTID '
        )
       ) FROM DUAL;

But this gives the following:
<nodes>
  <ns1:node xmlns:ns="test" xmlns:ns1="test1" xmlns:ns2="test2">
    <ns2:name>test parent</ns2:name>
  </ns1:node>
  <nodes>
    <ns1:node xmlns:ns="test" xmlns:ns1="test1" xmlns:ns2="test2">
      <ns2:name>test child 1</ns2:name>
    </ns1:node>
  </nodes>
  <nodes>
    <ns1:node xmlns:ns="test" xmlns:ns1="test1" xmlns:ns2="test2">
      <ns2:name>test child 2</ns2:name>
    </ns1:node>
    <nodes>
      <ns1:node xmlns:ns="test" xmlns:ns1="test1" xmlns:ns2="test2">
        <ns2:name>test child 3</ns2:name>
      </ns1:node>
    </nodes>
  </nodes>
</nodes>

But as you can see the nodes element is not inside the parent node. Also the nodes element is generated for each node and there is no grouping. Is there an easy way to do this in Oracle SQL?
I'm using Oracle DB 12.2.0.1.
Thanks in advance.

Your sample data has two nodes with ID 3 which doesn't look right, and the column name should be code not name I think? In your expected output where should test child 3 sit; and is there a limit to the number of levels in the hierarchy? Also, which version of Oracle are you using?

There is no technical limit to the structure. While we will be limiting this on a business level we do not wish to limit it in the code as this may change depending on the need of the consumers. Also sorry, I did not actually test with these queries, this example is actualy an example and shrunk down version of my actual query where I do use code instead of name. I will correct it.
'Test child 3' should be child of 'Test child 1'. I added this one to make clear that the root's children can also have children (but forgot to change the ID).

Oh and I'm using Oracle DB 12.2.0.1 to do this.

Your example output does not contain "test child 3" and position of "test child 2" does not look logical.

Sorry, i mistyped the name. Basically you have a  element and inside you have all the child  listed. Every  can contain child nodes grouped by a  element. I've corrected everything and tested it. So it should all be correct now.
