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:
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(
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
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
, 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.