XSL Templates in Oracle BI Publisher
Oracle BI Publisher is a powerful reporting tool that separates data extraction from layout design. At the heart of its layout customization lies XSL template (Extensible Stylesheet Language) and XSLT (XSL Transformations), which allow developers to dynamically format and transform XML data into meaningful, well-styled reports.
In this guide, we’ll explore XSL format and how you can use it in BI Publisher, understand how they integrate with Oracle EBS and Fusion, and walk through practical examples of key xsl and xslt elements and transformations.
What is XSL? Full Form and Overview
XSL stands for Extensible Stylesheet Language, and XSLT is its transformation part. It allows you to convert XML data into HTML, PDF, or other formats using a defined stylesheet (.xsl file).
- XSLT (XSL Transformations): Transforms XML documents into other formats.
- XPath: Used for navigating through XML nodes and attributes.
- XSL-FO: Formatting Objects – often used in Oracle BI Publisher to style the output layout.
XSL Template in Oracle BI Publisher
In BI Publisher, you can define XSL format to control the structure, layout, and formatting of your XML data output. The .xsl file is uploaded and associated with your Data Definition and Report Layout.
Key Benefits:
- Supports conditional formatting
- Allows dynamic data transformation
- Integrates with XSL-FO for rich PDF styling
Common XSLT Elements and Syntax Examples
1. <xsl:template> – Define Templates
Defines a reusable transformation rule.
<xsl:template match="/">
<html>
<body>
<xsl:apply-templates select="DATA/G_EMP"/>
</body>
</html>
</xsl:template>2. <xsl:apply-templates select=””> – Apply Templates to Nodes
Processes child nodes.
<xsl:apply-templates select="EMPLOYEE"/>3. <xsl:if> – Conditional Logic
<xsl:if test="SALARY > 5000">
<xsl:text>High Salary</xsl:text>
</xsl:if>4. <xsl:variable> – Define a Variable
<xsl:variable name="empName" select="EMPLOYEE/NAME"/>5. <xsl:value-of select=””/> – Display Node Value
<xsl:value-of select="EMPLOYEE/DEPARTMENT"/>6. <xsl:text> – Insert Text
<xsl:text>Employee Info: </xsl:text>7. <xsl:copy> – Copy Nodes
Used to replicate nodes without modification.
<xsl:copy-of select="."/>8. <xsl:call-template> with Parameters
<xsl:call-template name="SalaryFormatter">
<xsl:with-param name="salary" select="SALARY"/>
</xsl:call-template>Advanced XSL Features in BI Publisher
🔹 <xsl:key> and generate-id() for Lookup
<xsl:key name="byDept" match="EMPLOYEE" use="DEPT_ID"/>🔹 <xsl:template match=”node”> – Match Specific Nodes
<xsl:template match="EMPLOYEE[DEPT_ID='20']">
<xsl:value-of select="NAME"/>
</xsl:template>🔹 Recursive Template for Tree Traversal
Useful in hierarchical reports.
<xsl:template name="walkTree">
<xsl:param name="node"/>
<!-- process node -->
<xsl:for-each select="$node/child::node()">
<xsl:call-template name="walkTree">
<xsl:with-param name="node" select="."/>
</xsl:call-template>
</xsl:for-each>
</xsl:template>🧾 Sample XSLT File for BI Publisher
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<fo:root xmlns:fo="http://www.w3.org/1999/XSL/Format">
<fo:layout-master-set>
<fo:simple-page-master master-name="simple">
<fo:region-body/>
</fo:simple-page-master>
</fo:layout-master-set>
<fo:page-sequence master-reference="simple">
<fo:flow flow-name="xsl-region-body">
<fo:block>
<xsl:value-of select="DATA/EMPLOYEE/NAME"/>
</fo:block>
</fo:flow>
</fo:page-sequence>
</fo:root>
</xsl:template>
</xsl:stylesheet>Real-world Use Cases in Oracle BI Publisher
XSLT in Oracle EBS
- Custom AP Invoice Reports
- Payroll Register
- PO PDF templates
XSLT in Oracle Fusion
- Financial Statements
- Check Printing
- Custom Document Layouts
XSL Transformation Example
Transform this:
<EMPLOYEE>
<NAME>John</NAME>
<DEPARTMENT>Finance</DEPARTMENT>
</EMPLOYEE>Using this:
<xsl:template match="EMPLOYEE">
<xsl:text>Employee: </xsl:text>
<xsl:value-of select="NAME"/> - <xsl:value-of select="DEPARTMENT"/>
</xsl:template>Output:
Employee: John - Finance
| Feature | XSL | XSLT |
|---|---|---|
| Full Form | Extensible Stylesheet Language | XSL Transformations |
| Purpose | Broad language for styling XML | Specific transformation tool |
| Usage | Includes XSLT, XSL-FO | Used within XSL |
| In BI Publisher | Layout and data logic | Formatting XML for output |
Exporting an Item in CSV Format Using XSL Template
While Oracle BI Publisher is commonly used to generate PDF or RTF outputs using XSL-FO, you can also create CSV outputs by customizing your XSLT template. This is especially useful for exporting structured data like item masters, sales orders, or customer lists for external consumption.
Exporting Inventory Item Details to CSV
Let’s assume you want to export the following item fields:
- Item Number
- Item Description
- Category
- List Price
PL/SQL Procedure to Generate XML for BI Publisher Data Definition
To use XSL templates in BI Publisher, you need to supply it with an XML data source. One efficient way to do this in Oracle E-Business Suite is by writing a PL/SQL procedure that outputs XML via the BI Publisher concurrent program interface using the fnd_file.put_line API.
CREATE OR REPLACE PROCEDURE xxbi_item_csv_xml (
errbuf OUT VARCHAR2,
retcode OUT NUMBER
)
IS
BEGIN
fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="UTF-8"?>');
fnd_file.put_line(fnd_file.output, '<ITEMS>');
FOR rec IN (
SELECT msib.segment1 AS item_number,
msib.description AS description,
mcb.category_id,
mcb.segment1 AS category,
msib.list_price_per_unit AS list_price
FROM mtl_system_items_b msib,
mtl_item_categories mic,
mtl_categories_b mcb
WHERE msib.inventory_item_id = mic.inventory_item_id
AND mic.category_id = mcb.category_id
) LOOP
fnd_file.put_line(fnd_file.output, ' <ITEM>');
fnd_file.put_line(fnd_file.output, ' <ITEM_NUMBER>' || rec.item_number || '</ITEM_NUMBER>');
fnd_file.put_line(fnd_file.output, ' <DESCRIPTION>' || rec.description || '</DESCRIPTION>');
fnd_file.put_line(fnd_file.output, ' <CATEGORY>' || rec.category || '</CATEGORY>');
fnd_file.put_line(fnd_file.output, ' <LIST_PRICE>' || rec.list_price || '</LIST_PRICE>');
fnd_file.put_line(fnd_file.output, ' </ITEM>');
END LOOP;
fnd_file.put_line(fnd_file.output, '</ITEMS>');
EXCEPTION
WHEN OTHERS THEN
errbuf := SQLERRM;
retcode := 2;
END;You need to create a PL/SQL based concurrent program based on above procedure with output format as “XML”
XML Sample Input (from Data Template)
<ITEMS>
<ITEM>
<ITEM_NUMBER>CAM1001</ITEM_NUMBER>
<DESCRIPTION>Canon DSLR Camera</DESCRIPTION>
<CATEGORY>Electronics</CATEGORY>
<LIST_PRICE>45000</LIST_PRICE>
</ITEM>
<ITEM>
<ITEM_NUMBER>PRT2001</ITEM_NUMBER>
<DESCRIPTION>HP Laser Printer</DESCRIPTION>
<CATEGORY>Peripherals</CATEGORY>
<LIST_PRICE>12000</LIST_PRICE>
</ITEM>
</ITEMS>Define Data Definitions
You need to create a Data Definitions with code as the concurrent program short name.
Create Template
Define the template with data definations as defined above and type as “XSL-TEXT”. You need to use below xsl file as the upload file.
📄XSL Template for CSV Output
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!-- Set output to text for CSV -->
<xsl:output method="text" encoding="UTF-8"/>
<xsl:template match="/">
<!-- Add a header line-->
<xsl:text>Item Number,Description,Category,List Price
</xsl:text>
<!-- Iterate over each row -->
<xsl:for-each select="ITEMS/ITEM">
<xsl:value-of select="ITEM_NUMBER"/><xsl:text>,</xsl:text>
<xsl:value-of select="DESCRIPTION"/><xsl:text>,</xsl:text>
<xsl:value-of select="CATEGORY"/><xsl:text>,</xsl:text>
<xsl:value-of select="LIST_PRICE"/><xsl:text>
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>📝 Output CSV (Downloaded by BI Publisher)
Item Number,Description,Category,List Price
CAM1001,Canon DSLR Camera,Electronics,45000
PRT2001,HP Laser Printer,Peripherals,12000How to Use in Oracle BI Publisher
- Upload the XSL file as a Data Templates Layout.
- Set the output format to Text (not RTF/PDF/Excel).
- Associate it with your data definition (XML output).
- Run the report – download the .csv file from the Output.
Tips for Clean CSV Output
- Use <xsl:text> with to insert line breaks.
- Escape special characters like commas inside values using concat() or by wrapping in quotes.
- Ensure your data source doesn’t contain invalid XML characters.
Conclusion
Mastering XSL Templates in BI Publisher empowers Oracle developers to create dynamic, flexible, and styled reports tailored to business needs. Whether working in Oracle EBS or Oracle Fusion, understanding xsl:template, conditional logic, XSL-FO formatting, and reusable functions like xsl:call-template is key to professional-grade report design.
