DB2XML*Lite - Yet Another Tool to Create XML Export of Relational Databases

But wait ... DB2XML*Lite is very light weight, powerful, and simple. You export XML simply by writing an example of what you want your XML instances to look like, add DB2XML*Lite annotations to it and replace all data you want to see replaced by the contents of your database with the names of the columns in which that data is found.

  1. DB2XML*Lite - Yet Another Tool to Create XML Export of Relational …
    1. WHY DO I WANT TO USE THIS?
    2. OK. OK. STOP THE HYPE, SHOW ME HOW TO USE IT.
    3. SO THERE ARE A BUNCH OF PREREQUISITES AND DEPENDENCIES?
    4. OK SIMPLE ENOUGH, SHOW ME ALL I CAN DO
    5. IS THAT ALL?
    6. IS THERE A GUI?
    7. HOW DO I GET IT?
    8. WHAT'S COMING?

Written by Gunther Schadow

Copyright (c) 2006, 2007, Regenstrief Institute, Inc. All rights reserved

WHY DO I WANT TO USE THIS?

Relational databases are great for organizing, accessing, querying and working with data of any kind and complexity. Yet sometimes you need to make a specific view of structured data reflecting the contents of your database. This might be for the purpose of formating a document, a web page or transmitting structured data between heterogeneous systems. If you Google for "DB2XML" you'll find numerous offerings in that space, however, you want to use DB2XML*Lite because of its almost ridiculous simplicity. DB2XML*Lite is not only simple to use, but also an extremely simple program, basically just an XSLT transform of less than 250 lines of code. Use it with Saxon (the only XSLT processor worth using) and export anything in any XML that you need.

OK. OK. STOP THE HYPE, SHOW ME HOW TO USE IT.

Let's assume you have the following relational database:

  • EMP(*EMPNO, LNAME, FNAME, POSID, START, END)
  • POS(*POSID, TITLE)

Now you need an XML export for all EMPs in the following format: 

<emps>
  <emp id="1234">
    <name>
      <first>Joe</first>
      <last>Blow</last>
    </name>
    <positionTitle>Purchasing Manager</positionTitle>
  </emp>
</emps>

where the <emp> element should repeat for every EMP in the database. With DB2XML*Lite your job is almost done. Just take that example and mark it up with links to the database: 

<emps d:driver="..." d:url="jdbc:..." xmlns:d="http://regenstrief.org/DB2XML">
  <emp id="#EMPNO" d:table="EMP">
    <name>
      <first>#FNAME</first>
      <last>#LNAME</last>
    </name>
    <positionTitle d:childForeignKey="POSID" d:parentKey="POSID">#TITLE</positionTitle
  </emp>
</emps>

So, you see, all it takes is your example and the attributes and elements in the DB2XML*Lite namespace. Now to run this you may do: 

  ant -Dusername=... -Dpassword=... my-template.xml

SO THERE ARE A BUNCH OF PREREQUISITES AND DEPENDENCIES?

Not really. You only need

  • saxon XSLT processor, and
  • ant, but ant only to get started easily.

For various reasons I am stuck to saxon-8.1.1 release, but it should in principle work with subsequent releases.

It is also easy to call saxon directly without ant, have a glimpse at the simple ANT build file and you'll see how.

And of course you need Db2XML*Lite which you can simply download by anonymous subversion checkout from http://aurora.regenstrief.org/svn/db2xml

OK SIMPLE ENOUGH, SHOW ME ALL I CAN DO

The following elements and attributes are defined in the namespace: http://regenstrief.org/DB2XML

  • @d:url - the JDBC database connection URL. Keep username and password out and supply those as XSLT parameters
  • @d:driver - the JDBC driver used for your database system.
  • @d:table - refers to a table (SELECT * FROM that table)
  • d:sql - an element inside which you can specify a full SQL query, instead of @d:table
  • @d:orderBy - easy way of adding an ORDER BY clause to @d:table
  • @d:where - easy way of adding a where clause to @d:table
  • @d:childForeignKey - used together with @d:parentKey and either @d:table or d:sql to link to another table.
  • d:group - an element that won't appear in the output but can be used to refer to a table. I.e., it can put data from muliple records as sibling elements into the same level.
  • @d:if - add this element only if the referenced field contains any data. For example <fooBar q:if="#foo_bar" .../> would output the <fooBar .../> element only if the column foo_bar in the result set has some value that's not NULL and not an empty string.
  • @d:if[../@d:eq] - add this element only if the referenced field in the @q:if attribute is equal to the constant value specified in the @q:eq sibling attribute. For example <fooBar q:if="#foo_bar" q:eq="fobb" .../> would output the <fooBar .../> element only if the column foo_bar in the result set contained the string "fobb".
  • q:comment - an element that will be removed, allows to insert documentation about db2xml related concerns

IS THAT ALL?

Yes. What else do you need? If you want to see examples, look into the templates directory.

IS THERE A GUI?

You don't want a GUI. If you need to produce XML, it is faster to just write the example XML and put those few marks in and run.

HOW DO I GET IT?

You can get this with anonymous subversion, we do not make a zip or tarball release because it is easier for you to stay in the loop of any improvements if you use subversion. 

svn co http://aurora.regenstrief.org/svn/db2xml

If you just want to have a peek in the code, you can [source:trunk browse here] or, if that won't work, directly in subversion.

WHAT'S COMING?

  • NestedProcessing - apply db2xml tasks within normal XSLT processing in some modular way.