10
Generating XML Data from the Database

This chapter describes Oracle XML DB options for generating XML from the database. It explains in detail, the SQLX standard functions and Oracle-provided functions and packages for generating XML data from relational content.

Generating XML from the Database Using SQLX Functions

XMLElement(), XMLForest(), XMLConcat(), and XMLAgg() belong to the SQLX standard, an emerging SQL standard for XML. Because these are emerging standards the syntax and semantics of these functions are subject to change in the future in order to conform to the standard.

All of the generation functions convert user-defined types (UDTs) to their canonical XML format. In the canonical mapping the user-defined type's attributes are mapped to XML elements.

XMLElement() Function

XMLElement() function is based on the emerging SQL XML standard. It takes an element name, an optional collection of attributes for the element, and zero or more arguments that make up the element's content and returns an instance of type XMLType. See Figure 10-2. The XML_attributes_clause is described in the following section.

Figure 10-2 XMLElement() Syntax

It is similar to SYS_XMLGEN(), but unlike SYS_XMLGEN(), XMLElement() does not create an XML document with the prolog (the XML version information). It allows multiple arguments and can include attributes in the XML returned.

XMLElement() is primarily used to construct XML instances from relational data. It takes an identifier that is partially escaped to give the name of the root XML element to be created. The identifier does not have to be a column name, or column reference, and cannot be an expression. If the identifier specified is NULL, then no element is returned.

As part of generating a valid XML element name from an SQL identifier, characters that are disallowed in an XML element name are escaped. Partial escaping implies that SQL identifiers other than the ":" sign which are not representable in XML, are escaped using the # sign followed by the character's unicode representation in hexadecimal format. This can be used to specify namespace prefixes for the elements being generated. The fully escaped mapping escapes all non-XML characters in the SQL identifier name, including the ":" character.

XML_Attributes_Clause

XMLElement() also takes an optional XMLAttributes() clause, which specifies the attributes of that element. This can be followed by a list of values that make up the children of the newly created element. See Figure 10-3.

Figure 10-3 XML_attributes_clause Syntax

In the XMLAttributes() clause, the value expressions are evaluated to get the values for the attributes. For a given value expression, if the AS clause is omitted, the fully escaped form of the column name is used as the name of the attribute. If the AS clause is specified, then the partially escaped form of the alias is used as the name of the attribute. If the expression evaluates to NULL, then no attribute is created for that expression. The type of the expression cannot be an object type or collection.

The list of values that follow the XMLAttributes() clause are converted to XML format, and are made as children of the top-level element. If the expression evaluates to NULL, then no element is created for that expression.

Example 10-1 XMLElement(): Generating an Element for Each Employee

The following example produces an Emp XML element for each employee, with the employee's name as its content:

If the name of the element or attribute is being created from the ALIAS specified in the AS clause, then partially escaped mapping is used. If the name of the element or attribute is being created from a column reference, then fully escaped mapping is used. The following example illustrates these mappings:

XMLElement() does not validate the document produced with these namespace prefixes and it is the responsibility of the user to ensure that the appropriate namespace declarations are included as well. A full description of partial and full escaping has been specified as part of the emerging SQL XML standard.

The following example illustrates the use of namespaces to create an XML schema-based document. Assuming that an XML schema "http://www.oracle.com/Employee.xsd" exists and has no target namespace, then the following query creates an XMLType instance conforming to that schema:

Figure 10-4 XMLForest() Syntax

The list of value expressions are converted to XML format. For a given expression, if the AS clause is omitted, the fully escaped form of the column name is used as the name of the enclosing tag of the element.

For an object type or collection, the AS clause is mandatory, and for other types, it can still be optionally specified. If the AS clause is specified, then the partially escaped form of the alias is used as the name of the enclosing tag. If the expression evaluates to NULL, then no element is created for that expression.

Example 10-6 XMLForest(): Generating Elements for Each Employee with Name Attribute, Start Date, and Dept as Content

This example generates an Emp element for each employee, with a name attribute and elements with the employee's start date and department as the content.

Example 10-7 XMLForest(): Generating an Element from an UDT

You can also use XMLForest() to generate XML from user-defined types (UDTs). Using the same example as given in the following DBMS_XMLGEN section (Example 10-18, "DBMS_XMLGEN: Generating Complex XML"), you can generate a hierarchical XML for the employee, department example as follows:

XMLSEQUENCE() Function

