Oracle DBMS_XMLQUERY
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Provides database-to-XMLType functionality. Whenever possible, Oracle recommends the use of DBMS_XMLGEN, a built-in package in C, instead of this package whenever possible.
AUTHID CURRENT_USER
Constants
Name Data Type Value
General
DEFAULT_ROWSETTAG VARCHAR2(6) 'ROWSET' /* rowsettag */
DEFAULT_ERRORTAG VARCHAR2(5) 'ERROR' /* error tag */
DEFAULT_ROWIDATTR VARCHAR2(3) 'NUM' /* Row ID attr */
DEFAULT_ROWTAG VARCHAR2(3) 'ROW' /* rowtag */
DEFAULT_DATE_FORMAT VARCHAR2(21) 'MM/dd/yyyy HH:mm:ss'
ALL_ROWS NUMBER -1 /* NO MAX, render all */
LOWER_CASE NUMBER 1 /* LOWER case */
NONE NUMBER 0 /* NO META */
DTD NUMBER 1 /* META = DTD */
SCHEMA NUMBER 2 /* META = SCHEMA */
UPPER_CASE NUMBER 2 /* UPPER case */
Encoding Signal
DB_ENCODING VARCHAR2(1) '_'
Data Types SUBTYPE ctxType IS NUMBER; /* context type */

SUBTYPE ctxHandle IS NUMBER;
Dependencies
ALL_OBJECTS DBMS_LOB DBMS_OUTPUT
Documented Yes
Exceptions
Error Code Reason
ORA-29532 Java call terminated by uncaught Java exception: oracle.xml.sql.OracleXMLSQLException: Invalid context handle specified.
First Available Not known but believed to be 9.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsxsu.sql
Subprograms
 
CLEARBINDVALUES
Undocumented dbms_xmlquery.clearBindValues(ctxHdl IN ctxType);
See Demo Below
 
CLOSECONTEXT
Closes or deallocates a particular query context dbms_xmlquery.closeContext(ctxHdl IN ctxType);
See Demo Below
 
GETDTD
Generates the DTD

Overload 1
dbms_xmlquery.getDTD(
ctxHdl  IN ctxType,
withVer IN BOOLEAN := FALSE)
RETURN CLOB;
TBD
Overload 2 dbms_xmlquery.getDTD(
ctxHdl IN ctxType,
xDoc   IN CLOB,
withVer IN BOOLEAN := FALSE);
TBD
 
GETEXCEPTIONCONTENT
Returns the thrown exception's error code and error message dbms_xmlquery.getExceptionContent(
ctxHdl IN  ctxType,
errNo  OUT NUMBER,
errMsg OUT VARCHAR2);
DECLARE
 h       dbms_xmlquery.ctxType;
 stmnt   CLOB := 'SELECT table_name FROM dba_tables WHERE rownum < 3';
 errNo   NUMBER;
 errMsg  VARCHAR2(60);
BEGIN
  h := dbms_xmlquery.newContext(stmnt);
  dbms_xmlquery.getExceptionContent(h, errNo, errMsg);
  errMsg := NVL(errMsg, 'Success');
  dbms_output.put_line(TO_CHAR(errNo));
  dbms_output.put_line(errMsg);
  dbms_xmlquery.closeContext(h);
END;
/
 
GETNUMROWSPROCESSED
Returns the number of rows processed for the query dbms_xmlquery.getNumRowsProcessed(ctxHdl IN ctxType) RETURN NUMBER;
DECLARE
 h dbms_xmlquery.ctxType;
 c CLOB;
 i PLS_INTEGER;
BEGIN
  h := dbms_xmlquery.newContext('SELECT table_name FROM dba_tables WHERE rownum < 4');
  dbms_xmlquery.propagateOriginalException(h, FALSE);
  c := dbms_xmlquery.getXML(h);
  i := dbms_xmlquery.getNumRowsProcessed(h);
  dbms_output.put_line('Rows Processed = ' || TO_CHAR(i));
  dbms_output.put_line(c);
  dbms_xmlquery.closeContext(h);
  dbms_output.put_line('Success');
EXCEPTION
  WHEN OTHERS THEN
    dbms_xmlquery.closeContext(h);
    dbms_output.put_line('Exception Raised');
END;
/
 
GETVERSION
Prints the version of the XSU in use dbms_xmlquery.getVersion;
set serveroutput on

exec dbms_xmlquery.getVersion;
 
GETXML
Generates the XML document
Overload 1
dbms_xmlquery.getXML(ctxHdl IN ctxType, metaType IN NUMBER := NONE) RETURN CLOB;
See Demo Below
Overload 2 dbms_xmlquery.getXML(ctxHdl IN ctxType, xDoc IN CLOB, metaType IN NUMBER := NONE);
See Demo Below
Overload 3 dbms_xmlquery.getXML(sqlQuery IN VARCHAR2, metaType IN NUMBER := NONE) RETURN CLOB;
See Demo Below
Overload 4 dbms_xmlquery.getXML(sqlQuery IN CLOB, metaType IN NUMBER := NONE) RETURN CLOB;
See Demo Below
 
