Oracle SQL Generate nested xml

Question

I want to get a nested XML by using only one query in a PLSQL function for oracle.

The database (cannot be changed):

table 'products_details':

`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

table 'product_contents':

`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
...

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.

What I want is an XML:

<attribute detail="name">Tshirt</attribute>
<attribute detail="price">14.99</attribute>
<attribute detail="sizes">
      <row variation_number="1">
          <attribute detail="size_man">XL</attribute>
          <attribute detail="size_women">L</attribute>
          ...
      </row>
      <row variation_number="2">
          <attribute detail="size_man">M</attribute>
          <attribute detail="size_women">S</attribute>
      </row>
</attribute>

What I tried so far (which is of course not really working):

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;

How can I do this with just a single query?

Answer 1

Here you are:

WITH
  -- "memory table"
  product_details AS (
    SELECT 1 attr_id, 'name' attr, null parent_id FROM dual UNION ALL 
    SELECT 2, 'price', null FROM dual UNION ALL 
    SELECT 3, 'sizes', null FROM dual UNION ALL 
    SELECT 4, 'size_women', 3 FROM dual UNION ALL 
    SELECT 5, 'size_man', 3 FROM dual UNION ALL 
    SELECT 6, 'size_dimension', 3 FROM dual
  ),
  -- "memory table"
  product_contents AS (
    SELECT 'name' detail, 'Tshirt' value, null variation, 1000 product_id FROM dual UNION ALL
    SELECT 'price', '14.99', null, 1000 FROM dual UNION ALL
    SELECT 'size_man', 'XL', 1, 1000 FROM dual UNION ALL
    SELECT 'size_women', 'L', 1, 1000 FROM dual UNION ALL
    SELECT 'size_dimesion', '21x25cm', 1, 1000 FROM dual UNION ALL
    SELECT 'size_man', 'M', 2, 1000 FROM dual UNION ALL
    SELECT 'size_women', 'S', 2, 1000 FROM dual UNION ALL
    SELECT 'size_dimesion', '14x16cm', 2, 1000 FROM dual
  ),
  product_contents_xml AS (
    SELECT   
      variation,
      detail,
      XMLELEMENT(
        "attribute",
        XMLATTRIBUTES(detail as "detail"),
        value
      ) attr,
      product_id
    FROM product_contents
  ),
  attrs AS (
      SELECT
        pc.attr
      FROM
        product_contents_xml pc JOIN 
        product_details pd ON pc.detail = pd.attr and pc.product_id = 1000
      WHERE
        pd.parent_id IS NULL

      UNION ALL

    SELECT
      XMLELEMENT("attribute", XMLATTRIBUTES(t.attr AS "detail"), XMLAGG(t.value)) attr
    FROM (  
          SELECT
            parent.attr,
            XMLELEMENT("row", XMLATTRIBUTES(pc.variation as "variation_number"), XMLAGG(pc.attr)) value
          FROM
            product_contents_xml pc JOIN 
            product_details pd ON pc.detail = pd.attr and pc.product_id = 1000 JOIN
            product_details parent ON parent.attr_id = pd.parent_id
          WHERE
            pd.parent_id IS NOT NULL
          GROUP BY
            parent.attr, pc.variation
    ) t
    GROUP BY t.attr
  )
SELECT XMLAGG(attr) FROM attrs

attrs view is splited into two parts - one for attributes without parent_id and one for those with parent_id.