XMLSequence() function returns a sequence of XMLType. The function returns an XMLSequenceType which is a VARRAY of XMLType instances. Since this function returns a collection, it can be used in the FROM clause of SQL queries. See Figure 10-5.

Figure 10-5 XMLSequence() Syntax

The first form inputs an XMLType instance and returns a VARRAY of top-level nodes. This form can be used to shred XML fragments into multiple rows.

The second form takes as input a REFCURSOR argument, with an optional instance of the XMLFormat object and returns the VARRAY of XMLTypes corresponding to each row of the cursor. This form can be used to construct XMLType instances from arbitrary SQL queries. Note that in this release, this use of XMLFormat does not support XML schemas.

XMLSequence() creates a collection of these top level elements into XMLType instances and returns that.

The TABLE function was then used to makes the collection into a table value which can be used in the FROM clause of queries.

Example 10-9 XMLSequence(): Generating An XML Document for Each Row of a Cursor Expression, Using SYS_REFCURSOR Argument

Here XMLSequence() creates an XML document for each row of the cursor expression and returns the value as an XMLSequenceType. The XMLFormat object can be used to influence the structure of the resulting XML documents. For example, a call such as:

For each row in table dept_xml_tab, the TABLE function is evaluated. Here, the extract() function creates a new XMLType instance that contains a fragment of all employee elements. This is fed to the XMLSequence() which creates a collection of all employees.

The TABLE function then explodes the collection elements into multiple rows which are correlated with the parent table dept_xml_tab. Thus you get a list of all the parent dept_xml_tab rows with the associated employees.

The extractValue() functions extract out the scalar values for the department number, employee number, and name.

XMLConcat() Function

XMLConcat() function concatenates all the arguments passed in to create a XML fragment. Figure 10-6 shows the XMLConcat() syntax. XMLConcat() has two forms:

The first form takes an XMLSequenceType, which is a VARRAY of XMLType and returns a single XMLType instance that is the concatenation of all of the elements of the varray. This form is useful to collapse lists of XMLTypes into a single instance.

The second form takes an arbitrary number of XMLType values and concatenates them together. If one of the value is null, it is ignored in the result. If all the values are NULL, the result is NULL. This form is used to concatenate arbitrary number of XMLType instances in the same row. XMLAgg() can be used to concatenate XMLType instances across rows.

XMLAgg() Function

XMLAgg() is an aggregate function that produces a forest of XML elements from a collection of XML elements. Figure 10-7 describes the XMLAgg() syntax, where the order_by_clause is:

ORDER BY [list of: expr [ASC|DESC] [NULLS {FIRST|LAST} ] ]

and number literals are not interpreted as column positions. For example, ORDER BY 1 does not mean order by the first column. Instead the number literals are interpreted just as any other literal.

As with XMLConcat(), any arguments that are null are dropped from the result. XMLAgg() function is similar to the SYS_XMLAGG() function except that it returns a forest of nodes, and does not take the XMLFormat() parameter. This function can be used to concatenate XMLType instances across multiple rows. It also allows an optional ORDER BY clause to order the XML values being aggregated.

XMLAgg() is an aggregation function and hence produces one aggregated XML result for each group. If there is no group by specified in the query, then it returns a single aggregated XML result for all the rows of the query. NULL values are dropped from the result.

Figure 10-7 XMLAgg() Syntax

The following example produces a Department element containing Employee elements with employee job ID and last name as the contents of the elements. It also orders the employee XML elements within the department by their last name.

XMLAgg() can be used to reflect the hierarchical nature of some relationships that exist in tables. The following example generates a department element for each department. Within this it creates elements for all employees of the department. Within each employee, it lists their dependents:

Figure 10-8 XMLColAttVal() Syntax

The name of the arguments are put in the name attribute of the column element. Unlike the XMLForest() function, the name of the element is not escaped in any way and hence this function can be used to transport SQL columns and values without escaped names.

Example 10-15 XMLColAttVal(): Generating an Emp Element Per Employee with Name Attribute and Elements with Start Date and Dept as Content

This example generates an Emp element for each employee, with a name attribute and elements with the employee's start date and department as the content.

Because the name associated with each XMLColAttVal() argument is used to populate an attribute value, neither the fully escaped mapping nor the partially escaped mapping is used.

Generating XML from Oracle9i Database Using DBMS_XMLGEN

