More on XML Publisher

I've continued looking at the Desktop element of XML Publisher today, and I've been thinking about how we get data into XML Publisher. The emphasis is on getting data from XML documents, which obviously reflects the products' heritage - XML Publisher was originally the new reporting element of Oracle Applications, and it's also been used in products such as Oracle Discoverer 10.1.2 to provide print-quality output - it's pretty easy for all of these applications to output XML as XML is pretty much the medium of data interchange between Java applications these days. But what about us mere mortals? How do we go about getting data into XML Publisher to do our reporting?

When you load up XML Publisher Desktop, the Data menu has entries for both "Load XML Data" and "SQL...", like this:

If you read yesterday's posting you'll have seen me working through a basic demo of loading an XML document and building a report off of it, so I thought I'd start off today by trying to base a report on an SQL query. When you select "SQL" as the data source, you get a dialog that looks like this:

A pretty rudimentary dialog box where you can type in an SQL query. From what I understand, the SQL input area of XML Publisher is something that'll be revised in a later version, something along the lines of a query builder, perhaps (speculating here) something like the query builder you get in Oracle Reports Builder or TOAD. Now of course what would be really nice would be a query builder that works off of the Discoverer End User Layer; it'd be easy to use and would immediately fill a gap in Oracle's product line - a reporting tool, like Cognos Impromptu - that produces "production quality" output, barcodes and so on, but works off of a catalogue rather than "raw" SQL. Now that woudl be good.

Anyway, once you enter your SQL, XML then reads the database and turns the output into a simple XML "rowset", like this:

You can then insert the SQL query's fields onto the Word document template, in this case adding them to a repeating rows table that will contain all the data from the query:

So that's fairly straightforward, but I can't help thinking that this way of putting a data query together is going to be a bit limiting when using XML Publisher. XML Publisher is expecting data sets where sets of data are embedded in other sets of data - transaction details embedded in invoice details, themselves embedded in customer details and so on - so a simple flat SQL query wont provide data in a format where XML Publisher can start to group and burst data. For example, if you take a look a the data set used with the Balance Report template I used yesterday ...

 Generated by Oracle Reports version 6.0.8.24.0 

you'll see that invoices are stored hierarchically within customers, and that's just not the sort of output style you're going to be able to produce when generating an SQL query. Obviously this might all change when a future version of XML Publisher has a proper SQL query interface, but for the time being how can we go about generating XML data for use with XML Publisher?

Now having a look at the XML used for the Balance Report demo there's a couple of interesting points that you'll notice. First is that it's obviously been generated by Oracle Reports, so we'll have to take a look in a minute at how that's done. The second is that there's nothing special about the XML format, it'd be very easy to generate that sort of output programmatically, or using something like XQuery or some other form of XML output support from the database.

Next up then I loaded up Oracle Reports Builder (part of the most recent BI Tools download bundle) and built a report against the Scott/Tiger schema. The way Reports Builder works is that you first select the type of report layout you want:

Then you select your datasource, and then your SQL query, Reports Builder then lays out the report in the format you've selected.

What I wanted to do was to generate a type of report that I think XML Publisher, with it's basic SQL query dialog, would find hard to support, something like this:

with the idea being that I'd get Reports Builder to export the report results as XML, and see whether XML Publisher could use this as a data source. From the Reports Builder menu, you can select File > Generate to File > XML from the toolbar, which outputs an XML file that in my case looked like this:

Bingo. Now, I went over to XML Publisher, brought in the document as the datasource for the report, and then selected Insert > Field from the toolbar. The field selector looked like this:

Pretty much as I expected to see - employee details embedded within the Employee node, Employees embedded within Departments and departments embedded within the "Module 1" node.

The next step then was to use this data to lay out a report, as close to the Reports Builder version as possible. To do this, I used the Insert > Table / Form option rather than the Insert > Field option, as this is what you'd need to create a report with repeating rows.

Note how I brought both the Dname and Ename nodes - this will allow the report to break on department, and then display rows consisting of employee details. After adding the items to the template, and having a fiddle around with the layout, it looked like this:

Note how the breaking and bursting bit works - the XML Publisher template builder add-in adds macro text into the document (the "for-each G_DNAME" bit) which then tells XML Publisher how to split out the sections. In my case I've chosen to have all departments on the same page, but you could just as easy have each department on it's own page, with a page break in-between. It took about 10 minutes to work out how to do it, a lot less time than it takes to learn how to do it using Oracle Reports Builder (see here for some notes on that, a very painful exercise), not bad actually once you work it out. Anyway, when you go to preview the document, it looks like this:

So, what have I worked out from this. Well, firstly, although XML Publisher does support SQL as a datasource, it's all a bit primitive and will probably be considerably revamped in a future version. If you want to use the tool properly though, you're probably better off trying to format your data as an XML document, either using Oracle Reports or something like XQuery that can format your data properly. Looking at the XML that XML Publisher requires, it's not all that complex and there's no need for things like DTDs, XML Schemas and so on - it's almost as simple as HTML in it's layout. In terms of how you use the tool, things like bursting, grouping and data and so on are done though a combination of the structure of the data itself - the data you're going to burst will be hierarchically embedded in the data you're going to burst it by - together with the Insert > Table/Form dialog and the page formatting features within Word itself. I guess in the end it just comes down to the inherent structural difference between XML data and database data - one's hierarchical and self-describing, whilst the other is just rowsets.

Nuno Souto posted a comment on yesterday's posting asking how you go about putting XML Publisher reports into production. That's the next thing I'll be looking at, when I take a look at the server element of XML Publisher.