ou can think of an XML document as a relational table where the various elements represent columns in a database table. Therefore, by mapping the various elements to a table, you can store XML in relational databases. This mapping process is called XML-to-SQL mapping technology.

Oracle created the XML SQL (XSU) Java API to convert XML to SQL, and vice versa. Before you can perform such mapping, you must create the table to which you want to map the XML document. XSU maps XML elements to specified database table columns. You should be aware that mapping an XML document to a database table with XSU does not store the attributes of that XML document; but you can store those by applying an XSLT transformation to the XML document prior to storing it, transforming the attributes you want to store into elements, which you can map to columns. XSU also lets you retrieve data from the database and convert the data to an XML document. Again, XML documents generated with XSU do not include attributes; however, you can create them post-XSU by transforming the XSU-generated document with XSLT.

In this article you’ll see the steps required to map an example XML document with elements and attributes to an Oracle database 10g table, and to retrieve an XML document that includes attributes by mapping the database columns to an XML document. It’s worth noting that you’re not limited to using Oracle to store XML documents with XSU; you could just as easily use another relational database such as MySQL.

Preliminary Setup
To use Oracle as the database, you’ll need to install the Oracle 10g database, including the sample schemas. You’ll also need the XSU Java API classes. First, download and install XDK 10g. Add the following three files to your CLASSPATH variable, where represents your install path:

• /lib/xsu12.jar
• /lib/xmlparserv2.jar
• /lib/xdb.jar

Create a database instance; I’ve used an OracleDB database instance in this article. To establish a JDBC connection with a database, you need an appropriate JDBC driver; for Oracle 10g, add these three files to your CLASSPATH variable, where is the directory in which you installed Oracle 10g:

• /jdbc/lib/ojdbc14.jar
• /LIB/servlet.jar
• /jdbc/lib/classes12dms.jar

The examples in this article use the XML document catalog.xml, shown in Listing 1 (also available in the downloadable code).

You’ll need to create a database table that includes columns for each element tag and attribute in the example XML document. Here’s an SQL script you can use to create a database table named JOURNAL that has the appropriate columns:

   CREATE  TABLE OE.JOURNAL (      JOURNAL_TITLE VARCHAR(255),       PUBLISHER VARCHAR(255),       EDITION VARCHAR(255),       ARTICLE_SECTION VARCHAR(255),       TITLE VARCHAR(255),       AUTHOR VARCHAR(255)   );

XML Document to SQL Database Mapping
If you were to store the XML document in Listing 1 directly with XSU, only the element tags in the XML document would get stored. But you can store the attributes in the XML document by first converting the attributes to elements and storing the converted XML document rather than the original. You can convert the attributes to elements by applying an XSLT stylesheet to the XML document.

You store an XML document in the database using the OracleXMLSave class. Here’s the procedure. First, import the OracleXMLSave class:

   import oracle.xml.sql.dml.OracleXMLSave;

Next, create a database connection:

   DriverManager.registerDriver(new       oracle.jdbc.driver.OracleDriver());      Connection conn =  DriverManager.getConnection(      "jdbc:oracle:thin:@::,"      ",""");

The preceding code has several placeholders, delimited in brackets, for which you must substitute your own information:

• is the database host.
• is the database port.
• is the database SID.
• is the username to login to the database.
• is the password to login to the database.

You can get the appropriate values for , , and from your /NETWORK/ADMIN/tnsnames.ora file.

Create an instance of the OracleXMLSave class:

   OracleXMLSave oracleXMLSave = new       OracleXMLSave(conn, "JOURNAL");

In the preceding line, “JOURNAL” is the name of the database table you created during the preliminary setup.

You need to convert the attributes in the example XML document to element tags by applying an XSLT to the example XML document. First, assign an XSLT stylesheet to the OracleXMLSave object. Doing this causes the save process to transform the input XML document before saving it:

   Reader xsltReader=new FileReader(      new File("c:/catalog/input.xslt"));   oracleXMLSave.setXSLT(xsltReader, null);

The XSLT used to convert the attributes in the example XML document to elements is listed in Listing 2.

Applying the stylesheet results in the altered XML format shown below. Notice that the attributes of the original catalog.xml document have been transformed into elements:

                  Oracle Magazine       Oracle Publishing       Sept-Oct 2003       XML              Julie Basu              Oracle Magazine      Oracle Publishing      Nov-Dec 2003      SQL             Cameron ORourke        

Set the tag for the row-enclosing element. Setting the row tag to ‘null’ sets the top level element tags to correspond to the database rows. In this case, set the row tag to journal, so that each journal tag in the example XML document will correspond to a database row:

   oracleXMLSave.setRowTag("journal");

Finally, convert the example XML document to a database table, using this code:

   oracleXMLSave.insertXML(oracleXMLSave.getURL(      "file://c:/catalog/catalog.xml"));

In this case, the XSLT file input.xslt gets applied to the example XML document, then the document gets stored in the database.

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.