DBMS_XMLGEN creates XML documents from any SQL query by mapping the database query results into XML. It gets the XML document as a CLOB or XMLType. It provides a "fetch" interface whereby you can specify the maximum rows and rows to skip. This is useful for pagination requirements in Web applications. DBMS_XMLGEN also provides options for changing tag names for ROW, ROWSET, and so on.

The parameters of the package can restrict the number of rows retrieved, the enclosing tag names. To summarize, DBMS_XMLGEN PL/SQL package allows you:

To create an XML document instance from any SQL query and get the document as a CLOB or XMLType.

To use a fetch interface with maximum rows and rows to skip. For example, the first fetch could retrieve a maximum of 10 rows, skipping the first four. This is useful for pagination in Web-based applications.

Options for changing tag names for ROW, ROWSET, and so on.

See Also:

"Generating XML with XSU's OracleXMLQuery", in Chapter 7, "XML SQL Utility (XSU)", and compare the functionality of OracleXMLQuery with DBMS_XMLGEN.

Sample DBMS_XMLGEN Query Result

The following shows a sample result from executing a "select * from scott.emp" query on a database:

DBMS_XMLGEN Calling Sequence

Figure 10-9 DBMS_XMLGEN Calling Sequence

Get the context from the package by supplying a SQL query and calling the newContext() call.

Pass the context to all the procedures/functions in the package to set the various options. For example to set the ROW element's name, use setRowTag(ctx), where ctx is the context got from the previous newContext() call.

Get the XML result, using the getXML() or getXMLType(). By setting the maximum rows to be retrieved for each fetch using the setMaxRows() call, you can call this function repeatedly, getting the maximum number of row set for each call. The function returns null if there are no rows left in the query.

getXML() and getXMLType() always return an XML document, even if there were no rows to retrieve. If you want to know if there were any rows retrieved, use the function getNumRowsProcessed().

Can be used in getXML function to specify whether to generate a DTD or XML Schema or none. Only the NONE specification is supported in the getXML functions for this release.

FUNCTION PROTOTYPES

newContext()

Given a query string, generate a new context handle to be used in subsequent functions.

FUNCTION

newContext(queryString IN VARCHAR2)

Returns a new context

PARAMETERS: queryString (IN)- the query string, the result of which needs to be converted to XML

RETURNS: Context handle. Call this function first to obtain a handle that you can use in the getXML() and other functions to get the XML back from the result.

FUNCTION

newContext(queryString IN SYS_REFCURSOR) RETURN ctxHandle;

Creates a new context handle from a passed in PL/SQL ref cursor. The context handle can be used for the rest of the functions. See the example:

setRowTag()

Sets the name of the element separating all the rows. The default name is ROW.

PROCEDURE

setRowTag(ctx IN ctxHandle,rowTag IN VARCHAR2);

PARAMETERS:

ctx (IN) - the context handle obtained from the newContext call,

rowTag (IN) - the name of the ROW element. NULL indicates that you do not want the ROW element to be present. Call this function to set the name of the ROW element, if you do not want the default "ROW" name to show up. You can also set this to NULL to suppress the ROW element itself. Its an error if both the row and the rowset are null and there is more than one column or row in the output.

setRowSetTag()

Sets the name of the document's root element. The default name is ROWSET

PROCEDURE

setRowSetTag(ctx IN ctxHandle, rowSetTag IN VARCHAR2);

PARAMETERS:

ctx (IN) - the context handle obtained from the newContext call,

rowsetTag (IN) - the name of the document element. NULL indicates that you do not want the ROW element to be present. Call this to set the name of the document root element, if you do not want the default "ROWSET" name in the output. You can also set this to NULL to suppress the printing of this element. However, this is an error if both the row and the rowset are null and there is more than one column or row in the output.

getXML()

Gets the XML document by fetching the maximum number of rows specified. It appends the XML document to the CLOB passed in.

PROCEDURE

getXML(ctx IN ctxHandle,

clobval IN OUT NCOPY clob,

dtdOrSchema IN number:= NONE);

PARAMETERS:

ctx (IN) - The context handle obtained from the newContext() call,

clobval (IN/OUT) - the clob to which the XML document is to be appended,

dtdOrSchema (IN) - whether you should generate the DTD or Schema. This parameter is NOT supported.

