RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Store and Retrieve XML from Databases with XSU : Page 2

Oracle's XML SQL (XSU) is a Java API that provides XML-to-SQL (and the reverse—SQL-to-XML) mapping services, letting you map XML elements to database table columns so you can store and retrieve XML documents with ease.

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:

   Connection conn =  DriverManager.getConnection(
The preceding code has several placeholders, delimited in brackets, for which you must substitute your own information:

  • <host> is the database host.
  • <port> is the database port.
  • <SID> is the database SID.
  • <user> is the username to login to the database.
  • <password> is the password to login to the database.
You can get the appropriate values for <host>, <port>, and <SID> from your <Oracle 10g>/NETWORK/ADMIN/tnsnames.ora file.

You need to convert the attributes in the example XML document to element tags by applying an XSLT to the example XML document.
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:

   <?xml version = '1.0' encoding = 'UTF-8'?>
       <journal_title>Oracle Magazine</journal_title>
       <publisher>Oracle Publishing</publisher>
       <edition>Sept-Oct 2003</edition>
       <title>Parsing XML Efficiently</title>
       <author>Julie Basu</author>
      <journal_title>Oracle Magazine</journal_title>
      <publisher>Oracle Publishing</publisher>
      <edition>Nov-Dec 2003</edition>
      <title>The Active Database</title>
      <author> Cameron ORourke </author>
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:

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

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

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date