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.