Generating XML from SQL and PL/SQL – Part 2

In this second part I’ll show you how you can generate XML from table data in PL/SQL.

In part one of “Generating XML from SQL and PL/SQL“, I explained how to generate XML from SQL. In this second part I’ll show you how you can generate XML from table data in PL/SQL.

The datatype to hold XML in PL/SQL or in the database is XMLTYPE. So you can use the generated XML in PL/SQL, store it in table (XMLTYPE column), transform it to a CLOB (using the XMLTYPE getClobVal member function which I use in the examples) and write it to a file.

XMLTYPE

The easiest way to create an XML document, is using the constructor of XMLTYPE. This constructor can have several datatypes as input, like a CLOB and VARCHAR2, but as we’re going to base our XML on table data, we’re using a REF CURSOR.

You can create a ref cursor and pass on this ref cursor to the XMLTYPE constructor like this:

As you can see it creates a simple XML document where each row is seperated with a ROW tag.

DBMS_XMLGEN

The DBMS_XMLGEN built-in is similar to the XMLTYPE constructor, but accepts a query directly:

But it also provides procedures to change the ROWSET and ROW tags.

dbms_xmldom

With the XMLTYPE constructor and DBMS_XMLGEN package, you can create simple XML documents, fast and easy. When you need to create more advanced XML documents or want to have more control on how your XML document looks like, DBMS_XMLDOM can be used. The DBMS_XMLDOM package is a bit more complicated as you’ll have to create the entire document by calling functions and procedures of the package.

The following example creates an XML document with the department information retrieved from the query. In short, this is how it works: create new elements and add them as a (child) node.

In this second example I added the employees to each department. I created a new cursor loop on employees inside the department cursor loop, so that the employees of that department are added in a child node of the department. I also changed some code: I cast(dbms_xmldom.makeNode) the elements directly to a node, this way I need less variables:

This is how the final example looks, with the employees working for the department added in the XML document:

As you can see DBMS_XMLDOM is the most advanced way to create XML documents from table data, but it can also be used to create a document from different sources (query, CLOB, …), to search in XML documents, and to change documents (e.g. add nodes to an existing document).

More info: