SQLX How to easily generate XML

I recently had the need to generate XML files based on data stored in relational tables. This was done via an XML DOM implementation on the project I am currently working on. This works fine, but it’s difficult to maintain. Modifications have to be made simple because the XSD’s are not stable yet.

This was asking for a new approach. The decision was made to go for SQLX after a discussion with my colleague architect Aino. SQLX gives you a very simple way to generate XML just by using SQL statements. Time for a first example:

  1      SELECT XMLElement("TheDate", sysdate
  2             )
  3*     FROM dual;
XMLELEMENT("THEDATE",SYSDATE)
---------------------------------------------
07-OCT-05

The return type of the XMLElement funtion is of type xmltype. The xmltype datatype is an object which gives you a lot of nifty methods that can be used to modify (xmltype.updateXML) or create XML (xmltype.createxml(‘07-OCT-05‘). There are also methods available for stylesheets conversions of schema validations.

The example above can easily be modified to return a clob instead of an XML type variable. Just add the .getClobVal() method:

  1      SELECT XMLElement("TheDate", sysdate
  2             ).getClobVal()
  3*     FROM dual

There is more than XMLElement. You will at least need to use XMLattribute XMLAgg and XMLForest to generate your XML data. The following example shows how to generate attributes in your XML:

  1  select XMLElement("TableName", XMLAttributes(owner as "Owner"), table_name
  2         ).getCLobVal()
  3  from all_tables
  4* where table_name like 'MY%'
SQL> /

XMLELEMENT("TABLENAME",XMLATTRIBUTES(OWNERAS"OWNER"),TABLE_NAME).GETCLOBVAL()
--------------------------------------------------------------------------------
MYTABLE

Tables can have more than one column. You will need to use the XMLagg function to aggregate a repeating list into your XML data definition. The following query shows how to query the columns of our example table:

select XMLElement("MyDatabase"
       , XMLElement("table", XMLAttributes(tab.table_name as "name")
           , XMLAgg((select XMLAgg(XMLElement("ColumnName", col.column_name))
              from   all_tab_columns col
              where  col.table_name = tab.table_name
              and    col.owner      = tab.owner
                     )
             )
         )
       ).getCLobVal()
from all_tables tab
where tab.table_name like 'MY%'
group by tab.table_name;

  COL1COL2

XMLforest can be used to create a simple list of XML elements ( a forest). We will extend the example to show some basic descriptive attribute of our example table:

select XMLElement("MyDatabase"
       , XMLElement("table", XMLAttributes(tab.table_name as "name")
                  , XMLforest( tab.initial_extent as "InitialExtent"
                      , tab.tablespace_name as "Tablespace"
             )
           , XMLAgg((select XMLAgg(XMLElement("ColumnName", col.column_name))
              from   all_tab_columns col
              where  col.table_name = tab.table_name
              and    col.owner      = tab.owner
                     )
             )
         )
       ).getCLobVal()
from all_tables tab
where tab.table_name like 'MY%'
group by tab.table_name,initial_extent,tablespace_name;

   1024000DAT_MEDIUMCOL1COL2

The previous simple examples show how easy it is to create XML directly from a query. This actual query could be hidden in a view. This way the creation of XML becomes completely transparent for the other developers who don’t have knowledge about the powerful XML capabilities of the Oracle database.