Written by: ilmarkerm

Category: Blog entry

Published: October 29, 2014

Got an interesting question today: There are tables TABLE1, TABLE2 and a junction table that joins them called JUNCTABLE. Need the following output as XML

<table1>
  <table2></table2>
  <table2></table2>
</table1>
<table1>
  <table2></table2>
  <table2></table2>
</table1>

I know the output could be aggregated using XMLAGG, but I have never looked into how to format the tag names and attributed in the output like requested.

Data in my very simplified sample tables:

SQL> select * from table1;

        ID
----------
         1
         2
         3

SQL> select * from table2;

        ID
----------
        11
        12
        13
        14

SQL> select * from junctable;

     T1_ID      T2_ID
---------- ----------
         1         11
         1         12
         2         12
         2         13
         3         14

Expected output:

  <table1>
    <table2></table2>
    <table2></table2>
  </table1>
  <table1>
    <table2></table2>
    <table2></table2>
  </table1>
  <table1>
    <table2></table2>
  </table1>

Now the query itself:

SELECT XMLAGG (xmlpieces)
  FROM (  SELECT XMLELEMENT ("table1", xmlattributes (t1.id AS "id"), 
                   XMLAGG (XMLELEMENT ("table2", xmlattributes (t2.id AS "id")))
                 ) xmlpieces
            FROM table1 t1
                 JOIN junctable j ON j.t1_id = t1.id
                 JOIN table2 t2 ON t2.id = j.t2_id
        GROUP BY t1.id);

And the result:

  <table1>
    <table2></table2>
    <table2></table2>
  </table1>
  <table1>
    <table2></table2>
    <table2></table2>
  </table1>
  <table1>
    <table2></table2>
  </table1>