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.

Navigating XML in Oracle

A few code snippets for navigating and extracting dynamically from XML in PL/SQL.

--The executable section of the traverse_and_display procedure. 
BEGIN
  -- get all elements
  nodes := xmldom.getElementsByTagName (doc, '*');

  -- loop through elements
  FOR node_index IN 0 .. xmldom.getLength (nodes) - 1
  LOOP
     one_node := xmldom.item (nodes, node_index);

     display_element (one_node);

     node_map := xmldom.getAttributes (one_node);

     FOR attr_index IN
        0 .. xmldom.getLength (node_map) - 1
     LOOP
        display_attribute (node_map, attr_index);
     END LOOP;
  END LOOP;
END traverse_and_display;



-- The code required for displaying the name and value of an element. 
PROCEDURE display_element (node IN xmldom.DOMNode)
IS
  one_element   xmldom.DOMElement;
  value_node    xmldom.DOMNode;
BEGIN
  one_element := xmldom.makeElement (node);
  DBMS_OUTPUT.put_line ('Element: ' ||
                       xmldom.getTagName (one_element
                       )
  );
  value_node := xmldom.getFirstChild (node);
  DBMS_OUTPUT.put_line ('Value: ' ||
                       xmldom.getNodeValue (value_node
                       )
  );
END;



--Displaying the value of an attribute. 
PROCEDURE display_attribute (
  node_map     IN   xmldom.DOMNamedNodeMap,
  attr_index   IN   PLS_INTEGER
)
IS
  one_node   xmldom.DOMNode;
  attrname   VARCHAR2 (100);
  attrval    VARCHAR2 (100);
BEGIN
  one_node := xmldom.item (node_map, attr_index);
  attrname := xmldom.getNodeName (one_node);
  attrval := xmldom.getNodeValue (one_node);
  DBMS_OUTPUT.put_line ('   ' || 
        attrname || ' = ' || attrval
  );
END;