= 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.

[[PageOutline]]

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, [http://aurora.regenstrief.org/svn/db2xml directly in subversion].

== WHAT'S COMING? ==

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