Xml select oracle request

Question

I have a table like this:

Type    Root    Description     hierarchy_level
Event   1       Jump            1
Event   10      Dance           2
Event   2       Cough           1
Event   20      Sneeze          2
Event   21      Spin            2
Event   21      Run             3
Event   21      Walk            4

I want the xml result to be:

<Event
    <child desc="Jump">
        <grandchild desc="Dance"/>
    </child>  
    <child desc="Cough">
        <grandchild desc="Sneeze"/>
        <grandchild desc="Spin">
            <ggchild desc="Run">
                <gggchild desc="Walk"/>
            </ggchild>
        </grandchild
    </child>  
</Event>

that's what i came up with but it's not dynamic

SELECT XMLELEMENT
        ("Event",                
            (
                SELECT XMLAGG
                (
                    XMLELEMENT
                    (
                        "CHILD",
                        XMLATTRIBUTES
                        (oc2.description as "desc"),
                        XMLFOREST
                        (
                            oc2.description as "desc"
                        )
                    ) 
                )
                FROM table1 OC2
                WHERE OC2.CASE_CODE_TYPE = OC.CASE_CODE_TYPE 
                    AND OC2.HIERARCHY_LEVEL =2             
            )         
        ) AS "RESULT"
FROM table1 oc
WHERE oc.HIERARCHY_LEVEL = 1 and oc.TYPE = 'Event';

as you can see, I need to set the hierarchical level of the hierarchical code in order to get the first child. Now I do not want to do this at any other level. because if he has 8, then I have to repeat 8 times. Therefore, if someone can help point me in the right direction to make this dynamic, I really appreciate it. Thank you for your help.

Answer 1

select 
    xmltype('<event>'||dbms_xmlgen.convert(xmlagg(xmlelement(e,open_tag ||' desc="'||description||'"'||close_tags)).extract('//text()').getclobval(),1)||'</event>') out_xml
    from
    (
    select e.*
    ,case 
        when tt>hierarchy_level then '>' 
--Oracle 10g
                else '/>'||(select  to_char(dbms_xmlgen.convert(xmlagg(xmlelement("e",'</'||case level when 2 then 'grand' else lpad('g',level-1,'g') end||'child>') order by level desc).extract('//text()').getClobVal(),1))  from dual where level>=tt connect by level<hierarchy_level)
--Oracle 11g
--      else '/>'||(select listagg('</'||case level when 2 then 'grand' else lpad('g',level-1,'g') end||'child>') within group(order by level desc) from dual where level>=tt connect by level<hierarchy_level) 
    end close_tags
    from
    (
        select 
                s.*
                ,'<'||case hierarchy_level when 2 then 'grand' else lpad('g',hierarchy_level-1,'g') end||'child' open_tag
                ,lead(hierarchy_level,1,1) over (order by to_char(root),hierarchy_level) tt from table1 s
        order by to_char(root),s.hierarchy_level
    )e
    )

This query works on your example. If the xml result is always less than 4000 characters, you can replace xmlagg and dbms_convert with listagg.