116
DBMS_XMLQUERY

DBMS_XMLQUERY provides database-to-XMLType functionality. Whenever possible, use DBMS_XMLGEN, a built-in package in C, instead of DBMS_XMLQUERY.

This chapter contains the following topics:

• Using DBMS_XMLQUERY
  • Constants
  • Types
• Summary of DBMS_XMLQUERY Subprograms

Using DBMS_XMLQUERY

• Constants

Constants

Table 116-1  Constants of DBMS_XMLQUERY

DB_ENCODING

DEFAULT_ROWSETTAG 

DEFAULT_ERRORTAG 

DEFAULT_ROWIDATTR

DEFAULT_ROWTAG

DEFAULT_DATE_FORMAT

ALL_ROWS

NONE

DTD

SCHEMA

LOWER_CASE

UPPER_CASE

Types

Table 116-2  Types of DBMS_XMLQUERY

ctxType

Summary of DBMS_XMLQUERY Subprograms

Table 116-3  DBMS_XMLQUERY Package Subprograms

CLOSECONTEXT

Closes or deallocates a particular query context

Syntax

PROCEDURE closeContext(
   ctxHdl IN ctxType);

ctxHdl

(IN)

GETDTD

Generates and returns the DTD based on the SQL query used to initialize the context. The options are described in the following table.

FUNCTION  getDTD(
   ctxHdl IN ctxType, 
   withVer IN BOOLEAN := false)   
RETURN CLOB;

PROCEDURE getDTD(
   ctxHdl IN ctxType, 
   xDoc IN CLOB, 
   withVer IN BOOLEAN := 
false);

ctxHdl

(IN)

withVer

(IN)

xDoc

(IN)

GETEXCEPTIONCONTENT

Returns the thrown exception's SQL error code and error message through the procedure's OUT parameters. This procedure is a work around the JVM functionality that obscures the original exception by its own exception, rendering PL/SQL unable to access the original exception content.

Syntax

PROCEDURE getExceptionContent(
   ctxHdl IN ctxType,
   errNo OUT NUMBER,
   errMsg OUT VARCHAR2);

ctxHdl

(IN)

errNo

(OUT)

errMsg

(OUT)

GETNUMROWSPROCESSED

Return the number of rows processed for the query.

Syntax

FUNCTION getNumRowsProcessed(
   ctxHdl IN ctxType)
RETURN NUMBER;

ctxHdl

(IN)

GETVERSION

Prints the version of the XSU in use.

Syntax

PROCEDURE getVersion();

GETXML

Creates the new context, executes the query, gets the XML back and closes the context. This is a convenience function. The context doesn't have to be explicitly opened or closed. The options are described in the following table.

FUNCTION  getXML(
   sqlQuery IN VARCHAR2, 
   metaType IN NUMBER := NONE)
RETURN CLOB;

FUNCTION getXML(
   sqlQuery IN CLOB,
   metaType IN NUMBER := NONE)
RETURN CLOB;

FUNCTION  getXML( 
   ctxHdl IN ctxType, 
   metaType IN NUMBER := NONE)
RETURN CLOB;

PROCEDURE getXML( 
   ctxHdl IN ctxType, 
   xDoc IN CLOB, 
   metaType IN NUMBER := NONE);

ctxHdl

(IN)

metaType

(IN)

sqlQuery

(IN)

xDoc

(IN)

NEWCONTEXT

Creates a query context and it returns the context handle. The options are described in the following table.

FUNCTION newContext(
   sqlQuery IN VARCHAR2) 
RETURN ctxType;

FUNCTION newContext(
   sqlQuery IN CLOB)
RETURN ctxType;

sqlQuery

(IN)

PROPAGATEORIGINALEXCEPTION

Specifies whether to throw every original exception raised or to wrap it in an OracleXMLSQLException.

Syntax

PROCEDURE propagateOriginalException(
   ctxHdl IN ctxType,
   flag IN BOOLEAN);

ctxHdl

(IN)

flag

(IN)

REMOVEXSLTPARAM

