Using the SQLXML data type

Document options Document options requiring JavaScript are not displayed Discuss Sample code Rate this page Help us improve this content

Level: Intermediate

Deepak Vohra (dvohra09@yahoo.com), Consultant, Independent

09 May 2006
Updated 07 Apr 2008

If you're a Java™ 2 Enterprise Edition (J2EE) developer, you'll want to preview the SQLXML technology. Check out procedures to create an XML document, store an XML document in a relational database, retrieve an XML document from a database, and navigate an XML document with the SQLXML Java data type.

Overview

J2EE developers are commonly required to store an XML document in a relational database and retrieve an XML document from a database to obtain element and attribute values. Currently, only vendor-specific technologies and data types are available to store an XML document in a relational database. For example, IBM DB2 Universal Database™ (UDB) provides the DB2 XML Extender to store an XML document and XML user-defined types (UDTs). A standard data type for XML type columns and a Java data type corresponding to XML type columns would facilitate the storage and retrieval of XML documents in a relational database. The SQL:2003 standard supports such a data type: the new XML data type, for storing XML documents in a relational database. With the XML data type, you can store an XML document in a database table column similar to the other data types. DB2 database Version 9.x supports the XML data type. The Java Database Connectivity (JDBC) 4.0 specification supports the SQL:2003 standard. IBM Data Server Driver for JDBC and SQLJ Version 4.0 supports JDBC 4.0 specification.

Preliminary setup

Before you begin, make sure you have the necessary components to follow along. SQLXML is a JDBC 4.0 data type. Because the SQLXML data type is implemented in the Java Development Kit (JDK) 6.0, you'll need to install it. Another requirement to create an SQLXML type object in a relational database that supports the XML data type. DB2 database Version 9.x supports the XML data type. To run the sample application included with this article, SQLXMLDB.java, install a DB2 9.x database and create the sample DB2 database instance, Sample. JDK 6.0, DB2 9, and SQLXMLDB.java are available from Resources and Download, respectively.

You'll also require a JDBC 4.0 driver. IBM Data Server Driver for JDBC and SQLJ Version 4.0 is a JDBC 4.0 driver. To run SQLXMLDB.java with IBM Data Server Driver for JDBC and SQLJ Version 4.0, add JAR files db2jcc4.jar and db2jcc_license_cu.jar to the Classpath.

Getting started

The SQLXML Java data type lets you map an XML type database column to a Java data type. In the JDBC 4.0 specification, the java.sql.Connection interface can create an SQLXML object which initially does not have any data, but you can add data with the setString(), setBinaryStream(), setCharacterStream(), or setResult() methods. An SQLXML object is retrievable from a ResultSet or a CallableStatement object with the getSQLXML(int columnIndex) method or the getSQLXML(String columnName) method. Retrieve the data in a SQLXML object with one of the getString(), getBinaryStream(), getCharacterStream(), or getSource() methods. You can store an SQLXML object in a database table column of type XML similar to any other data type with the setSQLXML(int parameterIndex, SQLXML sqlXML) method of the PreparedStatement interface.

Listing 1 shows an example of an XML document, catalog.xml, which you can create:

<catalog title="XML Zone" publisher="IBM developerWorks"> 
 <journal date="Jan 2006"> 
  <article>
   <title>Managing XML data: Tag URIs</title>
   <author>Elliotte Harold</author>
  </article>
  <article> 
   <title>Practical data binding: XPath as data binding tool, Part 2</title>
   <author>Brett McLaughlin</author>
  </article>
 </journal>
</catalog>

Back to top

Selecting a database

A relational database is required to support the XML data type to store an XML document. Not all databases support the XML data type. To find out if a database supports it, obtain the database metadata from a Connection object. For example, to test if DB2 9 supports the XML data type, load and register the com.ibm.db2.jcc.DB2Driver JDBC driver, the DB2 JDBC Type 4 driver:

 
Class.forName("com.ibm.db2.jcc.DB2Driver");

Specify the connection URL for DB2 UDB database. The format of DB2 UDB Type 4 driver is jdbc:db2://<server>:<port>/<database>. In the connection URL, <server> is the DB2 UDB server name, <port> is the DB2 database port, and <database> is the DB2 database instance. The connection URL for DB2 UDB database is:

String url = "jdbc:db2://localhost:50000/Sample";

Obtain a Connection with the database with DriverManager static method getConnection():

Connection connection = DriverManager.getConnection(url,
                     "username", "password");

