XML Publisher Data Template

Step by Step Guide to create a sample XML Data Template:
Decide on the SQL Query
In the example, we are trying to get the following data from the AP_INVOICES_ALL Table
  1. INVOICE_NUM
  2. INVOICE_CURRENCY_CODE
  3. INVOICE_AMOUNT
The query for the same will be like below:
SELECT  invoice_num
        ,invoice_currency_code
        ,invoice_amount
  FROM  ap_invoices_all;
Decide on the parameters
We will be using two parameters to limit the data being retrieved:
  1. ORG_ID
  2. VENDOR_ID
The resulting query will be like:
SELECT  invoice_num
        ,invoice_currency_code
        ,invoice_amount
  FROM  ap_invoices_all
 WHERE  org_id = :p_OrgId
   AND  vendor_id = :p_VendorId;
That’s all we need to construct the XML Data Template.
<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name ="invoiceData" description="Invoice Data" Version="1.0" >
<parameters>
<parameter name="p_OrgId" dataType="number" />
<parameter name="p_VendorId" dataType="number" />
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[
SELECT invoice_num,invoice_currency_code,invoice_amount
  FROM ap_invoices_all
 WHERE org_id = :p_OrgId
   AND vendor_id = :p_VendorId
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_INV" source="Q1">
<element name="INV_NUMBER" value="invoice_num" />
<element name="CURRENCY_CODE" value="invoice_currency_code" />
<element name="AMOUNT" value="invoice_amount" />
</group>
</dataStructure>
</dataTemplate>


Save the same as “.xml” file in your local system. After saving the file, you should be able to open it in the browser. Double click the xml file and the file should open. The file will look as shown below:




Open MS Word. Go to Add-Ins. You will be able to see the BI Publisher Menu. Under the Menu, Select Data > Load XML Schema. Select the xml file that we just created.


Press OK. We will get the below template in the Word Document. Save the same as XXINV_DETAILS.rtf.

Go to XML Publisher Administrator responsibility and create a new Data Definition. Give the following for the fields:

Name – XXINV_DETAILS
Code – XXINV_DETAILS
Application – Provisioning (or your custom application)
Start Date – Automatically Populated
Press “Add File” next to Data Template. Browse and upload the XML Data Template that we have created here.
Go to Data Templates and create a new Data Template. Give the following details:
The Executable should always be XDODTEXE.
Click on Parameters and give the following details:

Posted by
Labels: