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:
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>
, and <SID>
from your <Oracle 10g>/NETWORK/ADMIN/tnsnames.ora
|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
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(
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'?>
<title>Parsing XML Efficiently</title>
<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.