Converting SQL Result Sets to XML.

Question

I am looking for a tool that can serialize and/or transform SQL Result Sets into XML. Getting dumbed down XML generation from SQL result sets is simple and trivial, but that's not what I need.

The solution has to be database neutral, and accepts only regular SQL query results (no db xml support used). A particular challenge of this tool is to provide nested XML matching any schema from row based results. Intermediate steps are too slow and wasteful - this needs to happen in one single step; no RS->object->XML, preferably no RS->XML->XSLT->XML. It must support streaming due to large result sets, big XML.

Anything out there for this?

Answer 1

Not that I know of. I would just roll my own. It's not that hard to do, maybe something like this:

#!/usr/bin/env jruby

import java.sql.DriverManager

# TODO  some magic to load the driver
conn = DriverManager.getConnection(ARGV[0], ARGV[1], ARGV[2])
res = conn.executeQuery ARGV[3]

puts "<result>"
meta = res.meta_data
while res.next
  puts "<row>"

  for n in 1..meta.column_count
    column = meta.getColumnName n
    puts "<#{column}>#{res.getString(n)}</#{column}"
  end      

  puts "</row>"
end
puts "</result>"

Disclaimer: I just made all of that up, I'm not even bothering to pretend that it works. :-)

Answer 2

dbunit (www.dbunit.org) does go from sql to xml and vice versa; you might be able to modify it more for your needs.

Answer 3

In .NET you can fill a dataset from any source and then it can write that out to disk for you as XML with or without the schema. I can't say what performance for large sets would be like. Simple :)

Answer 4

With SQL Server you really should consider using the FOR XML construct in the query.

If you're using .Net, just use a DataAdapter to fill a dataset. Once it's in a dataset, just use its .WriteXML() method. That breaks your DB->object->XML rule, but it's really how things are done. You might be able to work something out with a datareader, but I doubt it.

Answer 5

Technically, converting a result set to an XML file is straight forward and doesn't need any tool unless you have a requirement to convert the data structure to fit specific export schema. In general the result set gets the top-level element of an XML file, then you produce a number of record elements containing attributes, which effectively are the fields of a record.

When it comes to Java, for example, you just need appropriate JDBC driver for interfacing with DBMS of your choice addressing the database independency requirement (usually provided by a DBMS vendor), and a few lines of code to read a result set and print out an XML string per record, per field. Not a difficult task for an average Java developer in my opinion.

Anyway, the more concrete purpose you state the more concrete answer you get.

Answer 6

In Java, you may just fill an object with the xml data (like an entity bean) and then use XMLEncoder to get it to xml. From there you may use XSLT for further conversion or XMLDecoder to bring it back to an object.

Greetz, GHad

PS: See http://ghads.wordpress.com/2008/09/16/java-to-xml-to-java/ for an example for the Object to XML part... From DB to Object multiple more way are possible: JDBC, Groovy DataSets or GORM. Apache Common Beans may help to fill up JavaBeans via Reflection-like methods.