Oracle XML-SQL Utility (XSU)

Oracle XML-SQL Utility (XSU) supports Java and PL/SQL.

• XML-SQL Utility is comprised of core Java class libraries for automatically and dynamically rendering the results of arbitrary SQL queries into canonical XML. It includes the following features:
  • Supports queries over richly-structured user-defined object types and object views.

  • Supports automatic "XML Insert" of canonically-structured XML into any existing table, view, object table or object view. By combining with XSLT transformations, virtually any XML document can be automatically inserted into the database.

  XML-SQL Utility Java classes can be used for the following tasks:

  • Generate from an SQL query or Result set object a text or XML document, a Document Object Model (DOM), or a Document Type Definition (DTD).

  • Load data from an XML document into an exisitng database schema or view.

• XML-SQL Utility for PL/SQL is comprised of a PL/SQL package that wraps the XML-SQL Utility for Java.

Figure 3-5 shows the Oracle XML-SQL Utility overall functionality.

Figure 3-5 Oracle XML-SQL Utility Functional Diagram

XML SQL Utility for Java consists of a set of Java classes that perform the following tasks:

• Pass a query to the database and generate an XML document (text or DOM) from the results or the DTD which can be used for validation.

• Write XML data to a database table

Generating XML from Query Results

Figure 3-6 shows how XML SQL Utility processes SQL queries and returns the results as an XML document.

Figure 3-6 XMl-SQL Utility Processes SQL Queries and Returns the Result as an XML Document

XML Document Structure: Columns Are Mapped to Elements

The structure of the resulting XML document is based on the internal structure of the database schema that returns the query results:

• Columns are mapped to top level elements

• Scalar values are mapped to elements with text-only content

• Object types are mapped to elements with attributes appearing as sub-elements.

• Collections are mapped to lists of elements.

XSU Generates the XML Document as a String or DOM Element Tree

The XML-SQL Utility (XSU) generates either of the following:

• A string representation of the XML document. Use this respresentation if you are returning the XML document to a requester.

• An in-memory XML DOM tree of elements. Use this representation if you are operating on the XML programmatically, for example, transforming it using the XSLT Processor using DOM methods to search or modify the XML in some way.

XSU Generates a DTD Based on Queried Table's Schema

You can also use the XML-SQL Utility (XSU) to generate a DTD based on the schema of the underlying table or view being queried. You can use the generated DTD as input to the XML Class Generator for Java or C++. This generates a set of classes based on the DTD elements. You can then write code that uses these classes to generate the infrastructure behind a web-based form. See also "XML Class Generator".

Based on this infrastructure, the web form can capture user data and create an XML document compatible with the database schema. This data can then be written directly to the corresponding database table or object view without further processing.

See Also: Chapter 4, "Using XML-SQL Utility (XSU)" and Chapter 13, "B2B XML Application: Step by Step", for more information about this approach.

Note: To write an XML document to a database table, where the XML data does not match the underlying table structure, transform the XML document before writing it to the database. For techniques on doing this, see Chapter 4, "Using XML-SQL Utility (XSU)".