114
DBMS_XMLGEN

The DBMS_XMLGEN package 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.

This chapter contains the following topic:

• Summary of DBMS_XMLGEN Subprograms

  See Also: Oracle XML DB Developer's Guide, for more information on XML support and on examples of using DBMS_XMLGEN

Summary of DBMS_XMLGEN Subprograms

Table 114-1  Summary of DBMS_XMLGEN Package Subprograms

CLOSECONTEXT Procedure

This procedure closes a given context and releases all resources associated with it, including the SQL cursor and bind and define buffers. After this call, the handle cannot be used for a subsequent function call.

Syntax

DBMS_XMLGEN.CLOSECONTEXT (
   ctx  IN ctxHandle);

Parameters

Table 114-2 CLOSECONTEXT Procedure Parameters

CONVERT Functions

This function converts the XML data into the escaped or unescapes XML equivalent, and returns XML CLOB data in encoded or decoded format. There are several version of the function.

Syntax

Uses XMLDATA in string form (VARCHAR2):

DBMS_XMLGEN.CONVERT (
   xmlData IN VARCHAR2,
   flag    IN NUMBER := ENTITY_ENCODE)
RETURN VARCHAR2;

Uses XMLDATA in CLOB form:

DBMS_XMLGEN.CONVERT (
   xmlData IN CLOB,
   flag    IN NUMBER := ENTITY_ENCODE)
 RETURN CLOB;

Parameters

Table 114-3 CONVERT Function Parameters

Usage Notes

This function escapes the XML data if the ENTITY_ENCODE is specified. For example, the escaped form of the character < is <. Unescaping is the reverse transformation.

GETNUMROWSPROCESSED Function

This function retrieves the number of SQL rows processed when generating the XML using the GETXML Functions call. This count does not include the number of rows skipped before generating the XML. Note that GETXML Functions always generates an XML document, even if there are no rows present.

Syntax

DBMS_XMLGEN.GETNUMROWSPROCESSED (
   ctx     IN    ctxHandle)
RETURN NUMBER;

Parameters

Table 114-4 GETNUMROWSPROCESSED Function Parameters

Usage Notes

This function is used to determine the terminating condition if calling GETXML Functions in a loop.

GETXML Functions

This function gets the XML document. The function is overloaded.

Syntax

Gets the XML document by fetching the maximum number of rows specified. It appends the XML document to the CLOB passed in. Use this version of GETXML Functions to avoid any extra CLOB copies and to reuse the same CLOB for subsequent calls. Because of the CLOB reuse, this GETXML Functions call is potentially more efficient:

DBMS_XMLGEN.GETXML (
   ctx          IN ctxHandle, 
   tmpclob      IN OUT NCOPY CLOB,
   dtdOrSchema  IN number := NONE)
 RETURN BOOLEAN;

Generates the XML document and returns it as a temporary CLOB. The temporary CLOB obtained from this function must be freed using the DBMS_LOB.FREETEMPORARY call:

DBMS_XMLGEN.GETXML (
   ctx          IN ctxHandle,
   dtdOrSchema  IN number := NONE)
 RETURN CLOB;

Converts the results from the SQL query string to XML format, and returns the XML as a temporary CLOB, which must be subsequently freed using the DBMS_LOB.FREETEMPORARY call:

DBMS_XMLGEN.GETXML (
   sqlQuery     IN VARCHAR2,
   dtdOrSchema  IN number := NONE)
 RETURN CLOB;

Parameters

Table 114-5  GETXML Function Parameters

ctx

tmpclob

Usage Notes

When the rows indicated by the SETSKIPROWS Procedure call are skipped, the maximum number of rows as specified by the SETMAXROWS Procedure call (or the entire result if not specified) is fetched and converted to XML. Use the GETNUMROWSPROCESSED Function to check if any rows were retrieved.

GETXMLTYPE Functions

This function gets the XML document and returns it as an XMLTYPE. XMLTYPE operations can be performed on the results. This function is overloaded.

Syntax

Generates the XML document and returns it as a sys.XMLType:

DBMS_XMLGEN.GETXMLTYPE (
   ctx           IN ctxhandle,
   dtdOrSchema   IN number := NONE)
 RETURN sys.XMLType;

Converts the results from the SQL query string to XML format, and returns the XML as a sys.XMLType:

DBMS_XMLGEN.GETXMLTYPE (
   sqlQuery     IN VARCHAR2,
   dtdOrSchema  IN number := NONE)
 RETURN sys.XMLType

Parameters

Table 114-6 GETXMLTYPE Function Parameters

NEWCONTEXT Functions

This function generates and returns a new context handle. This context handle is used in GETXML Functions and other functions to get XML back from the result. There are several version of the function.

Syntax

Generates a new context handle from a query:

