CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_XMLGEN
Version 11.1
 
General Information
Note: 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.
Source $ORACLE_HOME/rdbms/admin/dbmsxml.sql
First Availability 9.0.1
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
Defined 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_LOB SDO_OLS URITYPE WWV_FLOW_DATLOAD_XML
DBMS_XMLGEN_LIB URIFACTORY WRI$_ADV_OBJSPACE_TREND_T XMLTYPE
Security Model Execute is granted to PUBLIC

Is XDK For PL/SQL Installed
col comp_name format a45

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

-- should return
COMP_NAME                          STATUS    VERSION
----------------------             --------  ---------
Oracle Database Catalog Views      VALID     10.2.0.1.0
Oracle Database Packages and Types VALID     10.2.0.1.0
Oracle Workspace Manager           VALID     10.2.0.1.0
JServer JAVA Virtual Machine       VALID     10.2.0.1.0
Oracle XDK                         VALID     10.2.0.1.0
Oracle Database Java Packages      VALID     10.2.0.1.0
Oracle Expression Filter           VALID     10.2.0.1.0
Oracle interMedia                  VALID     10.2.0.1.0
Oracle Data Mining                 VALID     10.2.0.1.0
Oracle Text                        VALID     10.2.0.1.0
Oracle XML Database                VALID     10.2.0.1.0
OLAP Analytic Workspace            VALID     10.2.0.1.0
Oracle OLAP API                    VALID     10.2.0.1.0
OLAP Catalog                       VALID     10.2.0.1.0
Spatial                            VALID     10.2.0.1.0
Oracle Enterprise Manager          VALID     10.2.0.1.0
Oracle Ultra Search                NO SCRIPT 10.1.0.4.0

-- if not installed run
$ORACLE_HOME/rdbms/admin/initxml.sql
Demo Tables -- emp table from SCOTT schema
CREATE TABLE test (testcol VARCHAR2(4000));
 
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

Overload 1
dbms_xmlgen.convert(
xmlData IN VARCHAR2,
flag    IN NUMBER := ENTITY_ENCODE)
RETURN VARCHAR2;

-- Flags are the Conversion Type constants
TBD
Overload 2 dbms_xmlgen.convert(
xmlData IN CLOB,
flag    IN NUMBER := ENTITY_ENCODE)
RETURN CLOB;

-- Flags are the Conversion Type constants
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 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_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);
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 dbms_xmlgen.setNullHandling(ctx IN ctxHandle, flag IN NUMBER);

-- Flags are the NULL handling constants
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);
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);
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
XSLT Support

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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [220 users online]    © 2010 psoug.org