SQLX How to easily generate XML

2

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)
---------------------------------------------
<thedate>07-OCT-05</thedate>

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()
--------------------------------------------------------------------------------
<tablename Owner="THE_OWNER">MYTABLE</tablename>

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;
<mydatabase>
  <table name="MYTABLE">
    <columnname>COL1</columnname>
    <columnname>COL2</columnname>
  </table>
</mydatabase>

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;
<mydatabase>
   <table name="MYTABLE">
    <initialextent>1024000</initialextent>
    <tablespace>DAT_MEDIUM</tablespace>
    <columnname>COL1</columnname>
    <columnname>COL2</columnname>
  </table>
</mydatabase>

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.

Share.

About Author

2 Comments

  1. The article was very useful. It just showed how powerful is the need for XML generation from the table and the ease with which oracle allows us to do. Thanks a ton.

  2. Andre,

    Oracle’s support of XML for the last few versions has been super. I like the inclusion of SQLX also. XQuery has been hailed as the greatest thing since white bread but I continue to use SQLX much more often, and think I will far into the future. I wrote a couple of articles on Oracle’s native XML support here: XML in the Database: A Brief Overview and Oracle and XML In Action – A Real World Example.

    Thanks,

    LewisC
    http://blogs.ittoolbox.com/oracle/guide/