10.3 XML Developer Kits | Oracle Application Server 10g Essentials

Oracle provides XML Developer Kits (XDKs) that can develop web applications that leverage XML in four programming languages:

XDK for Java
XDK for C
XDK for C++
XDK for PL/SQL

These XDKs are used in the Oracle Application Server Containers for J2EE (OC4J) for Java-based web applications, while mod_fastcgi is used for C and C++, and mod_plsql is used for PL/SQL.

Along with the XDK APIs and command-line utilities that execute these APIs, Oracle Application Server provides several XML tools, including:

XML Class Generator
XML SQL Utility for performing database queries that produce XML, and also insert XML into an Oracle database
XSQL Pages that produce web content using XML similar to JSPs

A substantial amount of additional XML functionality is available when you use an Oracle database. However, because that functionality is available only with an Oracle database, it isn't covered here.

Table 10-1 lists the various features supported by the four XDKs. Of the four, the XDK for Java is the most feature-rich.

Table 10-1. XML Developer Kits and features

Feature	C	C++
XML 1.0
XML Namespaces 1.0
XML Path Language 1.0
XML Schemas Part 0-2
XML Parser: DOM 1.0
XML Parser: DOM 2.0 Core
XML Parser: DOM 2.0 Traversal
XML Parser: SAX 1.0
XML Parser: SAX 2.0	~ ^[1]	~ ^[1]
XML Parser: nonvalidating mode
XML Parser: validation against DTD
XML Parser: validation against Schema
XSLT 1.0
XML Class Generator
XML Transviewer Beans

^[1] Partial implementation

10.3.1 XML Parsers

The Oracle XML parsers are composed of two components :

XML Parser: Supports XML 1.0, including XML Namespaces, XPath, XSLT, DOM, and SAX with validation against a DTD
XML Schema Processor: Adds the ability to handle XML Schema documents to the XML Parser; it also enables the parser to validate an XML document against an XML Schema

10.3.2 XML Class Generators

The two available XML class generators, for Java and C++, create source code in the form of class files, from a DTD. The XML class generator for Java can also create class files from an XML Schema. Generated class files can then be used programmatically to construct XML documents in your web applications.

Manually coding a class file (in Java or C++) that represents an XML document can be both tedious and fraught with opportunities for creating errors. Using an XML class file generator removes the burden of coding such classes from a programmer while, at the same time, eliminating possible coding errors.

10.3.3 XML Transviewer Beans

The XML Transviewer Beans are a set of JavaBeans that are part of the XDK for Java. These Beans are components for viewing and transforming XML documents with Java. The XML Transviewer Beans are:

DOMBuilder Bean: A nonvisual JavaBean wrapped around the XML Parser for Java's DOMParser class that allows you to build one or more DOM trees asynchronously and provides all the functionality of the DOMParser class with a JavaBean interface
XSLTransformer Bean: A nonvisual JavaBean wrapped around the XML Parser for Java's XSLT engine that allows you to perform one or more XSL transformations asynchronously and provides all the functionality of the XSLT engine with a JavaBean interface
DBAccessBean: A nonvisual JavaBean that creates CLOB tables in a database and then use retrieves or stores XML documents in one of its CLOB tables

These components may be used server-side in web-based application development.

10.3.4 XML SQL Utility

The XML SQL Utility (XSU), a proprietary Oracle tool that can be used in any application server, allows you to perform a query against a database that is output as well-formed XML. XSU also supports extracting data from any well- formed XML document, then using that data to perform a SQL DML statement such as an INSERT, UPDATE, or DELETE. In addition, using XSU, you can create DTDs from a SQL query and perform XSL transformations.

XSU APIs exist for Java and PL/SQL. A command-line utility, OracleXML , also exists. XSU can be used in any middle- tier web server that supports Java servlets because it is a set of Java classes.

10.3.4.1 SQL to XML

The XSU can create a well-formed XML document from a SQL SELECT statement. It outputs the XML document as a Java String as a DOM tree in the form of a Java class XMLDocument or as SAX events. When creating an XML document, XSU outputs the columns of a SQL statement as XML elements that use the names or alias names of the columns in the order in which they appear in the SQL statement. Column elements are then enclosed in a <ROW> element, so a <ROW> element exists for each row from a query's result set. All <ROW> elements are, in turn , enclosed by a <ROWSET> element for the SELECT statement. An XML processing instruction is placed at the beginning of the XML document to identify the document as an XML document. This format of wrapping database result set rows with <ROW> and <ROWSET> tags is called by Oracle a canonical format.

