Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E25788-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub
ARPLS376

174 DBMS_XMLQUERY

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

See Also:

Oracle XML DB Developer's Guide

This chapter contains the following topics:

ARPLS69934

Using DBMS_XMLQUERY

ARPLS73534

Security Model

Owned by XDB, the DBMS_XMLQUERY package must be created by SYS or XDB. The EXECUTE privilege is granted to PUBLIC. Subprograms in this package are executed using the privileges of the current user.

ARPLS69935

Constants

ARPLS69936Table 174-1 Constants of DBMS_XMLQUERY

Constant Description

DB_ENCODING

Used to signal that the DB character encoding is to be used.

DEFAULT_ROWSETTAG

The tag name for the element enclosing the XML generated from the result set (that is, for most cases the root node tag name) -- ROWSET.

DEFAULT_ERRORTAG

The default tag to enclose raised errors -- ERROR.

DEFAULT_ROWIDATTR

The default name for the cardinality attribute of XML elements corresponding to db.records -- NUM

DEFAULT_ROWTAG

The default tag name for the element corresponding to db. records -- ROW

DEFAULT_DATE_FORMAT

Default date mask --'MM/dd/yyyy HH:mm:ss'

ALL_ROWS

Indicates that all rows are needed in the output.

NONE

Used to specifies that the output should not contain any XML metadata (for example, no DTD).

DTD

Used to specify that the generation of the DTD is desired.

SCHEMA

Used to specify that the generation of the XML Schema is desired.

LOWER_CASE

Use lower case tag names.

UPPER_CASE

Use upper case tag names.


ARPLS69937

Types

ARPLS69938Table 174-2 Types of DBMS_XMLQUERY

Type Description

ctxType

The type of the query context handle. This is the return type of NEWCONTEXT


ARPLS69939

Summary of DBMS_XMLQUERY Subprograms

ARPLS69940Table 174-3 DBMS_XMLQUERY Package Subprograms

Method Description

CLOSECONTEXT

Closes or deallocates a particular query context.

GETDTD

Generates the DTD.

GETEXCEPTIONCONTENT

Returns the thrown exception's error code and error message.

GETNUMROWSPROCESSED

Returns the number of rows processed for the query.

GETVERSION

Prints the version of the XSU in use.

GETXML

Generates the XML document.

NEWCONTEXT

Creates a query context and it returns the context handle.

PROPAGATEORIGINALEXCEPTION

Tells the XSU that if an exception is raised, and is being thrown, the XSU should throw the very exception raised; rather then, wrapping it with an OracleXMLSQLException.

REMOVEXSLTPARAM

Removes a particular top-level stylesheet parameter.

SETBINDVALUE

Sets a value for a particular bind name.

SETCOLLIDATTRNAME

Sets the name of the id attribute of the collection element's separator tag.

SETDATAHEADER

Sets the XML data header.

SETDATEFORMAT

Sets the format of the generated dates in the XML document.

SETENCODINGTAG

Sets the encoding processing instruction in the XML document.

SETERRORTAG

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

SETMAXROWS

Sets the maximum number of rows to be converted to XML.

SETMETAHEADER

Sets the XML meta header.

SETRAISEEXCEPTION

Tells the XSU to throw the raised exceptions.

SETRAISENOROWSEXCEPTION

Tells the XSU to throw or not to throw an OracleXMLNoRowsException in the case when for one reason or another, the XML document generated is empty.

SETROWIDATTRNAME

Sets the name of the id attribute of the row enclosing tag.

SETROWIDATTRVALUE

Specifies the scalar column whose value is to be assigned to the id attribute of the row enclosing tag.

SETROWSETTAG

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

SETROWTAG

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

SETSKIPROWS

Sets the number of rows to skip.

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.

SETSTYLESHEETHEADER

Sets the stylesheet header.

SETTAGCASE

Specified the case of the generated XML tags.

SETXSLT

Registers a stylesheet to be applied to generated XML.

