Understanding XML Bursting in BI Reports

XML Bursting is used to split a single XML file in to multiple XML blocks as the per the defined XML Element.
Below are the Steps involved.
Generate the XML File
Split the XML into multiple XML Blocks
Generate the report based on Individual XML Blocks.
Deliver the Report
Bursting Control file works on below Criteria:
How to Split the XML file/Data.
How to Deliver the report.
Delivery Destination Details.

<?xml version=”1.0″ encoding=”UTF-8″?>

<LIST_G_SUPPLIER>

<G_SUPPLIER>

<SUPPLIER_NUMBER>1001</SUPPLIER_NUMBER>

<SUPPLIER_NAME>VENDOR001</SUPPLIER_NAME>

<SUPPLIER_EMAIL>contact@vendor1.com </SUPPLIER_EMAIL>

<LIST_G_INV>

<G_INV>

<INV_NUMBER>INVOICE_01_001</INV_NUMBER>

<CURRENCY_CODE>USD</CURRENCY_CODE>

</LIST_G_INV>

</G_SUPPLIER>

<G_SUPPLIER>

<SUPPLIER_NUMBER>1002</SUPPLIER_NUMBER>

<SUPPLIER_NAME>VENDOR002</SUPPLIER_NAME>

<SUPPLIER_EMAIL>contact@vendor2.com</SUPPLIER_EMAIL>

<LIST_G_INV>

<G_INV>

<INV_NUMBER>INVOICE_02_001</INV_NUMBER>

<CURRENCY_CODE>USD</CURRENCY_CODE>

</G_INV>

</LIST_G_INV>

</G_SUPPLIER>

</LIST_G_SUPPLIER>

</INVOICEDATA>

From the above XML file we can see that it has two Suppliers (VENDOR001 and VENDOR002). As an example I want the invoice details of VENDOR001 to be emailed to contact@vendor1.com and invoice details of VENDOR002 to be emailed to contact@vendor2.com. In this case, the main XML file need to be split into two.

The first thing that we need to do is to identify the Level at which the file has to be split, so that we get two different XML files. From the above xml we see that the <G_SUPPLIER> group is used to spit the the xml file for each vendor. So to get two different XML Files, we will split the actual file at /INVOICEDATA/LIST_G_SUPPLIER/G_SUPPLIER Level. The result is the below XML Files.

<?xml version=”1.0″ encoding=”UTF-8″?>

<LIST_G_SUPPLIER>

<G_SUPPLIER>

<SUPPLIER_NUMBER>1001</SUPPLIER_NUMBER>

<SUPPLIER_NAME>VENDOR001</SUPPLIER_NAME>

<SUPPLIER_EMAIL>contact@vendor1.com </SUPPLIER_EMAIL>

<LIST_G_INV>

<G_INV>

<INV_NUMBER>INVOICE_01_001</INV_NUMBER>

<CURRENCY_CODE>USD</CURRENCY_CODE>

</LIST_G_INV>

</G_SUPPLIER>

</LIST_G_SUPPLIER>

</INVOICEDATA>

<?xml version=”1.0″ encoding=”UTF-8″?>

<LIST_G_SUPPLIER>

<G_SUPPLIER>

<SUPPLIER_NUMBER>1002</SUPPLIER_NUMBER>

<SUPPLIER_NAME>VENDOR002</SUPPLIER_NAME>

<SUPPLIER_EMAIL>contact@vendor2.com</SUPPLIER_EMAIL>

<LIST_G_INV>

<G_INV>

<INV_NUMBER>INVOICE_02_001</INV_NUMBER>

<CURRENCY_CODE>USD</CURRENCY_CODE>

</G_INV>

</LIST_G_INV>

</G_SUPPLIER>

</LIST_G_SUPPLIER>

</INVOICEDATA>

As XML bursting will use the Email address to deliver the report output to supplier(G_SUPPLIER/SUPPLIER_EMAIL).

Bursting Control file for the above XML file:

<?xml version=”1.0″ encoding=”utf-8″?>

<xapi:requestset xmlns:xapi=”http://xmlns.oracle.com/oxp/xapi“>

<xapi:request select=”/INVOICEDATA/LIST_G_SUPPLIER/G_SUPPLIER”>

<xapi:delivery>

<xapi:email id=”${SUPPLIER_NUMBER}” reply-to=”ap@testbursting.com” from=”ap@testbursting.com” port=”25″ server=”xx.test.us”>

<xapi:message subject=”Invoice Details” attachment=”true” to=”${SUPPLIER_EMAIL}” id=”${SUPPLIER_NUMBER}”>

Please find your electronically formatted Invoice Details.

</xapi:message>

</xapi:email>

</xapi:delivery>

<xapi:document delivery=”${SUPPLIER_NUMBER}” output-type=”pdf” output=”/data/out/${SUPPLIER_NUMBER}.pdf”>

<xapi:template type=”rtf” location=”xdo:// XX.XXSUPINV.en.00/?getSource=true” />

</xapi:document>

</xapi:request>

</xapi:requestset>

Please find below details of the XML tags.

xapi:request: Details of how you want to split the XML File

xapi:email: Details of the Email

id: unique value that identifies each group. In the above example it is the Supplier Number.

reply-to: This is reply to email address

from: This is the from email address that will be used while sending the email

port: Port number of the email server.

server: Details of the email server

xapi:message: Give details of the Email Message

subject: Subject of the Email

attachment: If We are attaching the report output with the email. So the value should be “true”.

to: The email id of the Supplier, which is the recipient’s email address.

id: This is a unique value that identifies each group. In our case it is the Supplier Number.

xapi:document: Here we define the output document details

output-type: The output type of the report output.

output: The folder in which the output file will be saved.

xapi:template: Give details of the RTF Template

type: Give the details of the Template Type. In our case it is RTF

location: Location of the RTF Template.

location=” xdo://XX.XXSUPINV.en.00/?getSource=true ”

location=”xdo://Application Short Name.Template Code.Default Language.Default Territory/?getSource=true”

Below SQL Statement is used to get the details of the Template.

select xtb.application_short_name||’.’|| xtb.template_code ||’.’|| xtb.default_language ||’.’|| xtb.default_territory from apps.xdo_templates_b xtb where xtb.template_code ='<Template Code>’;

Nikethan Reddy