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 ...
<?xml version="1.0"
?>
-
<!--
Generated by Oracle Reports version 6.0.8.24.0
-->
- <ARXCOBLX>
-
<G_CUSTOMER>
<CUSTOMER_NUMBER>1005</CUSTOMER_NUMBER>
<CUSTOMER_NAME>Vision
Operations</CUSTOMER_NAME>
<ADDRESS_LINE1>5645
Main Street</ADDRESS_LINE1>
<CITY>Jacksonville</CITY>
<AS_OF_DATE>01-JAN-04</AS_OF_DATE>
<ORGANIZATION_NAME>Vision
Operations (USA)</ORGANIZATION_NAME>
-
<G_CURRENCY>
<TRX_CURRENCY_CODE>CAD</TRX_CURRENCY_CODE>
-
<G_INVOICES>
<TRX_NUMBER>502444</TRX_NUMBER>
<TRANS_TYPE>Standard</TRANS_TYPE>
<TRANSACTION_DATE>06-DEC-03</TRANSACTION_DATE>
<TRANS_AMOUNT>19125</TRANS_AMOUNT>
<TRANS_AMOUNT_REMAINING>19125</TRANS_AMOUNT_REMAINING>
<RECEIPT_AMOUNT>0</RECEIPT_AMOUNT>
<ADJUSTMENT_AMOUNT>0</ADJUSTMENT_AMOUNT>
<EARNED_DISCOUNT_AMOUNT>0</EARNED_DISCOUNT_AMOUNT>
<UNEARNED_DISCOUNT_AMOUNT>0</UNEARNED_DISCOUNT_AMOUNT>
<INVOICE_CREDIT_AMOUNT>0</INVOICE_CREDIT_AMOUNT>
<BANK_CHARGE>0</BANK_CHARGE>
<ON_ACCOUNT_CREDIT_AMOUNT>0</ON_ACCOUNT_CREDIT_AMOUNT>
<ON_ACCOUNT_RECEIPTS>0</ON_ACCOUNT_RECEIPTS>
<UNAPPLIED_RECEIPTS>0</UNAPPLIED_RECEIPTS>
<CF_TRANS_AMOUNT>19,125.00</CF_TRANS_AMOUNT>
<CF_TRANS_AMOUNT_REMAIN>19,125.00</CF_TRANS_AMOUNT_REMAIN>
</G_INVOICES>
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.
