Browse DevX
Sign up for e-mail newsletters from DevX


Store and Retrieve XML from Databases with XSU : Page 3

Oracle's XML SQL (XSU) is a Java API that provides XML-to-SQL (and the reverse—SQL-to-XML) mapping services, letting you map XML elements to database table columns so you can store and retrieve XML documents with ease.

SQL Database to XML Document Mapping
To transform data in the other direction—from database tables to XML—you use the OracleXMLQuery class.

XSU creates an XML element corresponding to each database table column, but doesn't create element attributes. Again, to create the element attributes, you apply an XSLT to the XML document created from the database table.

Here's the procedure. First, import the OracleXMLQuery class:

   import oracle.xml.sql.query.OracleXMLQuery;
Next, create an OracleXMLQuery object instance.

   OracleXMLQuery query = new OracleXMLQuery(
In the preceding code, conn is the JDBC connection used to query the database.

Set the XSLT stylesheet that you want the OracleXMLQuery object to use to generate the final XML document, including element attributes:

   Reader xsltReader=new FileReader(
      new File("c:/catalog/output.xslt"));
   query.setXSLT(xsltReader, null);
You can see the XSLT output.xslt in Listing 3.

Set the row tag to the element tag in the generated XML document that should correspond to a database table row.

Finally, generate the XML document from the JOURNAL database table:

   String xmlString=query.getXMLString();
As with the previous example, the OracleXMLQuery instance applies the output.xslt stylesheet set earlier to the XML document generated with XSU, generating (recreating) the XML document shown in Listing 1. An XML document with attributes could also be generated by modifying the SQL SELECT statement, instead of applying an XSLT, but the XML document generated would consist of the root element ROWSET, instead of catalog, and row element ROW instead of journal. The SQL statement that would create attributes for JOURNAL._TITLE, PUBLISHER, EDITION, and ARTICLE_SECTION columns is as follows:

   SELECT JOURNAL_TITLE "@title", PUBLISHER "@publisher", 
      EDITION "@edition",  ARTICLE_SECTION "@section", 
In other words, taken together, the two processes you've seen here stored an XML document in a database, and then retrieved a copy of the original document. In the downloadable code for this project you'll find the XMLToDatabase.java program, and the sample XML file.

So, you've seen a workaround process that uses XSLT stylesheet to let you store and retrieve XML element attributes. While this article focused only on XSU's native ability to store and retrieve documents from a single database table, if you're willing to do a little extra work, you can break up the XML into chunks with single-table affinity and insert the content that way. You can also use updatable views. See Chapter 9, "Using the XML SQL Utility (XSU)" of the online Oracle XML Developer's Kit Programmer's Guide 10g Release 2 (10.2) for additional XSU constraints and solutions.

Deepak Vohra is an O'Reilly technical reviewer, who reviewed the book WebLogic: The Definitive Guide. He's also a NuBean consultant and web developer, and is a Sun Certified Java 1.4 Programmer and Sun Certified Web Component Developer for J2EE.
Thanks for your registration, follow us on our social networks to keep up-to-date