title: xml select query oracle
body: <p>I have table like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I want the xml result to be:</p>
  
  <pre><code>&lt;Event
      &lt;child desc="Jump"&gt;
          &lt;grandchild desc="Dance"/&gt;
      &lt;/child&gt;  
      &lt;child desc="Cough"&gt;
          &lt;grandchild desc="Sneeze"/&gt;
          &lt;grandchild desc="Spin"&gt;
              &lt;ggchild desc="Run"&gt;
                  &lt;gggchild desc="Walk"/&gt;
              &lt;/ggchild&gt;
          &lt;/grandchild
      &lt;/child&gt;  
  &lt;/Event&gt;
  </code></pre>
  
  <p>this is what i came up with but it's not dynamic</p>
  
  <pre><code>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';
  </code></pre>
  
  <p>as you can see i have to hard code hierarchy_level in order to get the first child. now i don't want to do that for any other level. because if it has 8 then i have to repeat 8 times. So if someone can help pointing me to the right direction to make this dynamic i really appreciate it.<br>
   Thank you for your help.</p>