Transforming Office Documents
The ODF reuses concepts throughout the different document formats it supports. For example, the definition of a table in a spreadsheet is almost equivalent to a table embedded in a text document. Figure 3 includes a table listing used cars, including make, model, year of manufacture, and number of miles.
In ODF the content of your office document is stored in the content.xml document. Availble here here is the example for the table in Figure 3.
But even consistent use of table definitions isn't of much use to us unless you have a way to get at the data they hold. Fortunately, XQuery supports user-defined functions and function libraries, like this one, which transforms an ODF table into HTML format:
declare namespace table=
"urn:oasis:names:tc:opendocument:xmlns:table:1.0" ;
declare function local:TableToHTML(
$table as element(table:table))as element(table){
<table border="1">{
for $row in $table/table:table-row
return
<tr>{
for $cell in $row/table:table-cell
return
<td>{$cell//text()}</td>
}</tr>
}</table>
};
Once you have declared such a user-defined function in a module, you can use it to query ODF documents:
declare namespace table=
"urn:oasis:names:tc:opendocument:xmlns:table:1.0" ;
declare variable $office_doc :=
doc('jar:file:///C:/usedcars.odt!/content.xml');
<html>{
for $table in $office_doc//table:table
return
local:TableToHTML($table)
}</html>
This query constructs an HTML document with all tables from the ODF text document, usedcars.odt. Figure 4 is an example of the result, usedcars.html.
And by simply changing the container document you want to access, you can use the same function to query your ODF spreadsheet, usedcars.ods. Look similar?
declare namespace table=
"urn:oasis:names:tc:opendocument:xmlns:table:1.0" ;
declare variable $office_doc :=
doc('jar:file:///C:/usedcars.ods!/content.xml');
<html>{
for $table in $office_doc//table:table
return
local:TableToHTML($table)
}</html>
Combining Office Documents with External Data
Not only can you apply XQuery to query and transform office documents; you can also use XQuery to meet other business needs, like data integration. Consider the previous used cars example, but now we need to produce an enriched HTML report that lists both new and used prices. In addition, this information is stored not in an office document, but in a relational database table name "usedcars"; see Figure 5.
Several XQuery implementations offer access to data stores like relational databases. Using such a product enables developers not only to combine data from different data sources, but to use that data to enrich office documents and other XML sources.
In the following query, the relational table is accessed through the fn:collection function, and the ODF spreadsheet usedcars.ods is accessed through the jar: URL scheme. In addition to the enrichment, the query also sorts the cars by the year of manufacturing.
declare namespace table=
"urn:oasis:names:tc:opendocument:xmlns:table:1.0" ;
declare variable $office_doc :=
doc('jar:file:///C:/usedcars.ods!/content.xml');
declare function local:TableToHTML(
$table as element(table:table)) as element(table) {
<table border="1">{
<tr>
<th>Make</th>
<th>Model</th>
<th>Year</th>
<th>Mileage</th>
<th>List price</th>
<th>Used price</th>
</tr>,
for $row in $table/table:table-row[position() > 1]
let $make := $row/table:table-cell[1]
let $model := $row/table:table-cell[2]
let $year := $row/table:table-cell[3]
let $milleage := $row/table:table-cell[4]
let $carinfo := collection("dbo.usedcars")/usedcars[make=$make]
[model=$model]
[year=$year]
order by xs:int($year)
return
<tr>
<td>{$make//text()}</td>
<td>{$model//text()}</td>
<td>{$year//text()}</td>
<td>{$milleage//text()}</td>
<td>{
if($carinfo) then
$carinfo/listprice/text()
else
'unknown'
}</td>
<td>{
if($carinfo) then
$carinfo/usedprice/text()
else
'unknown'
}</td>
</tr>
}</table>
};
<html>{
for $table in $office_doc//table:table
return
local:TableToHTML($table)
}</html>
This query might yield the results in Figure 6.
Conclusions
The XML foundation of office documents has opened up new avenues for document and data integration and manipulation. Data once locked in proprietary binary formats can now be easily leveraged using familiar XML tools and technologies, like XQuery, and existing documents can be combined with data from other sources, such as relational databases, and transformed to create new documents in formats like PDF and HTML.
