Oracle DBMS_XMLGEN
Version 11.2.0.3

General Information
Purpose Converts the results of a SQL query to a canonical XML format. The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB. This package is similar to the DBMS_XMLQUERY package, except that it is written in C and compiled into the kernel. This package can only be run on the database.
AUTHID CURRENT_USER
Constants
Name Data Type Value
Conversion or Schema Specs
DTD NUMBER 1
NONE NUMBER 0
SCHEMA NUMBER 2
Conversion Type
ENTITY_DECODE conversionType 1
ENTITY_ENCODE conversionType 0
Null Handling
DROP_NULLS NUMBER 0
EMPTY_TAG NUMBER 2
NULL_ATTR NUMBER 1
Data Types SUBTYPE ctxHandle IS NUMBER;
SUBTYPE ctxType IS NUMBER;
SUBTYPE conversionType IS NUMBER;

TYPE PARAM_HASH IS TABLE OF VARCHAR2(100) 
INDEX BY VARCHAR2(32);
/
Dependencies
DBMS_CUBE SDO_OLS WRI$_ADV_OBJSPACE_TREND_T
DBMS_LOB URIFACTORY WWV_FLOW_DATALOAD_XML
DBMS_XMLGEN_LIB URITYPE XMLTYPE
Determine if XDB is installed and valid col comp_name format a45

SELECT comp_name, status, substr(version,1,10) as version
FROM dba_registry;

COMP_NAME                                     STATUS      VERSION
--------------------------------------------- ----------- ----------
OWB                                           VALID       11.2.0.3.0
Oracle Application Express                    VALID       3.2.1.00.1
Oracle Enterprise Manager                     VALID       11.2.0.3.0
OLAP Catalog                                  VALID       11.2.0.3.0
Spatial                                       VALID       11.2.0.3.0
Oracle Multimedia                             VALID       11.2.0.3.0
Oracle XML Database                           VALID       11.2.0.3.0
Oracle Text                                   VALID       11.2.0.3.0
Oracle Expression Filter                      VALID       11.2.0.3.0
Oracle Rules Manager                          VALID       11.2.0.3.0
Oracle Workspace Manager                      VALID       11.2.0.3.0
Oracle Database Catalog Views                 VALID       11.2.0.3.0
Oracle Database Packages and Types            VALID       11.2.0.3.0
JServer JAVA Virtual Machine                  VALID       11.2.0.3.0
Oracle XDK                                    VALID       11.2.0.3.0
Oracle Database Java Packages                 VALID       11.2.0.3.0
OLAP Analytic Workspace                       VALID       11.2.0.3.0
Oracle OLAP API                               VALID       11.2.0.3.0
Oracle Real Application Clusters              INVALID     11.2.0.3.0

-- if not installed run
$ORACLE_HOME/rdbms/admin/initxml.sql
Demo Table for this page -- emp table from SCOTT schema
CREATE TABLE test (testcol VARCHAR2(4000));
First Available 9.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source $ORACLE_HOME/rdbms/admin/dbmsxml.sql
Subprograms
clearBindValues restartQuery setPrettyPrinting
closeContext removeXSLTParam setRowSetTag
Convert setBindValue setRowTag
getNumRowsProcessed setCheckInvalidChars setSkipRows
getXML setConvertSpecialChars setXSLT
getXMLType setIndentationWidth setXSLTParam
newContext setMaxRows useItemTabsForColl
newContextFromHierarchy setNullHandling useNullAttributeIndicator
 
clearBindValues
Undocumented dbms_xmlgen.clearBindValues(ctx IN ctxHandle);
TBD
 
closeContext
Closes a given context and releases all resources associated with it, including the SQL cursor and bind and define buffers dbms_xmlgen.closeContext(ctx IN ctxHandle);
conn scott/tiger

set serveroutput on

DECLARE
 ctx    dbms_xmlgen.ctxHandle;
 emp_no NUMBER := 7369;
BEGIN
  ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
  dbms_xmlgen.closeContext(ctx);
  dbms_output.put_line(ctx);
