Where Can You Run XML-SQL Utility (XSU)?

The XML-SQL Utility is written in Java and can be run in any tier that supports Java, such as the following:

• In the database

• In Middle Tier Application servers which support Java -- call XSU from middle tier servers

• In Web Servers -- call the XSU Java APIs from within servlets running inside web servers.

• On the client -- use the XSU's Java API in your Java applications or use XSU's command line front end.

Running XML-SQL Utility in the Database

The java classes which make up the XSU can be loaded into the Oracle8i JServer; furthermore, the XSU contains a PL/SQL wrapper which published the XSU's Java API to PL/SQL creating a PL/SQL API. This way one can write new java applications which run inside the database and which directly access the XSU's Java API; one can write PL/SQL applicatons which access XSU through its PL/SQL API; or one can access the XSU's funtionality directly through SQL. Note that to load and run Java code inside the database you need a java enabled Oracle8i Sever.

Figure 4-1 shows how a typical architecture for such a system. XML generated from XSU running inside the database can be placed in advanced queues in the database to be queued to other systems or clients. The XML can be used from within stored procedures inside the database or shipped outside via web servers or application servers.

Note in the figure that all lines are bi-directional. Since XSU can generate as well as put data, data can come from various sources to XSU running inside the database and can be put back in the appropriate database tables.

Figure 4-1 Running XML-SQL Utility in the Database

Running XML-SQL Utility in the Middle Tier

Your application architecture might force the use of an 'application server' in the middle tier that is separate from the database. This application tier could be an Oracle database, an Oracle 'application server', or a third party application server that supports Java programs.

You may want to generate XML in the middle tier from SQL queries or ResultSets for various reasons. For example, to integrate different JDBC data sources in the middle tier. In this case, you can use the Java version of XSU and then call the XSU Java API directly from Java programs running in the middle tier.

Figure 4-2, shows how a typical architecture for running XSU in a middle tier. Data from JDBC sources is converted by XSU in the middle tier and then sent to web servers or other systems. Again, the whole process is bi-directional and the data can be put back into the JDBC sources (database tables or views) using XSU. If an Oracle8i database itself is used as the application server, then you can also use the PL/SQL front-end instead of Java.

Figure 4-2 Running XML-SQL Utility in the MIddle Tier

Running XML-SQL Utility in a Web Server

The Java version of XSU can also be run in the web server itself, as long as the web server supports Java servlets. XSU can be directly called by Java servlets and the XML can be generated and put back from the web server itself. The XSQL servlet is a standard servlet provided by Oracle which calls the utility to generate and save XML data. If this is the primary intent for using this product, then consider using the XSQL servlet, as it eliminates a lot of servlet coding and provides a simpler template approach for XML processing from the web server.

See: Chapter 19, "Using XSQL Servlet" for information about using the XSQL Servlet.

What Does XML-SQL Utility Work With?

XML-SQL Utility needs the following components in order to function:

• A Data Source. XML-SQL Utility (XSU) needs the JDBC drivers. The utility can work with any JDBC drivers but is optimized for Oracle's JDBC drivers. Oracle, does not make any guarantees or provide support for the XSU running against non-Oracle databases.

• XML Parser. XSU needs the Oracle XML Parser, Version 2. This is provided as part of the Oracle8i install, and is also available as part of the XSU install downloadable from Oracle Technology Network (OTN) web site.

XSU Features

XSU consists of a command line front end, a Java API and a PL/SQL API. The main features of the XSU are as follows:

• Supports generation of XML documents from any SQL query. It virtually supports all the datatypes supported in the Oracle8i database server.

• Supports dynamic generation of DTDs (Document Type Definitions). In the future we will support XMLSchemas as well.

• Supports simple transformations in the generation such as modifying the default tag names for the ROW element etc. One can also register a XSL transformation which is then applied to the generated XML documents on the fly.

• Can generate XML documents in their string or DOM representations.

• Supports insertion of XML into database tables/views. It also can update records or delete records from a database object, given an XML document.

• Complex nested XML documents can be easily generated and stored in to relational tables by creating object views over these flat tables and querying over these views. Object views can create structured data from existing relational data using Oracle8i's object-relational infrastructure.