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.

Author's Note: As of XSU version 2.1.0 (included in XDK 10g) you can also map a database column to an attribute by customizing the SQL SELECT statement.

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 <XDK> represents your install path:

• <XDK>/lib/xsu12.jar
• <XDK>/lib/xmlparserv2.jar
• <XDK>/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 <Oracle10g> is the directory in which you installed Oracle 10g:

• <Oracle10g>/jdbc/lib/ojdbc14.jar
• <Oracle10g>/LIB/servlet.jar
• <Oracle10g>/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.

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)
   );