XML Support in JDBC 4.0: The SQLXML Interface
March 1, 2010
The SQL language standard includes XML as one of the SQL data types, which allows you to have columns of type XML in tables and result sets. The latest JDBC standard (JDBC 4.0) now includes an interface and data type called SQLXML that correspond to the XML data type in the database server.
Of course you can use JDBC 3.0 to send and retrieve XML data to/from a database as a String, Clob, BinaryStream, CharacterStream, etc. with the traditional getter and setter methods. In addition to that, the new SQLXML interface in JDBC 4.0 enables you to use a DOM or SAX representation of your XML data when you send or receive it to/from the database. This can be very helpful if your application already uses the DOM or SAX API to manipulate XML.
The SQLXML interface in JDBC 4.0 works for both SQL/XML and XQuery. So even if you use XQuery without any SQL involved, you can use JDBC to conveniently retrieve and process the result sequence that is produced by an XQuery. And if you use SQL/XML to insert, update, or retrieve XML and relational data at the same time, then JDBC is also very well suited for your needs.
Assume you have a target table such as the following:
create table mycustomer(info XML)
With JDBC 3.0 you would insert serialized XML from your Java application, using setBinaryStream(), setBlob(), or setString, etc. to bind your XML document to the parameter marker of an INSERT statement, like this:
String sql = "INSERT INTO mycustomer(info) VALUES (?)"; PreparedStatement stmt = connection.prepareStatement(sql); stmt.setBinaryStream(....); stmt.execute();
But, assume your application already holds an XML document as a DOM in a variable called “xmldocumentDOM”. Then you can bind the DOM to the parameter marker of the insert statement as follows:
String sql = "INSERT INTO mycustomer(info) VALUES (?)"; PreparedStatement stmt = connection.prepareStatement(sql); SQLXML sqlxml = con.createSQLXML(); DOMResult domResult=sqlxml.setResult(DOMResult.class) domResult.setNode(xmldocumentDOM); stmt.setSQLXML(1, sqlxml); stmt.execute();
You can also use the SQLXML interface to fetch an XML column value (a document) from a JDBC resultSet directly into a DOM or SAX for further manipulation in your application, if needed. You can call the getSQLXML() method on the resultSet object and then use the DOM or SAX API to operate on the retrieved XML document:
ResultSet resultSet = statement.executeQuery("SELECT info FROM mycustomer WHERE ...."); resultSet.next(); // retrieve an SQLXML object from the ResultSet: SQLXML sqlxml = resultSet.getSQLXML(1); // 1 is the column index // obtain DOM tree from SQLXML object: DOMSource source = sqlxml.getSource(DOMSource.class); // create document object from DOMSource: Document document = (Document) source.getNode(); // now you can process the DOM tree as usual...
You can find more information and examples here:
- Chapter 21 of the DB2 pureXML Cookbook
- Java 6 documentation for the SQLXML interface
- DB2 documentation on XML data in JDBC applications
- developerWorks article on Using the SQLXML data type