Removes the value of a top-level stylesheet parameter. If no stylesheet is registered, this method is not operational.

Syntax

PROCEDURE removeXSLTParam(
   ctxHdl IN ctxType,
   name IN VARCHAR2);

ctxHdl

(IN)

name

(IN)

SETBINDVALUE

Sets a value for a particular bind name.

Syntax

PROCEDURE setBindValue(
   ctxHdl IN ctxType,
   bindName IN VARCHAR2,
   bindValue IN VARCHAR2);

ctxHdl

(IN)

bindName

(IN)

bindValue

(IN)

SETCOLLIDATTRNAME

Sets the name of the id attribute of the collection element's separator tag. Passing NULL or an empty string for the tag causes the row id attribute to be omitted.

Syntax

PROCEDURE setCollIdAttrName(
   ctxHdl IN ctxType,
   attrName IN VARCHAR2);

ctxHdl

(IN)

attrName

(IN)

SETDATAHEADER

Sets the XML data header. The data header is an XML entity that is appended at the beginning of the query-generated XML entity, the rowset. The two entities are enclosed by the docTag argument. The last data header specified is used. Passing in NULL for the header parameter unsets the data header.

Syntax

PROCEDURE setDataHeader(
   ctxHdl IN ctxType,
   header IN CLOB := null,
   tag IN VARCHAR2 := null);

ctxHdl

(IN)

header

(IN)

tag

(IN)

SETDATEFORMAT

Sets the format of the generated dates in the XML document. The syntax of the date format pattern, the date mask, should conform to the requirements of the java.text.SimpleDateFormat class. Setting the mask to NULL or an empty string sets the default mask -- DEFAULT_DATE_FORMAT.

Syntax

PROCEDURE setDateFormat(
   ctxHdl IN ctxType,
   mask IN VARCHAR2);

ctxHdl

(IN)

mask

(IN)

SETENCODINGTAG

Sets the encoding processing instruction in the XML document.

Syntax

PROCEDURE setEncodingTag(
   ctxHdl IN ctxType,
   enc IN VARCHAR2 := DB_ENCODING);

ctxHdl

(IN)

enc

(IN)

SETERRORTAG

Sets the tag to be used to enclose the XML error documents.

Syntax

PROCEDURE setErrorTag(
   ctxHdl IN ctxType,
   tag IN VARCHAR2);

ctxHdl

(IN)

tag

(IN)

SETMAXROWS

Sets the maximum number of rows to be converted to XML. By default, there is no set maximum.

Syntax

PROCEDURE setMaxRows (
   ctxHdl IN ctxType,
   rows IN NUMBER);

ctxHdl

(IN)

rows

(IN)

SETMETAHEADER

Sets the XML meta header. When set, the header is inserted at the beginning of the metadata part (DTD or XMLSchema) of each XML document generated by this object. The last meta header specified is used. Passing in NULL for the header parameter unsets the meta header.

Syntax

PROCEDURE setMetaHeader(
   ctxHdl IN ctxType, 
   header IN CLOB := null);

ctxHdl

(IN)

Header

(IN)

SETRAISEEXCEPTION

Specifies whether to throw raised exceptions. If this call isn't made or if FALSE is passed to the flag argument, the XSU catches the SQL exceptions and generates an XML document from the exception message.

Syntax

PROCEDURE setRaiseException(
   ctxHdl IN ctxType,
   flag IN BOOLEAN:=true);

ctxHdl

(IN)

flag

(IN)

SETRAISENOROWSEXCEPTION

Specifies whether to throw an OracleXMLNoRowsException when the generated XML document is empty. By default, the exception is not thrown.

Syntax

PROCEDURE setRaiseNoRowsException(
   ctxHdl IN ctxType,
   flag IN BOOLEAN:=false);

ctxHdl

(IN)

flag

(IN)

SETROWIDATTRNAME

Sets the name of the id attribute of the row enclosing tag. Passing NULL or an empty string for the tag causes the row id attribute to be omitted.

Syntax

PROCEDURE setRowIdAttrName(
   ctxHdl IN ctxType,
   attrName IN VARCHAR2);

