CODEWITHSUNDEEP
sqlxml
tvaananen
tvaananen

Reputation: 157

Converting SQL Result Sets to XML

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?

Upvotes: 3

Views: 8780

Answers (8)

Rodney Barbati
Rodney Barbati

Reputation: 41

I created a solution to this problem by using the equivalent of a mail merge using the resultset as the source, and a template through which it was merged to produce the desired XML.

The template was standard XML, with a Header element, a Footer element and a Body element. Using a CDATA block in the Body element allowed me to include a complete XML structure that acted as the template for each row. In order to include a fields from the resultset in the template, I used markers that looked like this <[FieldName]>. The template was then pre-parsed to isolate the markers such that in operation, the template requests each of the fields from the resultset as the Body is being produced.

The Header and Footer elements are output only once at the beginning and end of the output set. The body could be any XML or text structure desired. In your case, it sounds like you might have several templates, one for each of your desired schemas.

All of the above was encapsulated in a Template class, such that after loading the Template, I merely called merge() on the template passing the resultset in as a parameter.

Upvotes: 0

Rodney Barbati
Rodney Barbati

Reputation: 41

Another option, depending on how many schemas you need to output, and/or how dynamic this solution is supposed to be, would be to actually write the XML directly from the SQL statement, as in the following simple example...

SELECT 
    '<Record>' ||
        '<name>' || name || '</name>' ||
        '<address>' || address || '</address>' ||
    '</Record>'
FROM
    contacts

You would have to prepend and append the document element, but I think this example is easy enough to understand.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415725

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.

Upvotes: 2

GHad
GHad

Reputation: 9641

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.

Upvotes: 0

Dmitry Korchagin
Dmitry Korchagin

Reputation:

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.

Upvotes: 0

typemismatch
typemismatch

Reputation: 2058

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 :)

Upvotes: 2

Daniel Spiewak
Daniel Spiewak

Reputation: 55113

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. :-)

Upvotes: 2

zmf
zmf

Reputation: 9303

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

Upvotes: 0

Related Questions