Use this version of the getXML function, to avoid any extra CLOB copies and if you want to reuse the same CLOB for subsequent calls. This getXML call is more efficient than the next flavor, though this involves that you create the lob locator. When generating the XML, the number of rows indicated by the setSkipRows call are skipped, then 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 function to check if any rows were retrieved or not.

dtdOrSchema (IN) - whether we should generate the DTD or Schema. This parameter is NOT supported.

RETURNS: An XMLType instance containing the document.

FUNCTION

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

Converts the query results from the passed in SQL query string to XML format, and returns the XML as a CLOB.

FUNCTION

getXMLType(sqlQuery IN VARCHAR2, dtdOrSchema IN NUMBER := NONE) RETURN XMLTYPE;

Converts the query results from the passed in SQL query string to XML format, and returns the XML as a CLOB.

getNumRowsProcessed()

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

FUNCTION

getNumRowsProcessed(ctx IN ctxHandle)

RETURN number

PARAMETERS: queryString (IN)- the query string, the result of which needs to be converted to XML RETURNS:

This gets the number of SQL rows that were processed in the last call to getXML. You can call this to find out if the end of the result set has been reached. This does not include the number of rows skipped. Use this function to determine the terminating condition if you are calling getXML in a loop. Note that getXML would always generate a XML document even if there are no rows present.

setMaxRows()

Sets the maximum number of rows to fetch from the SQL query result for every invocation of the getXML call.

maxRows (IN) - the maximum number of rows to get for each call to getXML.

The maxRows parameter can be used when generating paginated results using this utility. For instance when generating a page of XML or HTML data, you can restrict the number of rows converted to XML and then in subsequent calls, you can get the next set of rows and so on. This also can provide for faster response times.

setSkipRows()

Skips a given number of rows before generating the XML output for every call to the getXML routine.

The skipRows parameter can be used when generating paginated results for stateless web pages using this utility. For instance when generating the first page of XML or HTML data, you can set skipRows to zero. For the next set, you can set the skipRows to the number of rows that you got in the first case.

setConvertSpecialChars()

Sets whether special characters in the XML data need to be converted into their escaped XML equivalent or not. For example, the "<" sign is converted to &lt;. The default is to perform conversions.

PROCEDURE

setConvertSpecialChars(ctx IN ctxHandle,

conv IN boolean);

PARAMETERS: ctx (IN) - the context handle to use,

conv (IN) - true indicates that conversion is needed.

You can use this function to speed up the XML processing whenever you are sure that the input data cannot contain any special characters such as <, >, ", ' , and so on, which need to be escaped. Note that it is expensive to actually scan the character data to replace the special characters, particularly if it involves a lot of data. So in cases when the data is XML-safe, then this function can be called to improve performance.

useItemTagsForColl()

Sets the name of the collection elements. The default name for collection elements it the type name itself. You can override that to use the name of the column with the _ITEM tag appended to it using this function.

PROCEDURE useItemTagsForColl(ctx IN ctxHandle);

PARAMETERS: ctx (IN) - the context handle.

If you have a collection of NUMBER, say, the default tag name for the collection elements is NUMBER. You can override this behavior and generate the collection column name with the _ITEM tag appended to it, by calling this procedure.

restartQuery()

Restarts the query and generate the XML from the first row again.

PROCEDURE

restartQuery(ctx IN ctxHandle);

PARAMETERS: ctx (IN) - the context handle corresponding to the current query. You can call this to start executing the query again, without having to create a new context.

closeContext()

Closes a given context and releases all resources associated with that context, including the SQL cursor and bind and define buffers, and so on.

PROCEDURE

closeContext(ctx IN ctxHandle);

PARAMETERS: ctx (IN) - the context handle to close. Closes all resources associated with this handle. After this you cannot use the handle for any other DBMS_XMLGEN function call.

Conversion Functions

FUNCTION

convert(xmlData IN varchar2, flag IN NUMBER := ENTITY_ENCODE) return varchar2;

Encodes or decodes the passed in XML data string.

Encoding refers to replacing entity references such as '<' to their escaped equivalent, such as '&lt;'.

Decoding refers to the reverse conversion.

FUNCTION

convert(xmlData IN CLOB, flag IN NUMBER := ENTITY_ENCODE) return CLOB;

Encodes or decodes the passed in XML CLOB data.

Encoding refers to replacing entity references such as '<' to their escaped equivalent, such as '&lt;'.

Decoding refers to the reverse conversion.

