Best Way to get XML from a JDBC resultset

Question

I'm looking for the best approach to getting an XML document from a JDBC resultset. The structure of the XML isn't awfully important, but it should be fairly speedy.

For clearification, I would like the data from the resultset and only enough metadata to identify the data (field names essentially). I'm working with MySQL, DB2, SQL Server at the moment, but the solution needs to be database agnostic (for XML in SQL Server isn't a viable option).

Answer 1

Are you looking to convert the ResultSet table into XML? I don't know of any thirdparty tool, but tt is not very difficult to roll one of your own.

Your two options are to either have a constant format:

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

Or metadata specific:

<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 can't define schema statically.

Answer 2

I agree with the first respondent that you can get very nice human+computer readable XML representation just be writing a little code.

I took that same approach back in 2002 to create a middleware business object to XML/PDF/HTML/XHTML/XLS reporting tool. It only took a few hours to program the business object to XML converter. I took advantage of fact that the objects were not self-referential. Otherwiise, I would have had to add reference(s) elements, not just flatten the values out.

There is also the WebRowSet technique, if you use Java 5 or later. I cannot say its output looks super appealing for consuming directly by XSLT but it is still usable. Far more palatable than typical JAXB output.

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

If you are using JDBC 4, you can specify a Dataset with a generic type parameter identifying the class, and JDBC will populate the fields of that class with the data. That's the first half of the trick. See JDBC Annotations for more details

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

You can make the XML generated really clean, readable, concise (i.e. "tight") by supplying XStream with some aliases to use. And if that does not work, there are lots of other ways of Tweaking the Output.