For example, by specifying the query:

 select last_name, first_name from   person  order by last_name, first_name;

XSU would create a document in canonical format similar to the following:

 <?xml version='1.0'?> <ROWSET>   <ROW num="1">     <LAST_NAME>Bales</LAST_NAME>     <FIRST_NAME>Don</FIRST_NAME>   </ROW>   <ROW num="2">     <LAST_NAME>Greenwald</LAST_NAME>     <FIRST_NAME>Rick</FIRST_NAME>   </ROW>   <ROW num="3">     <LAST_NAME>Stackowiak</LAST_NAME>     <FIRST_NAME>Bob</FIRST_NAME>   </ROW> </ROWSET>

If a query is executed against an object table, the result set may consist of a complex datatype structure ”a nested table or array. A complex datatype is represented by elements for each column in the user -defined datatype, which are, in turn, enclosed by an element whose name is derived from the name of the user-defined datatype.

A nested table or array is represented by elements for each column in the collection, enclosed by an element whose name is derived by the name of the collection with _ITEM appended to it, with all item elements then enclosed by an element whose name is derived using the name of the collection.

Generated XML can be customized in any of three ways:

By manipulating the source SQL statement
By customizing default mappings
Possibly by postprocessing the created XML document using XSLT

Of the three techniques, the first is the most performant, while the last is the least performant.

Here are some example techniques for manipulating the source SQL statement:

Using alias names on columns to get the desired element names
Using the at sign (@) in a column alias to make the column an element attribute instead of an element value
Using subqueries or the SQL statements CAST/MULTISET to produce nested results
Using views or object-relational views with user-defined datatypes against relational tables to create new column names, complex datatypes, or embedded collections

XSU mapping customizations allow you to:

Specify that NULL values should be indicated with a null attribute instead of omitting an element
Specify the format for dates
Specify the case used for element names
Specify that collection elements should contain a cardinality attribute
Change the name of the ROW element or omit it altogether
Change the name of the ROW element's attribute num or omit it altogether
Change the name of the ROWSET element or omit it altogether

You can also register an XSL stylesheet with XSU. The registered stylesheet is then used by XSU to automatically post-transform the XML document generated with the SQL statement.

10.3.4.2 XML to SQL

The XSU can also be used to apply a SQL INSERT, UPDATE, or DELETE statement against a database based on an XML document in the canonical format defined earlier. If an XML document isn't in the desired or canonical format, any of three techniques can be applied to make it compatible:

By manipulating the SQL target
By customizing default mappings
Possibly by preprocessing the XML document using XSLT

In many ways, these are the inverse of the SQL to XML customizations.

You can manipulate the SQL target by creating a view or object-relational view against a table or set of tables to make the format of the XML input document match the database. You can create INSTEAD-OF triggers to distribute required SQL statements to the appropriate members of a multitable or multiobject view.

In this direction, XSU mapping customizations allow you to:

Use case-insensitive matching for mapping element names to SQL column names
Map an alternative element name to the canonical element ROW
Specify the format for dates

Just as with the SQL-to-XML generation, an XSL stylesheet can be registered with XSU that, in turn, is used by XSU to automatically pretransform the input XML document.

10.3.4.3 Inserting

To insert data into a database using an XML document, XSU does the following:

Retrieves the target table's metadata from the database. XSU uses the metadata to create an appropriate SQL INSERT statement.
If an XSL stylesheet is registered, transforms the XML document.
Extracts data by matching element names in the possibly transformed XML document to column names in the SQL INSERT statement.
Executes the INSERT statement.

By default, XSU tries to map values for all columns in the target SQL object (table, view, object-relational view, etc.). The Java and PL/SQL APIs allow you to specify the column names you want used in the INSERT statement.

10.3.4.4 Updating

To update a database using an XML document, you must first use the Java or PL/SQL API to set a list of (primary) key columns. Then XSU follows a process that is similar to inserting data, this time creating an appropriate SQL UPDATE statement. The columns to be updated can also be constrained by specifying the desired column names using the API.

10.3.4.5 Deleting

Deleting from a database using an XML document is similar to updating. Just as with an UPDATE statement, you must first specify the (primary) key column names using the API. XSU then follows the same process it does for inserting and updating.

10.3.4.6 Committing

XSU normally batches updates. If auto-commit is turned on in the JDBC driver supplying the database connection for XSU, and if the batch execution is successful, a batch is committed. If auto-commit is turned off, you must manually commit, or all SQL statement operations will have to wait to be committed when the JDBC connection is closed.

10.3.4.7 PL/SQL