Example 10-16 DBMS_XMLGEN: Generating Simple XML

This example creates an XML document by selecting out the employee data from an object-relational table and putting the resulting CLOB into a table.

Example 10-17 DBMS_XMLGEN: Generating Simple XML with Pagination

Instead of generating all the XML for all rows, you can use the fetch interface that DBMS_XMLGEN provides to retrieve a fixed number of rows each time. This speeds up response time and also can help in scaling applications that need a DOM API on the resulting XML, particularly if the number of rows is large.

The following example illustrates how to use DBMS_XMLGEN to retrieve results from table scott.emp:

-- create a table to hold the results..!
CREATE TABLE temp_clob_tab ( result clob);
declare
qryCtx dbms_xmlgen.ctxHandle;
result CLOB;
begin
-- get the query context;
qryCtx := dbms_xmlgen.newContext('select * from scott.emp');
-- set the maximum number of rows to be 5,
dbms_xmlgen.setMaxRows(qryCtx, 5);
loop
-- now get the result
result := dbms_xmlgen.getXML(qryCtx);
-- if there were no rows processed, then quit..!
exit when dbms_xmlgen.getNumRowsProcessed(qryCtx) = 0;
-- do some processing with the lob data..!
-- Here, we are inserting the results
-- into a table. You can print the lob out, output it to a stream,
-- put it in a queure
-- or do any other processing.
insert into temp_clob_tab values(result);
end loop;
--close context
dbms_xmlgen.closeContext(qryCtx);
end;
/

Here, for each set of 5 rows, you generate an XML document.

Example 10-18 DBMS_XMLGEN: Generating Complex XML

Complex XML can be generated using object types to represent nested structures:

When you input a user-defined type (UDT) value to DBMS_XMLGEN functions, the user-defined type is mapped to an XML document using canonical mapping. In the canonical mapping, user-defined type's attributes are mapped to XML elements. Attributes with names starting with "@" are mapped to attributes of the preceding element.

User-defined types can be used for nesting in the resulting XML document. For example, consider tables, EMP and DEPT:

To generate a hierarchical view of the data, that is, departments with employees in them, you can define suitable object types to create the structure inside the database as follows:

