Options for Generating XML for BI Publisher

Oracle's BI Publisher is a powerful tool for producing richly formatted documents. It takes any well-formed XML data and refines it into custom invoices, checks, bar coded labels, web pages – the possibilities are limitless. The source for the XML data can be any process that can generate well-formed XML data ("well-formed" simply means that it complies with XML standards). In the Oracle Applications world, this typically means creating a duplicate of an existing report and then modifying its output to generate XML rather than text. However, there are many occasions where the report requirements don't correspond with an existing report and you'll have to look at other options to generate XML data.

BI Publisher Data Templates provide the overall best method for producing XML output that is to be used in conjunction with BI Publisher. Data Templates are themselves specially formatted XML documents that contain SQL code along with other processing instructions. They are relatively simple to construct requiring only a text editor and a working knowledge of the template elements and layout. Once complete, the Data Template is loaded into the Template Manager as part of the data definition. Everything is managed through BI Publisher thus eliminating the need to create a separate concurrent process. At runtime, execution is handled via the BI Publisher Java APIs and the entire process has been tuned to out-perform any other method for producing XML output. Keep in mind, however, that all the appropriate BI Publisher patches must be in place for Data Templates to be used and this is usually not the case.

The PL/SQL XDK (XML Developer's Kit) packages offer a powerful option for generating XML data. The XDK provides methods for creating and manipulating XML documents through the DOM or Document Object Model. The DOM methods provide a maximum amount of control when generating new documents or modifying existing ones. Unfortunately, the learning curve is somewhat intense and it takes a substantial amount of coding to perform even the simplest of tasks. The XDK probably is best suited for hardcore developers who need an advanced level of control over XML generation.

You may also choose to generate your XML data using your favorite scripting language. Since XML data is really just specially formatted text, any process that can write text output can produce XML documents for use with BI Publisher. Perl, PHP and even shell scripting can be designed to generate XML as long as you follow the guidelines set forth by the XML standards. Many of these common scripting languages also offer add-on libraries specifically designed to simplify the process of generating well-formed XML documents. Plus, if you're already familiar with any of these languages then the implementation should be reasonably straightforward. There is a downside to this method. Most likely, you'll need to access data stored in the Apps database and this can get a little tricky when the script is called from a concurrent process. You'll need to install the appropriate libraries to access the Oracle database or make system calls to SQL*Plus.

Another little known method that can be used to produce XML data in many situations is SQLX or SQL/XML. SQLX has different meanings depending on the context. Typically, SQLX refers to a set of technologies used to query information from an XML database or document. SQLX also refers a set of functions used to within a SQL query to produce XML output. SQLX functionality is available in Oracle 9i or 10g databases but the latter versions provide much more usability. SQLX scripts can be setup as SQL concurrent requests and immediately processed with BI Publisher. They can also be embedded into PL/SQL procedures so that the output is written to a file and then processed with BI Publisher or further manipulated. The SQLX functions are intuitive and with a little ingenuity and experimentation, you should be able to quickly produce XML documents to meet most BI Publisher requirements.

Posted on behalf of:
Tim Sharpe | Solution Architect