DBMS_XMLGEN.NEWCONTEXT ( 
      query     IN VARCHAR2) 
 RETURN ctxHandle;

Generates a new context handle from a query string in the form of a PL/SQL ref cursor:

DBMS_XMLGEN.NEWCONTEXT (
   queryString  IN SYS_REFCURSOR)
 RETURN ctxHandle;

Parameters

Table 114-7 NEWCONTEXT Function Parameters

RESTARTQUERY Procedure

This procedure restarts the query and generates the XML from the first row. It can be used to start executing the query again, without having to create a new context.

Syntax

DBMS_XMLGEN.RESTARTQUERY (
   ctx  IN ctxHandle);

Parameters

Table 114-8 RESTARTQUERY Procedure Parameters

SETCONVERTSPECIALCHARS Procedure

This procedure sets whether or not special characters in the XML data must be converted into their escaped XML equivalent. For example, the < sign is converted to <. The default is to perform conversions. This function improves performance of XML processing when the input data cannot contain any special characters such as <, >, ",', which must be escaped. It is expensive to scan the character data to replace the special characters, particularly if it involves a lot of data.

Syntax

DBMS_XMLGEN.SETCONVERTSPECIALCHARS (
   ctx   IN ctxHandle,
   conv  IN BOOLEAN);

Parameters

Table 114-9 SETCONVERTSPECIALCHARS Procedure Parameters

SETMAXROWS Procedure

This procedure sets the maximum number of rows to fetch from the SQL query result for every invokation of the GETXML Functions call. It is used when generating paginated results. For example, when generating a page of XML or HTML data, restrict the number of rows converted to XML or HTML by setting the maxrows parameter.

Syntax

DBMS_XMLGEN.SETMAXROWS (
   ctx      IN ctxHandle,
   maxRows  IN NUMBER);

Parameters

Table 114-10 SETMAXROWS Procedure Parameters

SETNULLHANDLING Procedure

This procedure sets NULL handling options, handled through the flag parameter setting.

Syntax

DBMS_XMLGEN.SETNULLHANDLING(
   ctx  IN ctx,
   flag IN NUMBER); 

Parameters

Table 114-11 SETNULLHANDLING Procedure Parameters

SETROWSETTAG Procedure

This procedure sets the name of the root element of the document. The default name is ROWSET.

Syntax

DBMS_XMLGEN.SETROWSETTAG ( 
   ctx            IN ctxHandle,  
   rowSetTagName  IN VARCHAR2);

Parameters

Table 114-12 SETROWSETTAG Procedure Parameters

Usage Notes

The user can set the rowSetTag to NULL to suppress the printing of this element. However, an error is produced if both the row and the rowset are NULL and there is more than one column or row in the output . This is because the generated XML would not have a top-level enclosing tag, and so would be invalid.

SETROWTAG Procedure

This procedure sets the name of the element separating all the rows. The default name is ROW.

Syntax

DBMS_XMLGEN.SETROWTAG (
   ctx         IN ctxHandle,
   rowTagName  IN VARCHAR2);

Parameters

Table 114-13 SETROWTAG Procedure Parameters

Usage Notes

The user can set the name of the element to NULL to suppress the ROW element itself. However, an error is produced if both the row and the rowset are NULL and there is more than one column or row in the output. This is because the generated XML would not have a top-level enclosing tag, and so would be invalid.

SETSKIPROWS Procedure

This procedure skips a given number of rows before generating the XML output for every call to the GETXML Functions. It is used when generating paginated results for stateless Web pages using this utility. For example, when generating the first page of XML or HTML data, set skiprows to zero. For the next set, set the skiprows to the number of rows obtained in the first case. See GETNUMROWSPROCESSED Function.

Syntax

DBMS_XMLGEN.SETSKIPROWS (
   ctx       IN ctxHandle,
   skipRows  IN NUMBER);

Parameters

Table 114-14 SETSKIPROWS Procedure Parameters

USEITEMTAGSFORCOLL Procedure

This procedure overrides the default name of the collection elements. The default name for collection elements is the type name itself.

Syntax

DBMS_XMLGEN.USEITEMTAGSFORCOLL (
   ctx  IN ctxHandle);

Parameters

Table 114-15 USEITEMTAGSFORCOLL Procedure Parameters

Usage Notes

Using this function, you can override the default to use the name of the column with the _ITEM tag appended to it. If there is a collection of NUMBER, the default tag name for the collection elements is NUMBER.

USENULLATTRIBUTEINDICATOR Procedure

This procedure specifies 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. It is used as a shortcut for the SETNULLHANDLING Procedure.

Syntax

DBMS_XMLGEN.USENULLATTRIBUTEINDICATOR(
   ctx       IN   ctxType,
   attrind   IN   BOOLEAN := TRUE);

Parameters

Table 114-16 USENULLATTRIBUTEINDICATOR Procedure Parameters