CREATE TYPE EMP_T AS OBJECT
(
"@empno" number, -- empno defined as an attribute!
ename varchar2(20)
);
/
-- You have defined the empno with an @ sign in front, to denote that it must
-- be mapped as an attribute of the enclosing Employee element.
CREATE TYPE EMPLIST_T AS TABLE OF EMP_T;
/
CREATE TYPE DEPT_T AS OBJECT
(
"@deptno" number,
dname varchar2(20),
emplist emplist_t
);
/
-- Department type, DEPT_T, denotes the department as containing a list of
-- employees. You can now query the employee and department tables and get
-- the result as an XML document, as follows:
declare
qryCtx dbms_xmlgen.ctxHandle;
result CLOB;
begin
-- get the query context;
qryCtx := dbms_xmlgen.newContext(
'SELECT
dept_t(deptno,dname,
CAST(MULTISET(select empno, ename
from emp e
where e.deptno = d.deptno) AS emplist_t)) AS deptxml
FROM dept d');
-- set the maximum number of rows to be 5,
dbms_xmlgen.setMaxRows(qryCtx, 5);
-- set no row tag for this result as we have a single ADT column
dbms_xmlgen.setRowTag(qryCtx,null);
loop
-- now get the result
result := dbms_xmlgen.getXML(qryCtx);
-- if there were no rows processed, then quit..!
exit when dbms_xmlgen.getNumRowsProcessed(qryCtx) = 0;
-- do whatever with the result..!
end loop;
end;
/

The MULTISET operator treats the result of the subset of employees working in the department as a list and the CAST around it, cast's it to the appropriate collection type. You then create a department instance around it and call the DBMS_XMLGEN routines to create the XML for the object instance. The result is:

Generating XML Using Oracle-Provided SQL Functions

In addition to the SQL standard functions, Oracle9i provides the SYS_XMLGEN and SYS_XMLAGG functions to aid in generating XML.

SYS_XMLGEN() Function

This Oracle specific SQL function is similar to the XMLElement() except that it takes a single argument and converts the result to XML. Unlike the other XML generation functions, SYS_XMLGEN() always returns a well-formed XML document. Unlike DBMS_XMLGEN which operates at a query level, SYS_XMLGEN() operates at the row level returning a XML document for each row.

Example 10-22 Using SQL_XMLGEN to Create XML

SELECT SYS_XMLGEN(employee_id)
FROM employees WHERE last_name LIKE 'Scott%';

The resulting XML document is:

<?xml version=''1.0''?>
<employee_id>60</employee_id>

SYS_XMLGEN Syntax

SYS_XMLGEN() takes in a scalar value, object type, or XMLType instance to be converted to an XML document. It also takes an optional XMLFormat (the old name was XMLGenFormatType) object that you can use to specify formatting options for the resulting XML document. See Figure 10-10.

Figure 10-10 SYS_XMLGEN Syntax

SYS_XMLGEN() takes an expression that evaluates to a particular row and column of the database, and returns an instance of type XMLType containing an XML document. The expr can be a scalar value, a user-defined type, or a XMLType instance.

If expr is a scalar value, the function returns an XML element containing the scalar value.

If expr is a type, the function maps the user-defined type attributes to XML elements.

If expr is a XMLType instance, then the function encloses the document in an XML element whose default tag name is ROW.

By default the elements of the XML document match the elements of expr. For example, if expr resolves to a column name, the enclosing XML element will have the same name as the column. If you want to format the XML document differently, specify fmt, which is an instance of the XMLFormat object.

In this release, the formatting argument for SYS_XMLGEN() accepts the schema and element name, and generates the XML document conforming to that registered schema.

The following example retrieves the employee email ID from the sample table oe.employees where the employee_id value is 205, and generates an instance of a XMLType containing an XML document with an EMAIL element.

Why is SYS_XMLGEN() so Powerful?

Using the object-relational infrastructure, you can create complex and nested XML instances from simple relational tables.

SYS_XMLGEN() creates an XML document from either of the following:

A user-defined type (UDT) instance

A scalar value passed

XML

and returns an XMLType instance contained in the document.

SYS_XMLGEN() also optionally inputs a XMLFormat object type through which you can customize the SQL results. A NULL format object implies that the default mapping behavior is to be used.

Using XMLFormat Object Type

You can use XMLFormat to specify formatting arguments for SYS_XMLGEN() and SYS_XMLAGG() functions.

SYS_XMLGEN() returns an instance of type XMLType containing an XML document. Oracle9i provides the XMLFormat object, which lets you format the output of the SYS_XMLGEN function.

Table 10-2 lists the XMLFormat attributes. of the XMLFormat object. The function that implements this type follows the table.

Table 10-2 Attributes of the XMLFormat Object

Attribute

Datatype

Purpose

enclTag

VARCHAR2(100)

The name of the enclosing tag for the result of the SYS_XMLGEN function. If the input to the function is a column name, the default is the column name. Otherwise the default is ROW. When schemaType is set to USE_GIVEN_SCHEMA, this attribute also gives the name of the XMLSchema element.

schemaType

VARCHAR2(100)

The type of schema generation for the output document. Valid values are 'NO_SCHEMA' and 'USE_GIVEN_SCHEMA'. The default is 'NO_SCHEMA'.

schemaName

VARCHAR2(4000)

The name of the target schema Oracle uses if the value of the schemaType is 'USE_GIVEN_SCHEMA'. If you specify schemaName, then Oracle uses the enclosing tag as the element name.

targetNameSpace

VARCHAR2(4000)

The target namespace if the schema is specified (that is, schemaType is GEN_SCHEMA_*, or USE_GIVEN_SCHEMA)

dburl

VARCHAR2(2000)

The URL to the database to use if WITH_SCHEMA is specified. If this attribute is not specified, the Oracle declares the URL to the types as a relative URL reference.

processingIns

VARCHAR2(4000)

User-provided processing instructions, which are appended to the top of the function output before the element.

Example 10-24 Creating a Formatting Object with createFormat

You can use the static member function createformat to implement the XMLFormat object. This function has most of the values defaulted. For example:

since the function cannot infer the name of the expression. You can override the default ROW tag by supplying an XMLFormat (the old name was "XMLGenFormatType") object to the first argument of the operator.

Example 10-27 Overriding the Default Column Name: Supplying an XMLFormat Object to the Operator's First Argument

For example, in the last case, if you wanted the result to have EMPNO as the tag name, you can supply a formatting argument to the function, as follows:

Example 10-28 SYS_XMLGEN(): Converting a User-Defined Type to XML

When you input a user-defined type value to SYS_XMLGEN(), the user-defined type gets mapped to an XML document using a canonical mapping. In the canonical mapping the user-defined type's attributes are mapped to XML elements.

Any type attributes with names starting with "@" are mapped to an attribute of the preceding element. User-defined types can be used to get nesting within the result XML document.

The MULTISET operator treats the result of the subset of employees working in the department as a list and the CAST around it, cast's it to the appropriate collection type. You then create a department instance around it and call SYS_XMLGEN() to create the XML for the object instance.

for each row of the department. The default name ROW is present because the function cannot deduce the name of the input operand directly.

Note:

The difference between SYS_XMLGEN() function and DBMS_XMLGEN package is apparent from the preceding example:

SYS_XMLGEN works inside SQL queries and operates on the expressions and columns within the rows

DBMS_XMLGEN works on the entire result set

Example 10-29 SYS_XMLGEN(): Converting an XMLType Instance

If you pass an XML document into SYS_XMLGEN(), SYS_XMLGEN() encloses the document (or fragment) with an element, whose tag name is the default ROW, or the name passed in through the formatting object. This functionality can be used to turn document fragments into well formed documents.

For example, the extract() operation on the following document, can return a fragment. If you extract out the EMPNO elements from the following document:

If the input was a column, then the column name would have been used as default. You can override the enclosing element name using the formatting object that can be passed in as an additional argument to the function. See "Using XMLFormat Object Type".

SYS_XMLAGG() Function

SYS_XMLAGG() function aggregates all XML documents or fragments represented by expr and produces a single XML document. It adds a new enclosing element with a default name, ROWSET. To format the XML document differently then specify fmt, the instance of XMLFORMAT object

Figure 10-11 SYS_XMLAGG() Syntax

Generating XML Using XSQL Pages Publishing Framework

Oracle9i introduced XMLType for use with storing and querying XML-based database content. You can use these database XML features to produce XML for inclusion in your XSQL pages by using the <xsql:include-xml> action element.

The SELECT statement that appears inside the <xsql:include-xml> element should return a single row containing a single column. The column can either be a CLOB or a VARCHAR2 value containing a well-formed XML document. The XML document will be parsed and included in your XSQL page.

Example 10-31 Using XSQL Servlet's <xsql:include-xml> and Nested XMLAgg() Functions to Aggregate the Results Into One XML Document

The following example uses nested xmlagg() functions to aggregate the results of a dynamically-constructed XML document containing departments and nested employees into a single XML "result" document, wrapped in a <DepartmentList> element:

Since it is more efficient for the database to aggregate XML fragments into a single result document, the <xsql:include-xml> element encourages this approach by only retrieving the first row from the query you provide.

For example, if you have a number of <Movie> XML documents stored in a table of XmlType called MOVIES, each document might look something like this:

You can use the built-in Oracle9i XPath query features to extract an aggregate list of all cast members who have received Oscar awards from any movie in the database using a query like this:

SELECT xmlelement("AwardedActors",
xmlagg(extract(value(m),
'/Movie/Cast/*[Award[@From="Oscar"]]')))
FROM movies m;
-- To include this query result of XMLType into your XSQL page,
-- simply paste the query inside an <xsql:include-xml> element, and add
-- a getClobVal() method call to the query expression so that the result will
-- be returned as a CLOB instead of as an XMLType to the client:
<xsql:include-xml connection="orcl92" xmlns:xsql="urn:oracle-xsql">
select xmlelement("AwardedActors",
xmlagg(extract(value(m),
'/Movie/Cast/*[Award[@From="Oscar"]]'))).getClobVal()
from movies m
</xsql:include-xml>

Note:

Again we use the combination of XMLElement() and XMLAgg() to have the database aggregate all of the XML fragments identified by the query into a single, well-formed XML document.

Failing to do this results in an attempt by the XSQL page processor to parse a CLOB that looks like:

<Actor>...</Actor>
<Actress>...</Actress>

Which is not well-formed XML because it does not have a single document element as required by the XML 1.0 specification. The combination of xmlelement() and xmlagg() work together to produce a well-formed result like this:

Generating XML Using XML SQL Utility (XSU)

The Oracle XML SQL Utility (XSU) can still be used with Oracle9i to generate XML. This might be useful if you want to generate XML on the middle-tier or the client. XSU now additionally supports generating XML on tables with XMLType columns.