NEWCONTEXT
Creates a query context and it returns the context handle

Overload 1
dbms_xmlquery.newContext(sqlQuery IN VARCHAR2) RETURN ctxType;
DECLARE
 h dbms_xmlquery.ctxType;
BEGIN
  h := dbms_xmlquery.newContext('SELECT table_name FROM dba_tables WHERE rownum < 11');
  dbms_output.put_line(TO_CHAR(h));
  dbms_xmlquery.closeContext(h);
END;
/

/
Overload 2 dbms_xmlquery.newContext(sqlQuery IN CLOB) RETURN ctxType;
DECLARE
 h     dbms_xmlquery.ctxType;
 stmnt CLOB := 'SELECT table_name FROM dba_tables WHERE rownum < 11';
BEGIN
  h := dbms_xmlquery.newContext(stmnt);
  dbms_output.put_line(TO_CHAR(h));
  dbms_xmlquery.closeContext(h);
END;
/

/
 
PROPAGATEORIGINALEXCEPTION
Tells the XSU that if an exception is raisedit should throw the  exception rather then, wrapping it with an OracleXMLSQLException dbms_xmlquery.propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE);
DECLARE
 h dbms_xmlquery.ctxType;
BEGIN
  h := dbms_xmlquery.newContext('SELECT table_name FROM dba_tables WHERE rownum < 11');
  dbms_xmlquery.propagateOriginalException(h, TRUE);
  dbms_xmlquery.closeContext(h);
END;
/
 
P_GETDTD
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_getDTD(ctxHdl IN ctxType, xDoc IN CLOB, withVer IN NUMBER);
TBD
 
P_GETXML
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_getXML(ctxHdl IN ctxType, xDoc IN CLOB, metaType IN NUMBER);
TBD
 
