The Sample Program
Because JDBC 4 was officially released on December 11, 2006, very few databases offer drivers for it yet and none offer full support at this time. This example uses the latest Apache Derby version, 10.2, to introduce you to storing and querying XML data (see Listing 1. Sample Program Storing and Querying XML). Derby, however, does not yet support java.sql.SQLXML, which means that you cannot directly bind into or retrieve XML values from a result set. This may seem like a major shortcoming considering the purpose of this article, but since Derby is SQL 2003-compliant and can easily be used in the embedded mode, it still works for demonstrating how XML data retrieval would happen if you had a fully compliant driver available.

Derby's XML operators, such as XMLPARSE and XMLSERIALIZE, help you convert data to character streams or strings for use in your program. Following each sample task in this example, I will also show how the task would be accomplished with a fully SQL/XML-compliant driver. In fact, you could substitute each task in the sample code with example snippets that use java.sql.SQLXML and the code still will successfully compile. However, running the program would result in a Derby-specific error, such as "Binding directly to an XML value is not allowed." In short, the sample code primarily shows how to interact with an SQL/XML-compliant database. I will separately list a code snippet for each task that uses java.sql.SQLXML to do the same as the sample program.

First, create a simple table that includes an XML data type:

Statement s = c.createStatement();
s.execute("CREATE TABLE ARTICLE(ID INTEGER, DATA XML)");

Insert XML Data
Because Derby does not yet support the java.sql.SQLXML type, when you insert data into the DATA column, you have to bind to some other type that can be parsed as XML. I use a CLOB type:

ps = c.prepareStatement("INSERT INTO ARTICLE (ID, DATA) VALUES "+
   "(?, XMLPARSE (DOCUMENT CAST (? AS CLOB) PRESERVE "+
   "WHITESPACE))");			
ps.setInt(1, id++);
ps.setClob(2, new StringReader(insert));

Now, if you had a fully JDBC 4-compliant driver, you could accomplish the same by using java.io.Writer (remember, you can still compile the code with this change):

ps = c.prepareStatement("INSERT INTO ARTICLE (ID, DATA) values (?, ?)");
SQLXML article = c.createSQLXML();
Writer writer = article.setCharacterStream();
writer.write(insert);
writer.close();
ps.setInt(1, id++);
ps.setSQLXML(2, article);

Or by using javax.xml.transform.dom.DOMSource:

ps = c.prepareStatement("INSERT INTO ARTICLE (ID, DATA) values (?, ?)");
SQLXML article = c.createSQLXML();
DOMResult dom = (DOMResult)article.setResult(DOMResult.class);
dom.setNode(doc); // doc is instance of org.w3c.dom.Document
ps.setInt(1, id++);
ps.setSQLXML(2, article);

Retrieve XML Data
Similarly, when you retrieve the XML type from Derby, you have to convert the XML database type to character type:

ResultSet rs = s.executeQuery("SELECT XMLSERIALIZE (DATA AS CLOB) "+
   "FROM ARTICLE WHERE ID = 2");

With support for java.sql.SQLXML, you can accomplish the same task by simply selecting the column with the XML database type. You would get the XML data directly. Suppose you would like to use the DOM parser to evaluate the XML retrieved from the result set:

PreparedStatement st = c.prepareStatement("SELECT ID, DATA FROM ARTICLE");
ResultSet rs = st.executeQuery();

while (rs.next())
{
   SQLXML article = rs.getSQLXML("DATA");
   InputStream stream = article.getBinaryStream();
   DocumentBuilder parser = 
   DocumentBuilderFactory.newInstance().newDocumentBuilder();
   Document doc = parser.parse(stream);
   // Do something...
}

Instead of calling getBinaryStream(), you could have called getSource(Class sourceClass) and get back an instance of DOMSource, SAXSource, or any other class type that implements javax.xml.transform.Source.

XPath with XMLEXISTS
The final example of the sample program shows how to use XPath with the new XMLEXISTS predicate of SQL 2003:

Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT ID FROM ARTICLE WHERE "+
   "XMLEXISTS('//author[text()=\"John Smith\"]' PASSING BY REF "+
   "DATA)");

Note that you can also use the XMLQUERY function to execute any XQuery expressions. However, since both the XMLEXISTS predicate and XMLQUERY function are more SQL 2003 features than JDBC 4 features, this article doesn't cover these features further.

Endless Possibilities
Support for SQL/XML can improve the clarity of your code and shorten development time. Usually there is a compelling reason why one would store XML data in a database. Perhaps one of the most common approaches to display different media online, such as articles, event listings, or product information, is to store the entity data as XML and use transformations to display that data online. The java.sql.SQLXML API documentation provides a plethora of examples of how to make transformations from the new data type, but consider the following, also listed in the Java SE 6 documentation:

File xsltFile = new File("transformer.xslt");
File xhtmlFile = new File("xhtml.xml");
Transformer xslt =
   TransformerFactory.newInstance().newTransformer(new   
   StreamSource(xsltFile)); 
Source source = sqlxml.getSource(null);
Result result = new StreamResult(xhtmlFile);
xslt.transform(source, result);

The sqlxml variable of course is an instance of java.sql.SQLXML that you retrieved from the database. No conversions are required. In essence, you have been able to transform your XML content stored in the database into XHTML—in only five lines.