END;
/
 
Convert
Converts the XML into the escaped or unescaped XML equivalent. Flag is a Conversion Type constant.

Overload 1		 dbms_xmlgen.convert(xmlData IN VARCHAR2, flag IN NUMBER := ENTITY_ENCODE) RETURN VARCHAR2;
TBD
Overload 2 dbms_xmlgen.convert(xmlData IN CLOB, flag IN NUMBER := ENTITY_ENCODE) RETURN CLOB;
TBD
 
getNumRowsProcessed
Returns the number of SQL rows that were processed in the last call to getXML dbms_xmlgen.getNumRowsProcessed(ctx IN ctxHandle) RETURN NUMBER;
conn scott/tiger

set serveroutput on

DECLARE
 ctx    dbms_xmlgen.ctxHandle;
 xml    CLOB;
 emp_no NUMBER := 7369;
 i      PLS_INTEGER;
BEGIN
  ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);

  xml := dbms_xmlgen.getXML(ctx);
  i := dbms_xmlgen.getNumRowsProcessed(ctx);
  dbms_output.put_line(TO_CHAR(i));
END;
/
 
getXML
Gets the XML document

Overload 1		 dbms_xmlgen.getXML(
ctx         IN     ctxHandle, 
tmpclob     IN OUT NCOPY CLOB,
dtdOrSchema IN     NUMBER := NONE);
TBD
Overload 2 dbms_xmlgen.getXML(
ctx         IN ctxHandle, 
dtdOrSchema IN NUMBER := NONE)
RETURN CLOB;
TBD
Overload 3 dbms_xmlgen.getXML(
sqlQuery    IN VARCHAR2,
dtdOrSchema IN NUMBER := NONE)
RETURN CLOB;
conn scott/tiger

set serveroutput on

DECLARE
 ctx    dbms_xmlgen.ctxHandle;
 xml    CLOB;
 emp_no NUMBER := 7369;
 xmlc   VARCHAR2(4000); -- required to convert LOB to VARCHAR2
 off    PLS_INTEGER := 1;
 len    PLS_INTEGER := 4000;
BEGIN
  ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = ' || emp_no);
  dbms_output.put_line(ctx);

  xml := dbms_xmlgen.getXML(ctx);
  dbms_output.put_line(xml);

  dbms_xmlgen.closeContext(ctx);

  dbms_lob.read(xml, len, off, xmlc); -- display first part
  dbms_output.put_line(xmlc);
END;
/
 
getXMLType
Gets the XML document and returns it as XMLType

Overload 1		 dbms_xmlgen.getXMLType(
ctx         IN     ctxHandle,
tmpxmltype  IN OUT NOCOPY xmltype, 
dtdOrSchema IN     NUMBER := NONE);
See newContextFromHierarchy Demo
Overload 2 dbms_xmlgen.getXMLType(
ctx         IN ctxHandle,
dtdOrSchema IN number := NONE)
RETURN sys.XMLType;
TBD
Overload 3 dbms_xmlgen.getXMLType(
sqlQuery    IN VARCHAR2,
dtdOrSchema IN NUMBER := NONE)
RETURN sys.XMLType;
TBD
 
newContext
Creates a new context handle

Overload 1		 dbms_xmlgen.newContext(query VARCHAR2) RETURN ctxHandle;
conn uwclass/uwclass

set serveroutput on

DECLARE
 ctx    dbms_xmlgen.ctxHandle;
 sid_no NUMBER := 7369;
BEGIN
  ctx := dbms_xmlgen.newContext('SELECT * FROM servers WHERE srvr_id LIKE %' || id_no || '%');
   dbms_output.put_line(ctx);
   dbms_xmlgen.closeContext(ctx);
END;
/
Overload 2 dbms_xmlgen.newContext(queryString SYS_REFCURSOR) RETURN ctxHandle;
TBD
 
newContextFromHierarchy
Undocumented. Used as part of new XML document creation dbms_xmlgen.newContextFromHierarchy(queryString IN VARCHAR2) RETURN ctxHandle;
conn scott/tiger