P_PROPORIGEXC
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_propOrigExc(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
P_SETDATAHEADER
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setDataHeader(ctxHdl IN ctxType, header IN CLOB,tag IN VARCHAR2);
TBD
 
P_SETENCODINGTAG
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setEncodingTag(ctxHdl IN ctxType, enc IN VARCHAR2);
TBD
 
P_SETMETAHEADER
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setMetaHeader(ctxHdl IN ctxType, header IN CLOB);
TBD
 
P_SETRAISEEXCEPTION
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setRaiseException(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
P_SETRAISENOROWSEXC
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setRaiseNoRowsExc(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
P_SETSQLTOXMLNAMEESC
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setSQLToXMLNameEsc(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
P_SETSTRICTLEGALXMLCHARCHECK
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setStrictLegalXMLCharCheck(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
P_SETSTYLESHEETHEADER
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setStylesheetHeader(ctxHdl IN ctxType, uri IN VARCHAR2, type IN VARCHAR2);
TBD
 
P_SETXSLT
ORA-600 [kgmexchi11] Bug Workaround
Overload 1
dbms_xmlquery.p_setXSLT(ctxHdl IN ctxType, uri IN VARCHAR2, ref IN VARCHAR2);
TBD
Overload 2 dbms_xmlquery.p_setXSLT(ctxHdl IN ctxType, stylesheet IN CLOB, ref IN VARCHAR2);
TBD
 
P_USENULLATTRIND
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_useNullAttrInd(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
P_USETYPEFORCOLLELEMTAG
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_useTypeForCollElemTag(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
REMOVEXSLTPARAM
Removes the named top-level stylesheet parameter dbms_xmlquery.removeXSLTParam(ctxHdl IN ctxType, name IN VARCHAR2);
TBD
 
RESETRESULTSET
Undocumented but appears to clear (reset) the result set dbms_xmlquery.resetResultSet(ctxHdl IN ctxType);
TBD
 
SETBINDVALUE
Sets a value for a particular bind name dbms_xmlquery.setBindValue(
ctxHdl    IN ctxType,
bindName  IN VARCHAR2,
bindValue IN VARCHAR2);
TBD
 
SETCOLLIDATTRNAME
Sets the name of the id attribute of the collection element's separator tag. Attribute is omitted if NULL is passed. dbms_xmlquery.setCollIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2);
TBD
 
SETDATAHEADER
Sets the XML data header dbms_xmlquery.setDataHeader(
ctxHdl IN ctxType,
header IN CLOB     := NULL,
tag    IN VARCHAR2 := NULL);
See Demo Below
 
SETDATEFORMAT
Sets the format of the generated dates for the XML document dbms_xmlquery.setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2);
See Demo Below
 
SETENCODINGTAG
Sets the encoding processing instruction in the XML document dbms_xmlquery.setEncodingTag(
ctxHdl IN ctxType,
enc    IN VARCHAR2 := DB_ENCODING);
TBD
 
SETERRORTAG
Sets the tag to be used to enclose the XML error documents dbms_xmlquery.setErrorTag(ctxHdl IN ctxType, tag IN VARCHAR2);
See Demo Below
 
SETMAXROWS
Sets the maximum number of rows to be converted to XML dbms_xmlquery.setMaxRows (ctxHdl IN ctxType, rows IN NUMBER);
See Demo Below
 
SETMETAHEADER
Sets the XML meta header dbms_xmlquery.setMetaHeader(ctxHdl IN ctxType, header IN CLOB := NULL);
TBD
 
SETRAISEEXCEPTION
Tells the XSU to throw the raised exceptions dbms_xmlquery.setRaiseException(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE);
TBD
 
SETRAISENOROWSEXCEPTION
Tells the XSU to throw or not to throw an exception if the XML document generated is empty dbms_xmlquery.setRaiseNoRowsException(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE);
TBD
 
SETROWIDATTRNAME
Sets the name of the id attribute of the row enclosing tag dbms_xmlquery.setRowIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2);
TBD
 
SETROWIDATTRVALUE
Specifies the scalar column whose value is to be assigned to the id attribute of the row enclosing tag dbms_xmlquery.setRowIdAttrValue(ctxHdl IN ctxType, colName IN VARCHAR2);
TBD
 
SETROWSETTAG
Sets the tag to be used to enclose the XML dataset dbms_xmlquery.setRowsetTag(ctxHdl IN ctxType, tag IN VARCHAR2);
See Demo Below
 
SETROWTAG
Sets the tag to be used to enclose the XML element dbms_xmlquery.setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2);
See Demo Below
 
SETSKIPROWS
Sets the number of rows to skip dbms_xmlquery.setSkipRows(ctxHdl IN ctxType, rows IN NUMBER);
TBD
 
SETSQLTOXMLNAMEESCAPING
Turns on or off escaping of XML tags in the case that the SQL object name, which is mapped to a XML identifier, is not a valid XML identifier dbms_xmlquery.setSQLToXMLNameEscaping(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE);
TBD
 
SETSTRICTLEGALXMLCHARCHECK
Undocumented dbms_xmlquery.setStrictLegalXMLCharCheck(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE);
TBD
 
SETSTYLESHEETHEADER
Sets the stylesheet header dbms_xmlquery.setStylesheetHeader(
ctxHdl IN ctxType,
uri    IN VARCHAR2,
type   IN VARCHAR2 := 'text/xsl');
TBD
 
SETTAGCASE
Specified the case of the generated XML tags dbms_xmlquery.setTagCase(ctxHdl IN ctxType, tCase IN NUMBER);
See Demo Below and not how the tag case is affected in theoutput
 
SETXSLT
Registers a stylesheet to be applied to generated XML

Overload 1
dbms_xmlquery.setXSLT(
ctxHdl IN ctxType,
uri    IN VARCHAR2,
ref    IN VARCHAR2 := NULL);
TBD
Overload 2 dbms_xmlquery.setXSLT(
ctxHdl     IN ctxType,
stylesheet IN CLOB,
ref        IN VARCHAR2 := NULL);
TBD
 
SETXSLTPARAM
Sets the value of a top-level stylesheet parameter dbms_xmlquery.setXSLTParam(
ctxHdl IN ctxType,
name   IN VARCHAR2,
value  IN VARCHAR2);
TBD
 
USENULLATTRIBUTEINDICATOR
Specifies weather to use an XML attribute to indicate NULLness dbms_xmlquery.useNullAttributeIndicator(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE);
TBD
 
USETYPEFORCOLLELEMTAG
Tells the XSU to use the collection element's type name as the collection element tag name dbms_xmlquery.useTypeForCollElemTag(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE);
TBD
 
Demo
This demo is formulated to use many of the individual packages's individual objects to demonstrate their correct syntax ... not to do anything useful

Note the use of setMaxRows to limit the number of rows selected in the demo to 2
DECLARE
 h dbms_xmlquery.ctxType;
 c CLOB;
 i PLS_INTEGER;
BEGIN
  h := dbms_xmlquery.newContext('SELECT object_name, created FROM dba_objects WHERE rownum < 42');
  dbms_xmlquery.setRowsetTag(h, 'OBJ_DATA');
  dbms_xmlquery.setRowTag(h, 'OBJ_NAME');
  dbms_xmlquery.setDataHeader(h, 'MLIB', 'OBJ_DATA');
  dbms_xmlquery.setDateFormat(h, dbms_xmlquery.default_date_format);
  dbms_xmlquery.setErrorTag(h, 'OOPS');
  dbms_xmlquery.setMaxRows (h, 2);
  dbms_xmlquery.setTagCase(h, dbms_xmlquery.lower_case);

  c := dbms_xmlquery.getXML(h);
  dbms_output.put_line(c);
  dbms_xmlquery.clearBindValues(h);
  dbms_xmlquery.closeContext(h);
  dbms_output.put_line('Success');
EXCEPTION
  WHEN OTHERS THEN
    dbms_xmlquery.clearBindValues(h);
    dbms_xmlquery.closeContext(h);
    dbms_output.put_line('Exception Raised');
END;
/

Related Topics
DBMS_XMLGEN
Packages
XML Functions

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-2014 Daniel A. Morgan All Rights Reserved