Store and Retrieve XML from Databases with XSU : Page 3

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(
      conn, "SELECT JOURNAL_TITLE, PUBLISHER, EDITION, " + 
      "ARTICLE_SECTION, TITLE, AUTHOR FROM JOURNAL");

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.

   query.setRowTag("journal");

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", 
      TITLE, AUTHOR FROM JOURNAL

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.

Previous Page