DBMS XMLGEN

DBMS XMLGEN is a PL/SQL package that allows programmers to extract XML data from Oracle database tables.

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 the GETXML functions to avoid any extra CLOB copies and to reuse the same CLOB for subsequent calls. Because of the CLOB reuse, the 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 PL/SQL example parses the fields in the employee table into XML and saves the XML as CLOB rows in a temporary table.

DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
BEGIN
  qryCtx :=  dbms_xmlgen.newContext ('SELECT * from emp');
  DBMS_XMLGEN.setMaxRows(qryCtx, 5);
  LOOP
    -- save the XML into the CLOB field
    result :=  DBMS_XMLGEN.getXML(qryCtx);
    EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;  

    -- store the XML to a temporary table
    INSERT INTO temp_clob_tab VALUES(result);
   END LOOP;
END;

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, 'EMPLOYEES');

sample output:

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

<?xml version="1.0"?>
<EMPLOYEES>
 <ROW>
  <EMPNO>7876</EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>23-MAY-1987 00:00:00</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 ...
</EMPLOYEES>

setRowTag()

This function sets the name of the element for each row. The default name is ROW (see example above). 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, 'EMPLOYEE');

sample output: Every row output is now enclosed inside the AUTHOR tag.

<?xml version="1.0"?>
<EMPLOYEES>
 <EMPLOYEE>
  <EMPNO>7876</EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>23-MAY-1987 00:00:00</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
 </EMPLOYEE>
 ...
</EMPLOYEES>

More examples

Using DBMS_XMLGEN from a normal SQL statement:

SQL> SELECT DBMS_XMLGEN.getXML('SELECT * FROM emp') FROM dual;
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17-DEC-1980 00:00:00</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
...
</ROWSET>

Using DBMS_XMLGEN from PL/SQL:

DECLARE
  ctx DBMS_XMLGEN.ctxHandle;
  xml CLOB;
BEGIN
  ctx := dbms_xmlgen.newcontext('select * from emp');
  dbms_xmlgen.setrowtag(ctx, 'MY-ROW-START-HERE');
  xml := dbms_xmlgen.getxml(ctx);
  dbms_output.put_line(substr(xml,1,255));
END;
/