Next, obtain database metadata from the Connection object:

DatabaseMetaData metadata =  connection.getMetaData();

Subsequently, get the data types supported by the database with the getTypeInfo() method:

ResultSet rs = metadata.getTypeInfo();

Iterate over the data type result set and output the TYPE_NAME column:

System.out.println("TYPE_NAME:"+rs.getString("TYPE_NAME"));

If the database supports the XML data type, the XML TYPE_NAME gets output. As the DB2 9 database supports the XML data type, output from running SQLXMLDB.java application includes the XML data type as shown here:

TYPE_NAME: XML

Creating an SQLXML object

This section discusses the procedure to create an SQLXML object. First, import the java.sql package and the javax.xml.stream package. The javax.xml.stream package has the XMLStreamWriter and XMLStreamReader interfaces required to instantiate an SQLXML object and obtain data in an SQLXML object.

import java.sql.*;
import javax.xml.stream.*;

The Java representation of an XML document in a database table column of type XML is SQLXML. To create an XML document to be stored in the XML type column, create an SQLXML object from the Connection object with the createSQLXML() method:

SQLXML sqlXML = connection.createSQLXML();

The SQLXML object created with the createSQLXML() method does not contain any data. The next section shows how to add data to the SQLXML object.

Initializing an SQLXML object

You can initialize an SQLXML object using one of the setString(), setBinaryStream(), setCharacterStream(), or setResult() methods. To initialize an SQLXML object, use the setResult() method and the StAXResult class. Create an XMLStreamWriter object from the StAXResult object with getXMLStreamWriter() method.

StAXResult staxResult = sqlXML.setResult(StAXResult.class);
XMLStreamWriter xmlStreamWriter = staxResult.getXMLStreamWriter();

After you invoke the setResult() method, the SQLXML object becomes non-writable.

Add the start of an XML document with the writeStartDocument(String encoding, String version) method. The encoding specified in the writeStartDocument() method does not set the encoding of the XML document output; the encoding specified only sets the encoding in the XML declaration. The XMLStreamWriter interface also includes the writeStartDocument() method to create an XML document without specifying encoding and version, and the writeStartDocument(String version) method to create an XML document with version:

xmlStreamWriter.writeStartDocument("UTF-8","1.0");

Add the start of the root element, catalog, with the writeStartElement(String localName) method as shown in following code snippet. Use the writeStartElement(String prefix, String localName, String namespaceURI) method to create an element with a namespace and a prefix. Generate an empty element with the writeEmptyElement(String localName) method:

xmlStreamWriter.writeStartElement("catalog");

Add the title attribute with the writeAttribute(String localName, String value) method, as shown in the following code snippet. Similarly, add the publisher attribute. If an attribute has a namespace prefix, use the method writeAttribute(String prefix, String namespaceURI, String localName, String value):

xmlStreamWriter.writeAttribute("title", "XML Zone");
mlStreamWriter.writeAttribute("publisher", "IBM developerWorks");

Similarly, add the journal element and its attribute, date:

xmlStreamWriter.writeStartElement("journal");
xmlStreamWriter.writeAttribute("date", "Jan 2006");

Add an article element and a title element:

xmlStreamWriter.writeStartElement("article");
xmlStreamWriter.writeStartElement("title");

Add text to a title element with the writeCharacters(String text) method, as shown in the following code snippet. Alternatively, add text from a char[] array with the method writeCharacters(char[] text,int start,int len).

xmlStreamWriter.writeCharacters("Managing XML data: Tag URIs");

Ensure that the start element has a corresponding end element tag. Use the writeEndElement() method to add an end element:

xmlStreamWriter.writeEndElement();

The writeEndElement() method does not specify the element local name as the writeStartElement() does. Similarly, add the other elements to create the XML document shown in Listing 1. To complete the XML document, invoke the writeEndDocument() method. Also, close the XMLStreamWriter object:

xmlStreamWriter.writeEndDocument();
xmlStreamWriter.close();

You can also add an XML document to an SQLXML object from an XML string with the setString(String) method of interface SQLXML, as shown in Listing 2.