ctxHdl

(IN)

attrName

(IN)

SETROWIDATTRVALUE

Specifies the scalar column whose value is to be assigned to the id attribute of the row enclosing tag. Passing NULL or an empty string for the colName assigns the row count value (0, 1, 2 and so on) to the row id attribute.

Syntax

PROCEDURE setRowIdAttrValue(
   ctxHdl IN ctxType,
   colName IN VARCHAR2);

ctxHdl

(IN)

colName

(IN)

SETROWSETTAG

Sets the tag to be used to enclose the XML dataset.

Syntax

PROCEDURE setRowsetTag(
   ctxHdl IN ctxType,
   tag IN VARCHAR2);

ctxHdl

(IN)

tag

(IN)

SETROWTAG

Sets the tag to be used to enclose the XML element corresponding to a db.record.

Syntax

PROCEDURE setRowTag(
   ctxHdl IN ctxType,
   tag IN VARCHAR2);

ctxHdl

(IN)

tag

(IN)

SETSKIPROWS

Sets the number of rows to skip. By default, 0 rows are skipped.

Syntax

PROCEDURE setSkipRows(
   ctxHdl IN ctxType,
   rows IN NUMBER);

ctxHdl

(IN)

rows

(IN)

SETSQLTOXMLNAMEESCAPING

This 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.

Syntax

PROCEDURE setSQLToXMLNameEscaping(
   ctxHdl IN ctxType,
   flag IN BOOLEAN := true);

ctxHdl

(IN)

flag

(IN)

SETSTYLESHEETHEADER

Sets the stylesheet header (the stylesheet processing instructions) in the generated XML document. Passing NULL for the uri argument will unset the stylesheet header and the stylesheet type.

Syntax

PROCEDURE setStylesheetHeader(
   ctxHdl IN ctxType,
   uri IN VARCHAR2,
   type IN VARCHAR2 := 'text/xsl');

ctxHdl

(IN)

uri

(IN)

type

(IN)

SETTAGCASE

Specifies the case of the generated XML tags.

Syntax

PROCEDURE setTagCase(
   ctxHdl IN ctxType,
   tCase IN NUMBER);

ctxHdl

(IN)

tCase

(IN)

SETXSLT

Registers a stylesheet to be applied to generated XML. If a stylesheet was already registered, it is replaced by the new one. The options are described in the following table. Passing NULL for the uri argument or an empty string for the stylesheet argument will unset the stylesheet header and type.

PROCEDURE setXSLT( 
   ctxHdl IN ctxType,
   uri IN VARCHAR2, 
   ref IN VARCHAR2 := null);

PROCEDURE setXSLT(
   ctxHdl IN ctxType,
   stylesheet CLOB, 
   ref IN VARCHAR2 := null);

ctxHdl

(IN)

uri

(IN)

stylesheet

(IN)

ref

(IN)

SETXSLTPARAM

Sets the value of a top-level stylesheet parameter. The parameter value is expected to be a valid XPath expression; the string literal values would therefore have to be quoted explicitly. If no stylesheet is registered, this method is not operational.

Syntax

PROCEDURE setXSLTParam(
   ctxHdl IN ctxType,
   name IN VARCHAR2,
   value IN VARCHAR2);

ctxHdl

(IN)

name

(IN)

value

(IN)

USENULLATTRIBUTEINDICATOR

Specifies whether to use an XML attribute to indicate NULLness, or to do this by omitting the particular entity in the XML document.

Syntax

PROCEDURE setNullAttributeIndicator(
   ctxHdl IN ctxType, 
   flag IN BOOLEAN);

ctxHdl

(IN)

flag

(IN)

USETYPEFORCOLLELEMTAG

Specifies whether to use the collection element's type name as its element tag name. By default, the tag name for elements of a collection is the collection's tag name followed by _item.

Syntax

PROCEDURE useTypeForCollElemTag(
   ctxHdl IN ctxType,
   flag IN BOOLEAN := true);

ctxHdl

(IN)

flag

(IN)