Generate XML from Oracle Database

Overview
Generate XML using SQL Functions
Generate XML using DBMS_XMLGEN
1. dbms-xmlgen Functions and Procedures
2. Examples
  1. Example One
References

Overview

Generate XML using SQL Functions

* XMLELEMENT and XMLATTRIBUTES
* XMLFOREST
* XMLCONCAT
* XMLAGG
* XMLPI
* XMLCOMMENT
* XMLSERIALIZE
* XMLPARSE

Generate XML using DBMS_XMLGEN

* DBMS_XMLGEN creates XML documents (as a CLOB or XMLType) from SQL query results
* It provides a fetch interface
– max rows to retrieve
– max rows to skip
* It provides options for changing tag names for ROW, ROWSET, etc.

dbms-xmlgen Functions and Procedures

* Context handle
– used by all functions

SUBTYPE ctxHandle IS NUMBER

* Obtain a context handle

-- Context handle from query string
newContext(
  queryString IN VARCHAR2)
 
-- Context handle from cursor variable
newContext(
  queryString IN SYS_REFCURSOR)
  RETURN ctxHandle;
 
-- ?
newContextFromHierarchy(
  queryString IN VARCHAR2)
  RETURN ctxHandle;

  -- Create context handle
  qryCtx := dbms_xmlgen.newContext(
    'select * from hr.employees');

* Sets the name of the element separating all the rows. The default name is ROW.

setRowTag(ctx IN ctxHandle,
          rowTag IN VARCHAR2);
 
dbms_xmlgen.setRowTag(qryCtx, 'EMPLOYEE');

* Sets the name of the document root element. The default name is ROWSET

setRowSetTag(ctx IN ctxHandle, 
             rowSetTag IN VARCHAR2);
 
  -- Set the rowset tab
  dbms_xmlgen.setRowSetTag(qryCtx, 'EMPLOYEES');

* Retrieves XML

-- Get xml doc from input context handle and append to input clob:
getXML(ctx IN ctxHandle, 
       clobval IN OUT NCOPY CLOB, 
       dtdOrSchema IN NUMBER:= NONE);
 
-- Get xml doc from input context handle and return as clob:
getXML(ctx IN ctxHandle, 
       dtdOrSchema IN NUMBER:= NONE) 
  RETURN CLOB;
 
-- Get xml doc from input context handle and return as XMLType:
getXMLType(
  ctx IN ctxHandle, 
  dtdOrSchema IN NUMBER:= NONE)
  RETURN XMLType;
 
-- Get xml from input query string and return as clob;
getXML(
  sqlQuery IN VARCHAR2, 
  dtdOrSchema IN NUMBER := NONE)
  RETURN CLOB;
 
-- Get xml from input query string and return as XMLType;
getXMLType(
  sqlQuery IN VARCHAR2, 
  dtdOrSchema IN NUMBER := NONE)   
  RETURN XMLType;

* Get number of rows processed

getNumRowsProcessed(ctx IN ctxHandle) 
  RETURN NUMBER;
 
-- e.g.
exit WHEN dbms_xmlgen.getnumrowsprocessed(qryCtx) = 0;

* Sets the maximum number of rows to fetch from the SQL query result for every invocation of the getXML call.
– It is an error to call this function on a context handle created by function newContextFromHierarchy.

setMaxRows(ctx IN ctxHandle, 
           maxRows IN NUMBER);
 
-- e.g.
dbms_xmlgen.setmaxrows(qryCtx, 2);

* Skips a given number of rows before generating the XML output for every call to getXML.
– It is an error to call this function on a context handle created by function newContextFromHierarchy.

setSkipRows(ctx IN ctxHandle, 
            skipRows IN NUMBER);

* Determines whether or not special characters in the XML data must be converted into their escaped XML equivalent.
– defaults to yes

setConvertSpecialChars(
  ctx IN ctxHandle, 
  conv IN BOOLEAN);

* Sets the name of the collection elements.
– The default name for collection elements it the type name itself.
– You can override that to use the name of the column with the _ITEM tag appended to it using this function.

useItemTagsForColl(ctx IN ctxHandle);

* Restarts the query and generate the XML from the first row again.

restartQuery(ctx IN ctxHandle);

* Close context handle

closeContext(ctx IN ctxHandle);

* Encodes or decodes the XML data string argument.
– Encoding refers to replacing entity references such as < to their escaped equivalent, such as <. - Decoding refers to the reverse conversion.

CONVERT(
  xmlData IN varchar2, 
  flag IN NUMBER := ENTITY_ENCODE)    
  RETURN VARCHAR2;
 
CONVERT(
  xmlData IN CLOB, 
  flag IN NUMBER := ENTITY_ENCODE) 
  RETURN CLOB;

* NULL handling
The setNullHandling flag values are:
DROP_NULLS CONSTANT NUMBER := 0;
This is the default setting and leaves out the tag for NULL elements.

NULL_ATTR CONSTANT NUMBER := 1;
This sets xsi:nil = “true”.

EMPTY_TAG CONSTANT NUMBER := 2;
This sets, for example, .

setNullHandling(ctx IN ctxHandle, 
                flag IN NUMBER);
 
-- shortcut for setNullHandling(ctx, NULL_ATTR)
useNullAttributeIndicator(
  ctx IN ctxHandle, 
  attrind IN BOOLEAN := TRUE);

* Sets bind value for the bind variable appearing in the query string associated with the context handle.
– The query string with bind variables cannot be executed until all of the bind variables are set values using setBindValue.

setBindValue(
  ctx IN ctxHandle, 
  bindVariableName IN VARCHAR2, 
  bindValue IN VARCHAR2);

* Clears all the bind values for all the bind variables appearing in the query string associated with the context handle. Afterwards, all of the bind variables must rebind new values using setBindValue.

clearBindValue(ctx IN ctxHandle);

Examples

Example One

* Create a temp table to hold XML result:

CREATE TABLE temp_clob_tab (RESULT CLOB);

* Use dbms_xmlgen:

DECLARE
  -- Define a context handle
  qryCtx dbms_xmlgen.ctxHandle;
  -- Result XML
  xml CLOB;
BEGIN
  -- Create context handle
  qryCtx := dbms_xmlgen.newContext(
    'select * from hr.employees');
 
  -- Set the row tab
  dbms_xmlgen.setRowTag(qryCtx, 'EMPLOYEE');
 
  dbms_xmlgen.setmaxrows(qryCtx, 2);
 
  loop
    -- Get result xml
    xml := dbms_xmlgen.getXML(qryCtx);
 
    exit WHEN dbms_xmlgen.getnumrowsprocessed(qryCtx) = 0;
 
    -- Insert result xml into temp table
    INSERT INTO temp_clob_tab VALUES(xml);
  END loop;
 
  -- Close context
  dbms_xmlgen.closeContext(qryCtx);
END;
/

References

* Generating XML Data from the Database

Generate XML from Oracle Database

Contents