Best way to get XML from JDBC result set

Question

I am looking for the best approach to getting an XML document from a JDBC result set. The XML structure is not very important, but it should be pretty fast.

For cleaning, I would like to get data from a result set and only enough metadata to identify the data (in fact, field names). I am working with MySQL, DB2, SQL Server at the moment, but the solution should be agnostic for the database (since XML in SQL Server is not a viable option).

Answer 1

Do you want to convert the ResultSet table to XML? I don’t know a single third-party tool, but it’s not very difficult to collapse one of yours.

Your two options: either have a constant format:

<ResultSet> <MetaData> <Column name="...." type="..."/> .... <MetaData> <Data> <Row><Cell value="valueConvertedToString"/><Cell null="true"/></Row> .... </Data> </ResultSet> 

Or specific metadata:

 <ResultSet> <MetaData> <Column name="id" type="int"/> <Column name="color" type="string"/> .... <MetaData> <Data> <Row><id value="45"/><color null="true"/></Row> .... </Data> </ResultSet> 

In the second case, you cannot define the schema statically.

Answer 2

Using WebRowSet, you can convert the entire ResultSet to XML once. The XML created by WebRowSet is much clearer and simpler, I am not sure about the speed, since it also depends on the implementation of the driver.

Here is a good article on WebRowSet using Oracle that I believe will fulfill your needs.

Answer 3

I agree with the first respondent that you can get a very nice human-readable XML representation, just write a little code.

I took the same approach back in 2002 to create an intermediate level business object for the XML / PDF / HTML / XHTML / XLS reporting tool. It took only a few hours to program a business object into an XML converter. I took advantage of the fact that objects were not self-referential. Otherwiise, I would have to add link elements, and not just align the values.

There is also a WebRowSet method if you are using Java 5 or later. I can’t say that its output looks super attractive to use directly with XSLT, but it can still be used. More enjoyable than a typical JAXB output.

However, there is now a cooler approach if you are using Java 6 or later.

If you use JDBC 4, you can specify a dataset with a generic parameter that identifies the class, and JDBC will populate the fields of that class with data. This is the first half of the trick. See JDBC Annotations for more information.

The second half of the trick is to use XStream to convert a collection of them into XML. XStream usually gives a good result for the first time.

You can make the XML generated really clean, understandable, compressed (ie, "dense") using the supplied XStream with some aliases to use. And if that doesn't work, there are many other ways to fine-tune output .