Good Article:

http://allthingsoracle.com/generating-xml-from-sql-and-pl-sql-part-1/

http://allthingsoracle.com/generating-xml-from-sql-and-plsql-part-2/


Generating XML

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.

DBMS_XMLGEN

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

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.