Generating XML direct from the Database

Generating XML direct from the Database


There are several ways to generate XML directly from the database using SQL.

DBMS_XMLGEN
The first method demands minimal knowledge for the maximum output. It is limited in what it does and you are limited in
what you can can influence.

Firstly create a table to store your result.

CREATE TABLE temp_xml_table(result CLOB);

The procedure then follows the following structure.

DECLARE
   qryctx DBMS_XMLGEN.ctxhandle;
   RESULT CLOB;
   l_sql VARCHAR2 (1000)
         := 'SELECT s.ID, CURSOR (SELECT e_code
                                    FROM site_equipments eq
                                   WHERE eq.sit_id = s.ID) as eqip
               FROM sites s
              WHERE ROWNUM <  11';
BEGIN
   qryctx := DBMS_XMLGEN.newcontext (l_sql);
   -- set the row header to be SITE
   DBMS_XMLGEN.setrowtag (qryctx, 'SITE');
   -- now get the result
   RESULT := DBMS_XMLGEN.getxml (qryctx);
   INSERT INTO temp_xml_table VALUES (RESULT);
   --always close context or you'll blow up.
   DBMS_XMLGEN.closecontext (qryctx);
END;
/ 

You can view the resulting clob by double clicking in TOAD which starts the XML viewer.

When you are finished drop the table.

DROP TABLE temp_xml_table PURGE;

SYS_XMLAGG
The second method demands more knowledge and planning upfront (and patience).

This is a 'more' straightforward way of generating XML content but uses SQL natively and wraps the columns in functions and procedures to produce a more clean output.

Once again start by creating a table for output.

CREATE TABLE temp_xml_table(result CLOB);

The query looks like follows:

INSERT INTO temp_xml_table
SELECT SYS_XMLAGG (XMLELEMENT ("SiteID",xmlattributes (
       LPAD (s.b_borough_no, 2, '0')'/'LPAD (s.site_no, 6, '0') AS "SiteNumber" ),
       s.ID,(SELECT xmlagg(xmlelement("eqi", e_code))
               FROM site_equipments eq
              WHERE eq.sit_id = s.ID and eq.end_date > sysdate)as "Equip"),
       xmlformat ('Sites')
       ) AS lmx
  FROM sites s
 WHERE ROWNUM < 11;

Other methods are more complex, involving object types, object views and casting to multisets and are not mentioned here but are left out for the sake of sanity (mainly mine).

This is just starting to scratch the surface of generating XML, so if you need help or advice let me know.