Using XML-SQL Utility (XSU)

XML has rapidly become the format for data interchange. Today, a substantial amount of business data resides in object-relational databases. It is therefore necessary to transform this "relational" data to XML for purposes of communication. XML-SQL Utility (XSU) provides a simple way of achieving this data transformation, by mapping canonically any SQL query result to XML and vice versa.

For example, to retrieve the results of the employee table in scott's schema (which is available in all databases as a default schema), we can supply the query,

select * from scott.emp

to the utility and with the default settings will result in an XML document as shown below:-

<?xml version='1.0'?>
<ROWSET>
	 	 <ROW num="1">
 	 	  <EMPNO>7369</EMPNO>
    <ENAME>Smith</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

You can take a similar document and also insert it back in to the same table. XSU also provides APIs for updating and deleting XML documents.

When to Use XML-SQL Utility (XSU)

The XML-SQL Utility (XSU) provides the basic functionality to get and put data to and from the database. The utility provides a canonical mapping back and forth with some simple transformations (such as changing the name of the ROW tag generated for each row). Any complex transformations can be achieved by applying an industry standard XSL (XML Stylesheet Language) transformation over the document. Oracle XML Parser V2 supports a powerful XSL processor, XSLT, to perform transformations. XSL transformations can be also registered with the XSU directly, thus the XML generated by the XSU will be automatically transformed.

Note: If the primary focus of your application is to generate XML pages to web sources, then you can consider using the XSQL servlet. This is a standard Java servlet that can automate this process and provide a simpler XML template-like language to specify the transformations.