The PL/SQL API for XSU is a wrapper around the Java API, so almost all the Java API functionality, as well as how it is employed programmatically, is the same for both APIs. XSU can be accessed in Oracle Application Server programmatically using the Java API in OC4J, using the PL/SQL API used by stored procedures executed through mod_plsql , or using XSQL Pages, described in the next section.

10.3.5 XSQL Pages

XSQL Pages simplify access to the XSU in a web server. XSQL Pages is a framework built on top of XSU that processes XSQL Pages document tags to create dynamic content in the form of XML. The process is similar to the way in which a JSP translator processes JavaServer Pages.

XSQL Pages documents are valid XML documents with a vocabulary that allows you to select, insert, update, and delete against a database using embedded SQL. XSQL tags also provide additional capabilities such as executing stored procedures, including other XML documents, and XSQL Pages.

XSQL Pages documents, by default, end with a .xsql suffix. When these documents are requested from Oracle Application Server, they are automatically processed by the XSQL servlet residing in the OC4J. The XSQL servlet processes the XSQL tags, creating an XML document. If an XSLT processing instruction is found, XSQL uses it to transform the XML document into another format. The resulting XML document is then sent to the requestor . An XSQL Pages file can also be processed using the XSQL Command Line Utility or programmatically using the Java class XSQLRequest .

10.3.5.1 Configuration

The XSQL Pages configuration is maintained in an XML document named XSQLConfig.xml . XSQLConfig.xml contains information such as database connections, database tuning parameters, stylesheet cache parameters, and so on.

10.3.5.2 SQL to XML

An XML Pages document can use five action tags to produce an XML document:

<xsql:query>: This tag embeds a SQL SELECT statement. When the SELECT statement is executed, it produces canonical XML content that incorporates the rows and columns of the SELECT statement.
<xsql:dml>: This tag executes any other kind of SQL statement: DML or DDL.
<xsql:ref-cursor-function>: This tag allows you to use a PL/SQL reference cursor returned from a stored procedure to produce XML just as with the use of a SELECT statement described earlier.
<xsql:include-owa>: This tag allows you to call a stored procedure that, in turn, uses the Oracle PL/SQL Web Toolkit functions in packages HTF and HTP to produce XML.
<xsql:include-xsql>: This tag allows you to include another XSQL Pages document in your current XSQL Page.

The first four action tags require a connection to a database. A database connection for these tags is specified with an attribute called connection . The value of the connection attribute corresponds to an alias for a database connection configured in XML document XSQLConfig.xml , as described earlier.

Parameters can be bound to embedded SQL statements or stored procedures with a bind-params attribute. Lexical substitution parameters can also be employed. These start with an at sign (@).

Parameter values can come from any of the following:

An explicit XSQL Pages <xsql:include-param> declaration
An HTTP cookie
An HTTP session object
An HTTP request object
A default value attribute

If no parameter value is found, a NULL value is used for a bind variable, while an empty string is used for lexical substitution parameters.

Default values can be specified for bind variables and lexical substitution parameters. A default value is specified including an attribute with the same name as the bind variable or lexical substitution parameter in question. This seemingly duplicate attribute's value specifies the desired default value. The database connection , bind-params , and default value attributes can be placed within the tag that requires them, or within one of its enclosing parents' tags.

10.3.5.3 XML to SQL

XSQL Pages can take a valid XML document in the canonical format or any valid XML document, such as XHTML, and transform it into the canonical format from an HTTP POST and use it to perform a SQL INSERT, UPDATE, or DELETE statement against a database. The XSQL servlet can also transform HTTP POST parameters into a valid XML document, transform the document into the canonical format using XSLT, and then use the canonically transformed XML document to perform these SQL operations.

The SQL INSERT, UPDATE, and DELETE statements are accomplished using the <xsql:insert-request> , <xsql:update-request> , and <xsql:delete-request> tags, respectively. In addition, a valid XML document sent as a request parameter in an HTTP GET request can insert data into a database using the <xsql:insert-param> tag.

10.3.5.4 Custom XSQL action handlers

You can create a custom action handler for XSQL pages by creating a Java class file that implements the oracle.xml.xsql.XSQLActionHandler interface. You then access the custom handler with the <xsql:action> tag, specifying the fully qualified name of your XSQLActionHandler class in the attribute handler .

10.3.5.5 Integrating XSQL Pages with JSP

A JSP calls on the services of XSQL Pages using a <jsp:include> or <jsp:forward> tag. In these tags, a programmer calls upon the XSQL Pages' XSQL servlet. In turn, the XSQL servlet either retrieves the requested data or applies the requested DML statement against the database.