desc emp

set serveroutput on

DECLARE
 qryctx dbmx_xmlgen.ctxhandle;
 result XMLTYPE;

 PROCEDURE lob_output (p_clob CLOB) IS l_clob CLOB;
  l_clob_length  NUMBER;
  l_iterations   NUMBER;
  l_chunk        VARCHAR2(32767);
  l_chunk_length NUMBER := 32767;
 BEGIN
   l_clob := p_clob;
   l_clob_length := dbms_lob.getlength(l_clob);
   l_iterations := CEIL(l_clob_length / l_chunk_length);

   FOR i IN 0 .. l_iterations - 1 LOOP
     l_chunk := dbms_lob.substr(l_clob,l_chunk_length,i*l_chunk_length+1);
     dbms_output.put_line(l_chunk);
   END LOOP;
 END;
BEGIN
  qryctx := dbms_xmlgen.newcontextFromHierarchy('SELECT level,
  XMLElement("Position", XMLElement("Name", ename), XMLElement("Title",
  job)) FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr is NULL');

  result := dbms_xmlgen.getxmltype(qryctx);
  dbms_xmlgen.closeContext(qryctx);

  lob_output(RESULT.getClobVal());
END;
/
 
restartQuery
Restarts the query to start fetching from the beginning dbms_xmlgen.restartQUERY(ctx IN ctxHandle);
TBD
 
removeXSLTParam
Undocumented XSLT Support dbms_xmlgen.removeXSLTParam(ctx IN ctxType, name IN VARCHAR2);
TBD
 
setBindValue
Undocumented dbms_xmlgen.setBindValue(
ctx       IN ctxHandle,
bindName  IN VARCHAR2, 
bindValue IN VARCHAR2);
TBD
 
setCheckInvalidChars
Sets whether checking for invalid characters such as the NULL character dbms_xmlgen.setCheckInvalidChars(ctx IN ctxHandle, chk IN BOOLEAN);
conn scott/tiger

set serveroutput on

DECLARE
 ctx    dbms_xmlgen.ctxHandle;
 emp_no NUMBER := 7369;
BEGIN
  ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
  dbms_xmlgen.setCheckInvalidChars(ctx, TRUE);
  dbms_xmlgen.closeContext(ctx);
  dbms_output.put_line(ctx);
END;
/
 
setConvertSpecialChars
Sets whether special characters such as $, which are non-XML characters, should be converted or not to their escaped representation dbms_xmlgen.setConvertSpecialChars(ctx  IN ctxHandle, conv IN BOOLEAN);
TBD
 
setIndentationWidth
Undocumented dbms_xmlgen.setIndentationWidth(ctx IN ctxHandle, width IN NUMBER);
conn scott/tiger

set serveroutput on

DECLARE
  ctx    dbms_xmlgen.ctxHandle;
  xml    CLOB;
  emp_no NUMBER := 7369;
  xmlc   VARCHAR2(4000); -- required to convert LOB to VARCHAR2
  off    PLS_INTEGER := 1;
  len    PLS_INTEGER := 4000;
BEGIN
  ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno =
  '|| emp_no);

  dbms_xmlgen.setIndentationWidth(ctx, 10);

  dbms_output.put_line(ctx);

  xml := dbms_xmlgen.getXML(ctx);
  dbms_output.put_line(xml);

  dbms_xmlgen.closeContext(ctx);

  dbms_lob.read(xml, len, off, xmlc); -- display first part
  dbms_output.put_line(xmlc);
END;
/
 
SetMaxRows
Sets the maximum number of rows to be fetched each time dbms_xmlgen.setMaxRows(ctx IN ctxHandle, maxRows IN NUMBER);
conn scott/tiger

set serveroutput on

DECLARE
 ctx  dbms_xmlgen.ctxHandle;
 xml  CLOB;
 xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
 off  PLS_INTEGER := 1;
 len  PLS_INTEGER := 4000;
 i    PLS_INTEGER;
BEGIN
  ctx := dbms_xmlgen.newContext('SELECT * FROM emp');
  dbms_xmlgen.setMaxRows(ctx, 14);
  xml := dbms_xmlgen.getXML(ctx);
  i := dbms_xmlgen.getNumRowsProcessed(ctx);
  dbms_output.put_line(TO_CHAR(i));
  dbms_xmlgen.closeContext(ctx);

  dbms_lob.read(xml, len, off, xmlc); -- display first part
  INSERT INTO test (testcol) VALUES (xmlc);
  COMMIT;
END;
/
 
setNullHandling
Sets NULL handling options
Flag is a NULL handling constant		 dbms_xmlgen.setNullHandling(ctx IN ctxHandle, flag IN NUMBER);
TBD
 
setPrettyPrinting
Undocumented dbms_xmlgen.setPrettyPrinting(ctx IN ctxHandle, pp IN BOOLEAN);
TBD
 
setRowSetTag
Sets the name of the element enclosing the entire result dbms_xmlgen.setRowSetTag(ctx IN ctxHandle, rowSetTagName IN VARCHAR2);
conn scott/tiger

set serveroutput on

DECLARE
 ctx    dbms_xmlgen.ctxHandle;
 xml    CLOB;
 emp_no NUMBER := 7369;
 xmlc   VARCHAR2(4000); -- required to convert LOB to VARCHAR2
 off    PLS_INTEGER := 1;
 len    PLS_INTEGER := 4000;
BEGIN
  ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
  dbms_xmlgen.setRowSetTag(ctx, 'SRST');
  xml := dbms_xmlgen.getXML(ctx);
  dbms_output.put_line(xml);

  dbms_xmlgen.closeContext(ctx);

  dbms_lob.read(xml, len, off, xmlc); -- display first part
  dbms_output.put_line(xmlc);
END;
/
 
setRowTag
Sets the name of the element enclosing each row of the result dbms_xmlgen.setRowTag(ctx IN ctxHandle, rowTagName IN VARCHAR2);
conn scott/tiger

set serveroutput on

DECLARE

ctx    dbms_xmlgen.ctxHandle;
xml    CLOB;
emp_no NUMBER := 7369;

xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off  PLS_INTEGER := 1;
len  PLS_INTEGER := 4000;

BEGIN
  ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
  dbms_xmlgen.setRowTag(ctx, 'SRT');
  xml := dbms_xmlgen.getXML(ctx);
  dbms_output.put_line(xml);

  dbms_xmlgen.closeContext(ctx);

  dbms_lob.read(xml, len, off, xmlc); -- display first part
  dbms_output.put_line(xmlc);
END;
/
 
setSkipRows
Sets the number of rows to skip every time before generating the XML dbms_xmlgen.setSkipRows(ctx IN ctxHandle, skipRows IN NUMBER);
TBD
 
setXSLT
Undocumented
Overload 1		 dbms_xmlgen.setXSLT(ctx IN ctxType, stylesheet IN CLOB);
TBD
Overload 2 dbms_xmlgen.setXSLT(ctx IN ctxType, stylesheet IN XMLType);
TBD
Overload 3 dbms_xmlgen.setXSLT(ctx IN ctxType, uri IN VARCHAR2);
TBD
 
setXSLTParam
Undocumented dbms_xmlgen.setXSLT(ctx IN ctxType, name IN VARCHAR2, value IN VARCHAR2);
TBD
 
useItemTabsForColl
Forces the use of the collection column name appended with the tag _ITEM for collection elements dbms_xmlgen.useItemTagsForColl(ctx IN ctxHandle);
TBD
 
useNullAttributeIndicator
Specified whether to use an XML attribute to indicate NULLness, or to do it by omitting the inclusion of the particular entity in the XML document dbms_xmlgen.useNullAttributeIndicator(
ctx     IN ctxHandle,
attrind IN BOOLEAN := TRUE);
TBD

Related Topics
DBMS_LOB
DBMS_XMLSTORE

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved