Building Oracle XML Applications

Automatic XML Generation with DBXML

We could certainly continue with PL/SQL examples, demonstrating how to write stored procedures to:

• Format the query results from multiple SQL statements into a single resulting XML page

• Generically process any SQL query, using the built-in DBMS_SQL package, generating appropriate XML tags for their column names

• Automatically search for additional information in related tables by checking the database dictionary views for metadata about foreign key and primary key constraints

But luckily, we don’t have to write this code ourselves, since Oracle provides all this functionality in the freely downloadable set of XML Utilities for PL/SQL called PLSXML which includes lots of demos and source code (see http://technet.oracle.com/tech/xml/info/plsxml/xml4plsql.htm). The readme.html file in the readme directory in the PLSXML distribution provides setup instructions.

Letting DBXML Do the Work for You

The heart of the PLSXML suite of utilities is a PL/SQL package called DBXML. The package offers a key procedure named Query that accepts a SQL query to be processed and automatically produces the XML output in the OWA page buffer. As Example 10.11 illustrates, it is practically no work at all to use Dbxml.Query. Passing any query to it as a string causes the appropriately formatted XML document representing its query results to be sent to the HTP page buffer.

Example 10-11. Automatically Producing Stock Quote XML with DBXML

CREATE PROCEDURE StockQuotesDbxmlBasic( ...