title: Oracle SQL Generate nested xml
body: <p>I want to get a nested XML by using only one query in a PLSQL function for oracle.</p>
  
  <p>The database (cannot be changed):</p>
  
  <p><code>table 'products_details'</code>:</p>
  
  <pre><code>`attr_id` | `attribute`    | `fk_parent_id`(Foreign key on `attr_id`)
  -------------------------------------------------------------------------------
  1         |  name           | null
  3         |  sizes          | null
  4         |  size_women     | 3
  5         |  size_man       | 3
  6         |  size_dimension | 3
  </code></pre>
  
  <p><code>table 'product_contents'</code>:</p>
  
  <pre><code>`detail`      | `value`   |  variation_number  | `product_id` (doesnt matter)
  -------------------------------------------------------------------------------
  name          |  Tshirt   | null               | 1000
  price         |  14.99    | null               | 1000
  size_man      |  XL       | 1                  | 1000
  size_women    |  L        | 1                  | 1000
  size_dimesion |  21x25cm  | 1                  | 1000
  size_man      |  M        | 2                  | 1000
  size_women    |  S        | 2                  | 1000
  size_dimesion |  14x16cm  | 2                  | 1000
  ...
  </code></pre>
  
  <p>As you can see there are some options (name, price) only once for each product but there are also some options (size_man, size_woman...) that are variations and can exist more than one time for each product. </p>
  
  <p>What I want is an XML:</p>
  
  <pre><code>&lt;attribute detail="name"&gt;Tshirt&lt;/attribute&gt;
  &lt;attribute detail="price"&gt;14.99&lt;/attribute&gt;
  &lt;attribute detail="sizes"&gt;
        &lt;row variation_number="1"&gt;
            &lt;attribute detail="size_man"&gt;XL&lt;/attribute&gt;
            &lt;attribute detail="size_women"&gt;L&lt;/attribute&gt;
            ...
        &lt;/row&gt;
        &lt;row variation_number="2"&gt;
            &lt;attribute detail="size_man"&gt;M&lt;/attribute&gt;
            &lt;attribute detail="size_women"&gt;S&lt;/attribute&gt;
        &lt;/row&gt;
  &lt;/attribute&gt;
  </code></pre>
  
  <p>What I tried so far (which is of course not really working):</p>
  
  <pre><code>SELECT 
  (
      XMLELEMENT(  "attribute", 
  
                XMLATTRIBUTES(pc.detail as "detail"), 
  
                (SELECT XMLAGG
                  (
  
                  XMLELEMENT("row", XMLATTRIBUTES(pc.variant_number as "variation_number") )
  
                  )
  
                  FROM product_contents pc 
  
                  JOIN product_details pd ON pc.detail = pd.attribute and pc.product_id = '1000'
  
                  WHERE pd.fk_parent_id = pd.ID
  
                )      
  
  ).getClobVal() CONTENT
  
  FROM product_details pd   
  pd.fk_parent_id is null
  order by pd.attribute;
  </code></pre>
  
  <p>How can I do this with just a single query?</p>