Wednesday, October 29, 2014

Structured XML output from Oracle SQL query

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


  
  


  
  

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:

  
    
    
  
  
    
    
  
  
    
  

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:

  
    
    
  
  
    
    
  
  
    
  

No comments:

Post a Comment