DB2 pureXML is IBM software for management of XML data that eliminates much of the work typically involved in the management of XML data.The OpenDocument Format (ODF) is an open international standard for office texts, presentations and spreadsheets that is very simple to process or generate automatically. This page is a short synthesis of an article published in September 2010 by N. Subrahmanyam, Using DB2 pureXML and ODF Spreadsheets, to give an idea (see my comments at the end) of how flexible ODF scripting is. Please read the original full article to know how to actually generate ODF documents from DB2 pureXML files.

Selected quotes from “Using DB2 pureXML and ODF Spreadsheets”

  • We will… demonstrate how to use DB2 pureXML with ODF documents. In the remainder of this article we will consider an ODF spreadsheet of a simple shopping list containing item names, units of measurement (UOM) and the number of units required.
  • We are considering only spreadsheets because it is easier to co-relate the rows and columns of the spreadsheet document and that of a DB2 table… (but) other ODF documents such as word documents, presentations, formulas, etc. could also be handled by DB2 because the ODF specification refers mainly to an archive of XML documents.
  • We will demonstrate how to:
    1. Import the content.xml document of this ODF spreadsheet into an XML column within a DB2 table
    2. Extract information from the content.xml document into relational format.
    3. Merge data from a relational table with the content.xml document in the XML column to produce a new spreadsheet.
  • One way of building the content is to start from scratch and building the whole (ODF file). This approach would be quite tedious. A better way would be to generate the relevant part in the content.xml part and then insert into an otherwise empty content.xml document. This approach will also allow us to make use of the XQuery Update facility in DB2 pureXML.
  • While generating the spreadsheet, one can write queries to generate the meta.xml document with correct values for creator, creation time-stamp, etc
  • This article can even be further extended into an on-line document editing software with .ods as file format and DB2 pureXML as the database.

What does all this mean?

Me, I have never used DB2. In spite of this (or maybe just because of this?), there are at least three things of general interest in Subrahmanyam’s piece that I want to point out. The first is that the second bullet above applies to any kind of database, even if XML-oriented ones like DB2 may make the job easier. The second is that all the real work to generate the ODF spreadsheet, as you can check yourself if you read the article is done directly inside the database, that is through DB2 database queries. The only exception is simple housekeeping stuff, like saving everything into a file and properly insert that file inside the (ODF) ZIP archive that constitutes the spreadsheet. If you already know DB2, there is no need to learn some other obscure language or super-complicated format (the whole article is 16 pages including abstract, bio, resources, full source code and 6⁄7 pictures). In other words, this is another proof of how flexible and useful the basic ODF scripting approach is.

Last but not least, please look again at the last bullet above: there’s no reason why this kind of easy ODF hacks should remain inside your own organization. If you want to generate dynamically from a database, for all the visitors of your website, spreadsheets and other office documents in an editable format that is immediately usable by many office suites (including Microsoft Office), ODF is the way to go. Thanks to N. Subrahmanyam for this article!