30
Generating Queries Using DBMS_XMLGEN for PL/SQL

The DBMS_XMLGEN Package is used to transform results of SQL queries into a canonical XML format.

This chapter discusses the following topics:

• Description of DMS_XMLGEN
• Functions and Procedures of DBMS_XMLGEN

• 
   

  See Also: Oracle9i XML Database Developer's Guide - Oracle XML DB Oracle9i Supplied PL/SQL Packages and Types Reference

---

DBMS_XMLGEN Package

Description of DMS_XMLGEN

DBMS_XMLGEN 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 in the database.

Functions and Procedures of DBMS_XMLGEN

Table 30-1 Summary of Functions and Procedures of DBMS_XMLGEN

Function/Procedure	Description
newContext()	Creates a new context handle.
setRowTag()	Sets the name of the element enclosing each row of the result. The default tag is ROW.
setRowSetTag ()	Sets the name of the element enclosing the entire result. The default tag is ROWSET.
getXML()	Gets the XML document.
getNumRowsProcessed()	Gets the number of SQL rows that were processed in the last call to getXML.
setMaxRows()	Sets the maximum number of rows to be fetched each time.
setSkipRows()	Sets the number of rows to skip every time before generating the XML. The default is 0.
setConvertSpecialChars()	Sets whether special characters such as $, which are non-XML characters, should be converted or not to their escaped representation. The default is to perform the conversion.
convert()	Converts the XML into the escaped or unescaped XML equivalent.
useItemTagsForColl()	Forces the use of the collection column name appended with the tag _ITEM for collection elements. The default is to set the underlying object type name for the base element of the collection.
restartQUERY()	Restarts the query to start fetching from the beginning.
closeContext()	Closes the context and releases all resources.

newContext()

Description

Generates and returns a new context handle; this context handle is used in getXML() and other functions to get XML back from the result. The available options are given in the following table.

Syntax	Description
DBMS_XMLGEN.newContext ( query IN VARCHAR2) RETURN ctxHandle;	Generates a new context handle from a query.
DBMS_XMLGEN.newContext ( queryString IN SYS_REFCURSOR) RETURN ctxHandle;	Generates a new context handle from a query string in the form of a PL/SQL ref cursor

Parameter	IN / OUT	Description
query	(IN)	The query, in the form of a VARCHAR, the result of which must be converted to XML
queryString	(IN)	The query string in the form of a PL/SQL ref cursor, the result of which must be converted to XML.

setRowTag()

Description

Sets the name of the element separating all the rows. The default name is ROW. User can set this 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.

Syntax

DBMS_XMLGEN.setRowTag (
   ctx     IN ctxHandle,
   rowTag  IN VARCHAR2);

Parameter	IN / OUT	Description
ctx	(IN)	The context handle obtained from the newContext call.
rowTag	(IN)	The name of the ROW element. Passing NULL indicates that you do not want the ROW element present.

setRowSetTag ()

Description

Sets the name of the root element of the document. The default name is ROWSET. User can set the rowSetTag 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.

Syntax

DBMS_XMLGEN.setRowSetTag ( 
   ctx        IN ctxHandle,  
   rowSetTag  IN VARCHAR2);

Parameter	IN / OUT	Description
ctx	(IN)	The context handle obtained from the newContext call.
rowSetTag	(IN)	The name of the document element. Passing NULL indicates that you do not want the ROWSET element present.

getXML()

Description

Gets the XML document. When the rows indicated by the setSkipRows() call are skipped, the maximum number of rows as specified by the setMaxRows() call (or the entire result if not specified) is fetched and converted to XML. Use the getNumRowsProcessed() to check if any rows were retrieved. The available options are given in the following table.

