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>

If you are in Estonia, then join our Oracle User Group Estonia Meetup #3. I’ll talk there about my latest trip to Oracle Open World, what I heard there and also I’ll present how to use CLONEDB feature.

This post is for those who google the errors and maybe it will help to save some time. I’ve been playing around with clonedb for some time now and every time I hit these errors it takes me some time to figure out what actually the problem is 🙂 The error messages are not really helpful at all. Maybe I’ll remember it now.

When creating a new clone database using clonedb then after creating the new controlfile you need to execute dbms_dnfs.clonedb_renamefile(sourcefile, deltafile) for each of the data files in the image copy (sourcefile) to create a file that will hold the changes done to this datafile (deltafile). For example if one of the data files in image copy is named /nfs/source/user_data.dbf and I want to store changes done to this file as /u02/db/delta/user_data.dbf then need to execute:

SQL> exec dbms_dnfs.clonedb_renamefile('/nfs/source/user_data.dbf', '/u02/db/delta/user_data.dbf');

Sometimes when I run it I get strange errors:

SQL> exec dbms_dnfs.clonedb_renamefile('/nfs/source/user_data.dbf', '/u02/db/delta/user_data.dbf');

ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6 - new file '/u02/db/delta/user_data.dbf' not found
ORA-01110: data file 6: '/nfs/source/user_data.dbf'
ORA-17503: ksfdopn:1 Failed to open file /u02/db/delta/user_data.dbf
ORA-17515: Creation of clonedb failed using snapshot file /nfs/source/user_data.dbf
ORA-06512: at "SYS.X$DBMS_DNFS", line 10
ORA-06512: at line 2

This happens when clonedb init.ora parameter is FALSE (default). So before running dbms_dnfs.clonedb_renamefile make sure clonedb is set to true. If it is not, then change the parameter and bounce the database.

SQL> show parameter clonedb

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
clonedb                              boolean     FALSE

SQL> alter system set clonedb=true scope=spfile;
SQL> startup mount force

PS. Starting from 12.1.0.2 the deltafile does not need to be stored in NFS server (and accessed using dNFS) and can be a local file instead, but the problem remains and the error comes from 12.1.0.2.