Oracle Programming/XML Cheatsheet

Oracle possesses a variety of powerful XML features. A tremendous amount of documentation exists regarding Oracle's XML features. This resource is intended to be a cheat sheet for those of us who don't have time to wade through the hundreds of pages of documentation, but instead wish to quickly understand how to create simple XML output and input XML into a database.

Gets the XML document by fetching the maximum number of rows specified. It appends the XML document to the CLOB passed in. Use this version of GETXML Functions to avoid any extra CLOB copies and to reuse the same CLOB for subsequent calls. Because of the CLOB reuse, this GETXML Functions call is potentially more efficient.

Sets the name of the root element of the document. The default name is ROWSET. Setting the rowSetTag to NULL will stop this element from being output. An error is produced if both the row and the rowset are NULL and there is more than one column or row in the output. The error is produced because the generated XML would not have a top-level enclosing tag.

Syntax:

DBMS_XMLGEN.setRowSetTag (
ctx IN ctxHandle,
rowSetTag IN VARCHAR2);

Example:

DBMS_XMLGEN.setRowSetTag ( ctxHandle, 'ALL ROWS' );

Sample output:

This encloses the entire XML result set in the tag specified by the second parameter.

This function sets the name of the element each row. The default name is ROW. Setting this to NULL suppresses the ROW element itself. This produces an error if both the row and the rowset are NULL and there is more than one column or row in the output. The error is returned because the generated XML must have a top-level enclosing tag.

Syntax:

DBMS_XMLGEN.setRowTag (
ctx IN ctxHandle,
rowTag IN VARCHAR2);

Example:

This tells the XML generator to enclose the columns of each row in an AUTHOR tag.