SETXSLTPARAM

Sets the value of a top-level stylesheet parameter.

USENULLATTRIBUTEINDICATOR

Specifies weather to use an XML attribute to indicate NULLness.

USETYPEFORCOLLELEMTAG

Tells the XSU to use the collection element's type name as the collection element tag name.


ARPLS69941

CLOSECONTEXT

Closes or deallocates a particular query context

ARPLS69942Syntax

PROCEDURE CLOSECONTEXT(
ctxHdl IN ctxType);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.

ARPLS69943

GETDTD

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

Syntax Description
FUNCTION GETDTD(

   ctxHdl IN ctxType,

   withVer IN BOOLEAN := false)

RETURN CLOB;

Function that generates the DTD based on the SQL query used to initialize the context.
PROCEDURE GETDTD(

   ctxHdl IN ctxType,

   xDoc IN CLOB,

   withVer IN BOOLEAN := false);

Procedure that generates the DTD based on the SQL query used to initialize the context; specifies the output CLOB for XML document result.

Parameter IN / OUT Description
ctxHdl (IN) Context handle.
withVer (IN) Generate the version information? TRUE for yes.
xDoc (IN) CLOB into which to write the generated XML document.

ARPLS69944

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.

ARPLS69945Syntax

PROCEDURE GETEXCEPTIONCONTENT(
ctxHdl IN ctxType,
errNo OUT NUMBER,
errMsg OUT VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
errNo (OUT) Error number.
errMsg (OUT) Error message.

ARPLS69946

GETNUMROWSPROCESSED

Return the number of rows processed for the query.

ARPLS69947Syntax

FUNCTION GETNUMROWSPROCESSED(
ctxHdl IN ctxType)
RETURN NUMBER;
Parameter IN / OUT Description
ctxHdl (IN) Context handle.

ARPLS69948

GETVERSION

Prints the version of the XSU in use.

ARPLS69949Syntax

PROCEDURE GETVERSION();
ARPLS69950

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.

Syntax Description
FUNCTION GETXML(

   sqlQuery IN VARCHAR2,

   metaType IN NUMBER := NONE)

RETURN CLOB;

This function uses a SQL query in string form.
FUNCTION GETXML(

   sqlQuery IN CLOB,

   metaType IN NUMBER := NONE)

RETURN CLOB;

This function uses a SQL query in CLOB form.
FUNCTION GETXML(

   ctxHdl IN ctxType,

   metaType IN NUMBER := NONE)

RETURN CLOB;

This function generates the XML document based on a SQL query used to initialize the context.
PROCEDURE GETXML(

   ctxHdl IN ctxType,

   xDoc IN CLOB,

   metaType IN NUMBER := NONE);

This procedure generates the XML document based on the SQL query used to initialize the context.

Parameter IN / OUT Description
ctxHdl (IN) Context handle.
metaType (IN) XML metadata type (NONE, DTD, or SCHEMA).
sqlQuery (IN) SQL query.
xDoc (IN) CLOB into which to write the generated XML document.

ARPLS69951

NEWCONTEXT

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

Syntax Description
FUNCTION NEWCONTEXT(

   sqlQuery IN VARCHAR2)

RETURN ctxType;

Creates a query context from a string.
FUNCTION NEWCONTEXT(

   sqlQuery IN CLOB)

RETURN ctxType;

Creates a query context from a CLOB.

Parameter IN / OUT Description
sqlQuery (IN) SQL query, the results of which to convert to XML.

ARPLS69952

PROPAGATEORIGINALEXCEPTION

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

ARPLS69953Syntax

PROCEDURE PROPAGATEORIGINALEXCEPTION(
ctxHdl IN ctxType,
flag IN BOOLEAN);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
flag (IN) TRUE if want to propagate original exception, FALSE to wrap in OracleXMLException.

ARPLS69954

REMOVEXSLTPARAM

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

ARPLS69955Syntax

PROCEDURE REMOVEXSLTPARAM(
ctxHdl IN ctxType,
name IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
name (IN) Name of the top level stylesheet parameter.

ARPLS69956

SETBINDVALUE

Sets a value for a particular bind name.

ARPLS69957Syntax

PROCEDURE SETBINDVALUE(
ctxHdl IN ctxType,
bindName IN VARCHAR2,
bindValue IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
bindName (IN) Bind name.
bindValue (IN) Bind value.

ARPLS69958

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.

ARPLS69959Syntax

PROCEDURE SETCOLLIDATTRNAME(
ctxHdl IN ctxType,
attrName IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
attrName (IN) Attribute name.

ARPLS69960

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.

ARPLS69961Syntax

PROCEDURE SETDATAHEADER(
ctxHdl IN ctxType,
header IN CLOB := null,
tag IN VARCHAR2 := null);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
header (IN) Header.
tag (IN) Tag used to enclose the data header and the rowset.

ARPLS69962

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.

ARPLS69963Syntax

PROCEDURE SETDATEFORMAT(
ctxHdl IN ctxType,
mask IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
mask (IN) The date mask.

ARPLS69964

SETENCODINGTAG

Sets the encoding processing instruction in the XML document.

ARPLS69965Syntax

PROCEDURE SETENCODINGTAG(
ctxHdl IN ctxType,
enc IN VARCHAR2 := DB_ENCODING);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
enc (IN) The encoding to use.

ARPLS69966

SETERRORTAG

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

ARPLS69967Syntax

PROCEDURE SETERRORTAG(
ctxHdl IN ctxType,
tag IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
tag (IN) Tag name.

ARPLS69968

SETMAXROWS

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

ARPLS69969Syntax

PROCEDURE SETMAXROWS (
ctxHdl IN ctxType,
rows IN NUMBER);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
rows (IN) Maximum number of rows to generate.

ARPLS69970

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.

ARPLS69971Syntax

PROCEDURE SETMETAHEADER(
ctxHdl IN ctxType, 
header IN CLOB := null);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
Header (IN) Header.

ARPLS69972

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.

ARPLS69973Syntax

PROCEDURE SETRAISEEXCEPTION(
ctxHdl IN ctxType,
flag IN BOOLEAN:=true);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
flag (IN) Throw raised exceptions? TRUE for yes, otherwise FALSE.

ARPLS69974

SETRAISENOROWSEXCEPTION

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

ARPLS69975Syntax

PROCEDURE SETRAISENOROWSEXCEPTION(
ctxHdl IN ctxType,
flag IN BOOLEAN:=false);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
flag (IN) Throws an OracleXMLNoRowsException if set to TRUE.

ARPLS69976

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.

ARPLS69977Syntax

PROCEDURE SETROWIDATTRNAME(
ctxHdl IN ctxType,
attrName IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
attrName (IN) Attribute name.

ARPLS69978

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.

ARPLS69979Syntax

PROCEDURE SETROWIDATTRVALUE(
ctxHdl IN ctxType,
colName IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
colName (IN) Column whose value is to be assigned to the row id attribute.

ARPLS69980

SETROWSETTAG

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

ARPLS69981Syntax

PROCEDURE SETROWSETTAG(
ctxHdl IN ctxType,
tag IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
tag (IN) Tag name.

ARPLS69982

SETROWTAG

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

ARPLS69983Syntax

PROCEDURE SETROWTAG(
ctxHdl IN ctxType,
tag IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
tag (IN) Tag name.

ARPLS69984

SETSKIPROWS

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

ARPLS69985Syntax

PROCEDURE SETSKIPROWS(
ctxHdl IN ctxType,
rows IN NUMBER);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
rows (IN) Maximum number of rows to skip.

ARPLS69986

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.

ARPLS69987Syntax

PROCEDURE SETSQLTOXMLNAMEESCAPING(
ctxHdl IN ctxType,
flag IN BOOLEAN := true);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
flag (IN) Turn on escaping? TRUE for yes, otherwise FALSE.

ARPLS69988

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.

ARPLS69989Syntax

PROCEDURE SETSTYLESHEETHEADER(
ctxHdl IN ctxType,
uri IN VARCHAR2,
type IN VARCHAR2 := 'text/xsl');
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
uri (IN) Stylesheet URI.
type (IN) Stylesheet type; defaults to "text/xsl".

ARPLS69990

SETTAGCASE

Specifies the case of the generated XML tags.

ARPLS69991Syntax

PROCEDURE SETTAGCASE(
ctxHdl IN ctxType,
tCase IN NUMBER);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
tCase (IN) The tag's case:
  • 0 for as are

  • 1 for lower case

  • 2 for upper case


ARPLS69992

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.

Syntax Description
PROCEDURE SETXSLT(

   ctxHdl IN ctxType,

   uri IN VARCHAR2,

   ref IN VARCHAR2 := null);

To un-register the stylesheet pass in a null for the uri.
PROCEDURE SETXSLT(

   ctxHdl IN ctxType,

   stylesheet CLOB,

   ref IN VARCHAR2 := null);

To un-register the stylesheet pass in a null or an empty string for the stylesheet.

Parameter IN / OUT Description
ctxHdl (IN) Context handle.
uri (IN) Stylesheet URI.
stylesheet (IN) Stylesheet.
ref (IN) URL to include, imported and external entities.

ARPLS69993

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.

ARPLS69994Syntax

PROCEDURE SETXSLTPARAM(
ctxHdl IN ctxType,
name IN VARCHAR2,
value IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
name (IN) Name of the top level stylesheet parameter.
value (IN) Value to be assigned to the stylesheet parameter.

ARPLS69995

USENULLATTRIBUTEINDICATOR

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

ARPLS69996Syntax

PROCEDURE SETNULLATTRIBUTEINDICATOR(
ctxHdl IN ctxType, 
flag IN BOOLEAN);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
flag (IN) Sets attribute to NULL if TRUE, omits from XML document if FALSE.

ARPLS69997

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.

ARPLS69998Syntax

PROCEDURE USETYPEFORCOLLELEMTAG(
ctxHdl IN ctxType,
flag IN BOOLEAN := true);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
flag (IN) Turn on use of the type name?

Reader Comment

   

Comments, corrections, and suggestions are forwarded to authors every week. By submitting, you confirm you agree to the terms and conditions. Use the OTN forums for product questions. For support or consulting, file a service request through My Oracle Support.

Hide Navigation

Quick Lookup

Database Library · Master Index · Master Glossary · Book List · Data Dictionary · SQL Keywords · Initialization Parameters · Advanced Search · Error Messages

Main Categories

This Page

  • Using DBMS_XMLQUERY
  • Summary of DBMS_XMLQUERY Subprograms
    • CLOSECONTEXT
    • GETDTD
    • GETEXCEPTIONCONTENT
    • GETNUMROWSPROCESSED
    • GETVERSION
    • GETXML
    • NEWCONTEXT
    • PROPAGATEORIGINALEXCEPTION
    • REMOVEXSLTPARAM
    • SETBINDVALUE
    • SETCOLLIDATTRNAME
    • SETDATAHEADER
    • SETDATEFORMAT
    • SETENCODINGTAG
    • SETERRORTAG
    • SETMAXROWS
    • SETMETAHEADER
    • SETRAISEEXCEPTION
    • SETRAISENOROWSEXCEPTION
    • SETROWIDATTRNAME
    • SETROWIDATTRVALUE
    • SETROWSETTAG
    • SETROWTAG
    • SETSKIPROWS
    • SETSQLTOXMLNAMEESCAPING
    • SETSTYLESHEETHEADER
    • SETTAGCASE
    • SETXSLT
    • SETXSLTPARAM
    • USENULLATTRIBUTEINDICATOR
    • USETYPEFORCOLLELEMTAG

This Document

New and changed documents:
RSS Feed HTML RSS Feed PDF