WEBINAR:
On-Demand
Application Security Testing: An Integral Part of DevOps
SQL Database to XML Document Mapping
To transform data in the other directionfrom database tables to XMLyou 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.