sqlXML.setString("<catalog title="DB2 Zone" publisher="IBM developerWorks"> 
 <journal date="Jan 2006"> 
  <article>
   <title>Managing XML data: Tag URIs</title>
   <author>Elliotte Harold</author>
  </article>
  <article> 
   <title>Practical data binding: XPath as data binding tool, Part 2</title>
   <author>Brett McLaughlin</author>
  </article>
 </journal>
</catalog>");

The SQLXML object becomes non-writable after the setString() method is invoked. If the setString(), setBinaryStream(), setCharacterStream(), or setResult() method is invoked on an SQLXML object that has been previously initiated SQLException gets generated. The following section explains how to store the SQLXML object created in a database.

Storing an SQLXML object

The SQLXML Java data type is stored in an XML document just like any other Java data type. First, create a database table with an XML type column. From an SQL command line tool or with the JDBC API, create the database table with an XML type column. To create a database table with JDBC, obtain a Statement object from the Connection object:

Statement stmt = connection.createStatement();

Create a database table, Catalog, for example, with an XML type column:

stmt.executeUpdate("CREATE Table Catalog(CatalogId INTEGER, Catalog XML)");

Store the SQLXML object, created in the previous section, to the database. Create a PreparedStatement object to add values to the database table Catalog. The PreparedStatement consists of an INSERT SQL statement with parameter markers for the values to be added to database:

PreparedStatement statement = 
    connection.prepareStatement("INSERT INTO CATALOG(catalogId, catalog) VALUES(?,?)");

Set the int value with the setInt(int parameterIndex, int value) method and the SQLXML value with the setSQLXML(int parameterIndex, SQLXML value) method of the PreparedStatement interface, as shown below. If you do not close the XMLStreamWriter object prior to invoking the setSQLXML() method, SQLException is thrown.

statement.setInt(1, 1);
statement.setSQLXML(2, sqlXML);

Update the database with the executeUpdate() method:

statement.executeUpdate();

SQLXML objects are valid for at least the duration of the transaction in which the SQLXML objects are created. If you do not use an SQLXML object, release the SQLXML object resources with the free() method:

sqlXML.free();

Updating an SQLXML object

JDK 6.0 also provides updater methods in the ResultSet interface to update SQLXML values. Updater methods updateSQLXML(int columnIndex, SQLXML sqlXML) and updateSQLXML(String columnName, SQLXML sqlXML) revise the SQLXML value in the current row or the insert row of a ResultSet object. For example, to add a new row in a ResultSet object, obtain a Statement object of an updateable ResultSet type:

Statement stmt = connection.createStatement(
                   ResultSet.TYPE_SCROLL_INSENSITIVE,
                   ResultSet.CONCUR_UPDATABLE);

Obtain a ResultSet from the database table Catalog, which has a column of type XML. Move the ResultSet cursor to insert a row. Add the SQLXML column value with the updateSQLXML() method. The updateSQLXML() methods do not update the database. To update the database, invoke the insertRow() method:

ResultSet rs = stmt.executeQuery("SELECT * from Catalog");
rs.moveToInsertRow();
rs.updateSQLXML(2, xmlObject);
rs.insertRow();

Alternatively, to update ResultSet from a current row, in a scrollable ResultSet, move to a ResultSet row with an absolute(int) or relative(int) method. Revise the SQLXML value with one of the updater methods. Update the database with the updateRow() method:

rs.absolute(5);
updateSQLXML("catalog", xmlObject)
rs.updateRow();

If you do not close the XMLStreamWriter object prior to invoking the updater methods, SQLException gets thrown.

Retrieving an SQLXML object from a ResultSet object

Retrieve an XML database data type column value as an SQLXML Java type. Create a PreparedStatement for a SELECT SQL query to obtain a ResultSet from the database table, Catalog, as shown in the following code snippet. The SQL statement has a parameter marker for CatalogId value.

PreparedStatement stmt = 
    connection.prepareStatement("SELECT * FROM  CATALOG WHERE CatalogId=?");

Specify the CatalogId value for which an XML document is to be retrieved:

stmt.setInt(1, 1);

Obtain a result set with the executeQuery() method:

ResultSet rs = stmt.executeQuery();

Use the ResultSet interface method getSQLXML(int columnIndex) or getSQLXML(String columnName) to get the SQLXML object for the XML type Catalog column:

SQLXML sqlXML = rs.getSQLXML("Catalog");

To output the XML document in the SQLXML object, you might use the getString() method of SQLXML interface:

System.out.println(sqlXML.getString());

Accessing an SQLXML object

Retrieve the XML document in a SQLXML object using one of the getBinaryStream(), getCharacterStream(), or getSource() methods. Use the XMLStreamReader interface to read an XML document in an SQLXML object with an event iterator. Create an XMLStreamReader object from the SQLXML object that is to be navigated:

InputStream binaryStream = sqlXML.getBinaryStream();
XMLInputFactory factory = XMLInputFactory.newInstance();
XMLStreamReader xmlStreamReader = factory.createXMLStreamReader(binaryStream);

The SQLXML object becomes non-readable after the getBinaryStream() method is invoked. The next parse event is obtained with the next() method:

while(xmlStreamReader.hasNext()){
int parseEvent = xmlStreamReader.next();
}

The next() method returns an int value which corresponds to an XMLStreamConstants constant. Table 1 lists the return values of the next() method.

If the return value is START_ELEMENT, the parse event indicates an element has been parsed. Use the methods getLocalName(), getPrefix(), getNamespaceURI() to obtain the local name, prefix, and namespace elements:

if(parseEvent==XMLStreamConstants.START_ELEMENT){
System.out.println("Element Local Name: "+xmlStreamReader.getLocalName());
System.out.println("Element Prefix: "+xmlStreamReader.getPrefix());
System.out.println("Element Namespace:"+xmlStreamReader.getNamespaceURI()); 
}

Obtain the attribute count in an element with the getAttributeCount() method. Iterate over the attributes and obtain the attribute local name with the getAttributeLocalName() method, the attribute value with the getAttributeValue() method, the attribute prefix with the getAttributePrefix() method, and the attribute namespace with the getAttributeNamespace() method:

for(int i=0; i<xmlStreamReader.getAttributeCount();i++){

System.out.println("Attribute Prefix:"+xmlStreamReader.getAttributePrefix(i));
System.out.println("Attribute Namespace:"+xmlStreamReader.getAttributeNamespace(i));
System.out.println("Attribute Local Name:"+xmlStreamReader.getAttributeLocalName(i));
System.out.println("Attribute Value:"+xmlStreamReader.getAttributeValue(i));
}

If the parse event is of type CHARACTERS, obtain the text of the parse event with the getText() methods:

if(parseEvent==XMLStreamConstants.CHARACTERS){
    System.out.println("CHARACTERS text: "+xmlStreamReader.getText());
    }

Conclusion

The SQLXML data type provides a database-to-Java mapping facility for database type XML. You can use the SQLXML data type with any database that supports the XML data type, and provides a JDBC 4.0 driver. Vendor-specific XML-to-SQL APIs are not required with SQLXML. The SQLXML API is included in JDK 6.0. DB2 9 supports the XML data type. IBM Data Server Driver for JDBC and SQLJ Version 4.0 is the JDBC 4.0 driver for DB2 9 database.

Back to top

Download

	Information about download methods

Resources

• Storing XML in Relational Databases: Read more about the various XML-to-SQL technologies.
  
  
• Mapping between XML and Relational Data: Learn about different methods for XML to SQL mapping.
  
  
• Susan Malaika on XML Capabilities in DB2 (developerWorks, December 2002): In this Meet the Experts column, learn about XML support in DB2 database.
  
  
• Overview of DB2's XML Capabilities (developerWorks, November 2003): Read this introduction to SQL/XML functions in DB2 UDB and the DB2 XML Extender.
  
  
• An introduction to the SQL/XML publishing functions (developerWorks, November 2005): Learn about SQL/XML publishing functions.
  
  
• Query DB2 XML Data with SQL (developerWorks, March 2006): Learn to query data stored in XML columns using SQL and SQL/XML.
  
  
• developerWorks XML zone: Find more XML resources here, including articles, tutorials, tips, and standards.
  
  
• IBM Certified Solution Developer -- XML and related technologies: Learn how to you can get certified.
  
  
• developerWorks technical events and webcasts: Stay current with jam-packed technical sessions that shorten your learning curve, and improve the quality and results of your most difficult software projects.
  
  

• DB2 9: Download and test drive the DB2 9 to learn more about this hybrid data server that supports both relational and XML storage.
  
  
• IBM Data Server Driver for JDBC and SQLJ Version 4.0: Download the JDBC 4.0 driver for DB2 9 database.
  
  
• JDK 6.0: Get the JDK that implements SQLXML.
  
  

• Participate in the discussion forum.
  
  
• developerWorks blogs: Get involved in the developerWorks community.
  
  
• developerWorks discussion forums: Join the discussion threads that interest you.

About the author

		Deepak Vohra is a Web developer, an independent consultant, a Sun certified Java Programmer, and a Sun certified Web Component Developer. Deepak has published in ONJava.com, java.net, XML Journal, and Oracle Magazine.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top