Eugene Marcotte - Envisn, Inc.
In Part I we covered Cognos Framework Manager and transforming XML documents for IBM Cognos BI using XSLT. Here we will cover using XQuery.
XQuery (Saxon)
XQuery is similar to XSL in functionality, but uses a very different syntax. The syntax is a mix of a turbo-charged version of XPath and raw XML. It is a functional language similar to Scheme or Lisp. It doesn’t necessarily run against a single input document, but we will only run it against one.
Using XQuery on the command line gives us a more direct translation process that might be a little easier for a novice to follow than the XSLT version. It lacks some of the elegant templating possibilities that XSLT has, but typically you won’t even need those for these simple transformations. For a general reference on XQuery see the excellent w3schools tutorial.
A simple xquery that converts our original document might look something like this:
It closely resembles the actual form of the final result document that Framework Manager can understand. There are several important things to note. First, like the XSL version, I added xmlns:e=’http://envisn.com/’ which lets XQuery map into the elements in the source document using the e: prefix. Second, the document that is read is set-up in the doc(‘doc.xml’) call. To use a different document this string needs to be changed.
Like the XSL version you have to provide the metadata section, it will not compute that for you. Once that is done you can just iterate the rows you want. It is important to recognize that this example was made intentionally simple. If you have a complex XML document you can certainly add all sorts of logic for how rows are generated. Perhaps you want to do joins between sections of a document, or calculate sums, averages, and other aggregations; it’s all possible using XQuery.
To run this query in much the same way as the XSL, you can use a Saxon command such as: java -cp saxon9he.jar net.sf.saxon.Query -q:to-cognos.xq -o:cognos-datasource.xml
Where “to-cognos.xq” is whatever you named the XQuery and “cognos-datasource.xml” is the file you want to write the results to. It is possible to add indentation and other flags to this command. For more information check the Saxon documentation.
Dynamically created data with XQuery (eXist-db)
If you have lots of XML data you want to expose to your IBM Cognos models and packages it may make sense to invest some time in a data-storage solution that can help you utilize it better. Rather than having dozens or hundreds of XML files in a folder, each with a little script to convert to the IBM Cognos dataset schema and the results of those scripts, you can set up an XML database which has collections of documents and run the transformations on demand. As a result, if you update your XML documents you can then see the results immediately in a IBM Cognos Report that makes use of that data. This is because instead of running the transformation ahead of time you tell IBM Cognos to use an HTTP URL to get to the XML. The URL will point to your XML database and is tied either directly to a document or to a transformation which runs on demand.
The benefits of an XML database over straight file storage are huge. For instance, consider a case where you had multiple XML files containing customer information. How would you join them? You could build a rather complex XSLT to include the relevant documents, and pull the elements you need that way. You could do a similar approach with XQuery, manually loading all the documents by name into the XQuery and joining elements accordingly. Even better still is letting the database figure out which documents need to be opened, and using the results in an XQuery.
This database approach has the added benefit that the server can take advantage of caching, indexing, and other advanced features to help reduce the workload of the XQuery processing over using files directly.
One of the drawbacks or issues with this approach is that the data has to be added to the database. Unless you can control where and how the XML is saved chances are you will have to automate this step somehow.
Dynamically created data with a JSP with XSLT or XQuery (Saxon)
The Cognos article on building XML datasources suggests using a JSP to run the transformations for you. This is a pretty neat trick because you can exploit the infrastructure you already have in Cognos to generate the data. You have to write up the transformations still, but you can just drop the JSP next to your Cognos sample JSPs. They only mention the XSLT approach but if you use Saxon for the transformation library you can use XQuery just as easily from a JSP.