Automatic XML from SQL Queries

In Example 11.2, we wrote a class that produces the XML output of any SQL query using the JDBC ResultSetMetaData class. This example takes a straightforward approach, producing the XML representation for all of the rows of a ResultSet with columns of type NUMBER, DATE, and VARCHAR2. In practice, developers need a more robust solution that supports the full range of Oracle8i object-relational SQL features, including the ability to query user-defined datatypes. They need better control over the number of rows retrieved and the format of the output tags. Extending our XMLForResultSet class to offer generic support for these additional requirements would be a non-trivial task. Luckily, we don’t have to build this code ourselves since Oracle provides the XML SQL Utility for Java, which includes the OracleXMLQuery component that automates the entire job.

Using OracleXMLQuery to Do the Work for You

OracleXMLQuery is a utility class that automates the task of producing XML from SQL query results. It gracefully handles all SQL queries, returning XML for result sets containing both scalar datatypes and user-defined object types in their row values. It offers numerous handy options to control the XML output and is equally adept at producing query results as XML text for web delivery, and in-memory Document Object Model (DOM) tree structures.

To exploit the facilities of the XML SQL Utility, you can use the OracleXML command-line program or you can use the

Get Building Oracle XML Applications now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.