Syntax	Description
FUNCTION DBMS_XMLGEN.getXML ( ctx IN ctxHandle, clobval IN OUT NCOPY clob, dtdOrSchema IN number := NONE) RETURN boolean;	This procedure 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() to avoid any extra CLOB copies and to reuse the same CLOB for subsequent calls. Because of the CLOB reuse, this getXML() call is potentially more efficient.
FUNCTION DBMS_XMLGEN.getXML ( ctx IN ctxHandle, dtdOrSchema IN number := NONE) RETURN clob;	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.
FUNCTION DBMS_XMLGEN.getXML ( sqlQuery IN VARCHAR2, 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. This temporary CLOB must be subsequently freed using the DBMS_LOB.FREETEMPORARY call.
FUNCTION DBMS_XMLGEN.getXMLType ( ctx IN ctxhandle, dtdOrSchema IN number := NONE) RETURN sys.XMLType;	Generates the XML document and returns it as a sys.XMLType. XMLType operations can be performed on the results, including ExistsNode and Extract. This also provides a way of obtaining the results as a string by using the getStringVal() function, if the result size is less than 4K.
FUNCTION DBMS_XMLGEN.getXMLType ( sqlQuery IN VARCHAR2, 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. XMLType operations can be performed on the results, including ExistsNode and Extract. This also provides a way of obtaining the results as a string by using the getStringVal() function, if the result size is less than 4K.

 

Parameter	IN / OUT	Description
ctx	(IN)	The context handle obtained from the newContext call.
clobval	(IN/OUT)	The clob to which the XML document is appended.
sqlQuery	(IN)	The SQL query string.
dtdOrSchema	(IN)	The Boolean to indicate generation of either a DTD or a schema. NONE is the only option currently supported.

getNumRowsProcessed()

Description

Retrieves the number of SQL rows processed when generating the XML using the getXML call; this count does not include the number of rows skipped before generating the XML. Used to determine the terminating condition if calling getXML() in a loop. Note that getXML() always generates an XML document, even if there are no rows present.

Syntax

DBMS_XMLGEN.getNumRowsProcessed (
   ctx  IN ctxHandle)
RETURN NUMBER;

Parameter	IN / OUT	Description
ctx	(IN)	The context handle obtained from the newContext call.

setMaxRows()

Description

Sets the maximum number of rows to fetch from the SQL query result for every invokation of the getXML call. 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);

Parameter	IN / OUT	Description
ctx	(IN)	The context handle corresponding to the query executed.
maxRows	(IN)	The maximum number of rows to get for each call to getXML.

setSkipRows()

Description

Skips a given number of rows before generating the XML output for every call to the getXML routine. 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().

Syntax

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

Parameter	IN / OUT	Description
ctx	(IN)	The context handle corresponding to the query executed.
skipRows	(IN)	The number of rows to skip for each call to getXML.

setConvertSpecialChars()

Description

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 &lt;. The default is to perform conversions. 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);

Parameter	IN / OUT	Description
ctx	(IN)	The context handle obtained from the newContext call.
conv	(IN)	TRUE indicates that conversion is needed.

convert()

Description

Converts the XML data into the escaped or unescaped XML equivalent; returns XML CLOB data in encoded or decoded format. Escapes the XML data if the ENTITY_ENCODE is specified. For example, the escaped form of the character < is &lt;. Unescaping is the reverse transformation. The available options are given in the following table.

Syntax	Description
DBMS_XMLGEN.convert ( xmlData IN VARCHAR2, flag IN NUMBER := ENTITY_ENCODE) RETURN VARCHAR2;	Uses xmlData in string form (VARCHAR2).
DBMS_XMLGEN.convert ( xmlData IN CLOB, flag IN NUMBER := ENTITY_ENCODE) RETURN CLOB;	Uses xmlData in Clob form.

Parameter	IN / OUT	Description
xmlData	(IN)	The XML CLOB data to be encoded or decoded.
flag	(IN)	The flag setting; ENTITY_ENCODE (default) for encode, and ENTITY_DECODE for decode.

useItemTagsForColl()

Description

Overrides the default name of the collection elements. The default name for collection elements is the type name itself. 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. Using this procedure, the user can override this behavior and generate the collection column name with the _ITEM tag appended to it.

Syntax

DBMS_XMLGEN.useItemTagsForColl (
   ctx  IN ctxHandle);

Parameter	IN / OUT	Description
ctx	(IN)	The context handle.

restartQUERY()

Description

Restarts the query and generates the XML from the first row. Can be used to start executing the query again, without having to create a new context.

Syntax

DBMS_XMLGEN.restartQUERY (ctx  IN ctxHandle);

Parameter	IN / OUT	Description
ctx	(IN)	The context handle corresponding to the current query.

closeContext()

Description

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 DBMS_XMLGEN function call.

Syntax

DBMS_XMLGEN.closeContext ( ctx  IN ctxHandle);

Parameter	IN / OUT	Description
ctx	(IN)	The context handle to close.