Oracle Database/XML Management

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.

DBMS_XMLGEN

Functions

getXML()

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.

Syntax:

DBMS_XMLGEN.GETXML (
  ctx          IN ctxHandle, 
  tmpclob      IN OUT NCOPY CLOB,
  dtdOrSchema  IN number := NONE)
RETURN BOOLEAN;

Generates the XML document and returns it as a temporary CLOB. The temporary CLOB obtained from this function must be freed using the DBMS_LOB.FREETEMPORARY call:

DBMS_XMLGEN.GETXML (
  ctx          IN ctxHandle,
  dtdOrSchema  IN number := NONE)
RETURN CLOB;

Converts the results from the SQL query string to XML format, and returns the XML as a temporary CLOB, which must be subsequently freed using the DBMS_LOB.FREETEMPORARY call:

DBMS_XMLGEN.GETXML (
  sqlQuery     IN VARCHAR2,
  dtdOrSchema  IN number := NONE)
RETURN CLOB;

Example:

The following procedure parses the fields in the employee table into XML and saves the XML as CLOB rows in a table.

CREATE OR REPLACE procedure dump_pcd AS
    qryCtx DBMS_XMLGEN.ctxHandle;
    result CLOB;
BEGIN
    qryCtx :=  dbms_xmlgen.newContext ('SELECT * from employees;');
    DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE'); DBMS_XMLGEN.setMaxRows(qryCtx, 5);
    LOOP

        -- save the XML into the CLOB result.
        result :=  DBMS_XMLGEN.getXML(qryCtx);
        EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed((qryCtx)=0);
 
        -- store the data to a temporary table
        INSERT INTO temp_clob_tab VALUES(result);

    END LOOP;
END dump_pcd;

setRowSetTag()

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.

<ALL ROWS>
  <ROW>
     <NAME>John Doe</NAME>
  </ROW>
  <ROW>
     <NAME>Jane Doe</NAME>
  </ROW>
  ...
</ALL ROWS>

setRowTag()

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.

DBMS_XMLGEN.setRowTag ( ctxHandle, 'AUTHOR' );

Sample output:

Every row output is now enclosed inside the AUTHOR tag.

<ROWSET>
  <AUTHOR>
     <NAME>John Doe</NAME>
  </AUTHOR>
  <AUTHOR>
     <NAME>Jane Doe</NAME>
  </AUTHOR>
  ...
</ROWSET>

Examples

Dumping a Query Result as XML

Sample procedure for dumping the results of an SQL query as XML.

CREATE OR REPLACE procedure dump_pcd AS
    qryCtx DBMS_XMLGEN.ctxHandle;
    result CLOB;
BEGIN
    qryCtx :=  dbms_xmlgen.newContext ('SELECT * from employees;');
    DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE'); DBMS_XMLGEN.setMaxRows(qryCtx, 5);
    LOOP
        result :=  DBMS_XMLGEN.getXML(qryCtx);
        EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed((qryCtx)=0);
        INSERT INTO temp_clob_tab VALUES(result);
    END LOOP;
END dump_pcd;

The returned XML results will look similar to the following:

<?xml version=''1.0''?>
 <ROWSET>
  <EMPLOYEE>
    <EMPLOYEE_ID>30</EMPLOYEE_ID>
    <LAST_NAME>SCOTT</LAST_NAME>
    <SALARY>20000<SALARY>
  </EMPLOYEE>
  <EMPLOYEE>
    <EMPLOYEE_ID>31</EMPLOYEE_ID>
    <LAST_NAME>MARY</LAST_NAME>
    <AGE>25</AGE>
  </EMPLOYEE>
</ROWSET>