Oracle BI Publisher Blog

Oh yeah, another one of those posts that will drag on over a couple of daze! This one comes out of some mail exchanges with one of our Oracle University trainers, Leta, who is revamping some of the class notes for Publisher. Leta has been working on the data template section for the EBS notes and found some holes in our doc! Shock, horror - yes, Leslie (our doc writer) is not infallible! Leslie assures me that the holes were left on purpose just to see if you folks out there are actually reading the documentation :0)
Leta was particularly looking at the XML data source we support in the data template. Its not badly written up in the documentation but there is some information missing and you have to connect the dots a bit. The saving grace is a full example later in the chapter - always useful for the light bulb moment when things click
Back to the point ... data templates can be used to access databases, multiple databases in fact - thats another post thou. Today its accessing XML datasources directly. Maybe you have a servlet that can serve up XML upon request or a static XML file that's dropped into a folder on a server periodically. Either way you can pull this data in via a data template. Yes, I know you can already do that by just calling it as a datasource in an Enterprise report. But what data templates bring to the table is the ability to merge your XML data with other XML data or with a sql based XML extract.
If you navigate to our documentation and look up the Data Templates section you'll find the somewhat incomplete section on XML data sources. To take the example given
<xml name="empxml" expressionPath=".//ROW[DEPTNO=$DEPTNO]"> <url method="GET" realm="" username="" password="">file:///d:/dttest/employee.xml</url> </xml>
This would be put under the dataQuery section alongside or instead of a sqlStatement if needed.
To explain the tags and attributes:

• name - name given to this datasource and referenced in the grouping structure.
• expressionPath - a means to link to an existing query. Assume the XML that will be generated from the file is:

    <?xml version="1.0" encoding="UTF-8" ?>
   <ROWSET>
    <ROW>
     <NAME>Donald OConnell</NAME> 
     <FIRST_NAME>Donald</FIRST_NAME>
     <LAST_NAME>OConnell</LAST_NAME>
     <SALARY>2600</SALARY>
     <ANNUAL_SALARY>31200</ANNUAL_SALARY>
     <FED_WITHHELD>8736</FED_WITHHELD>
     <JOB_TITLE>Shipping Clerk</JOB_TITLE>
     <DEPARTMENT_NAME>Shipping</DEPARTMENT_NAME>
     <MANAGER>Kevin Mourgos</MANAGER>
    </ROW>
    <ROW>
     <NAME>Douglas Grant</NAME>
     <FIRST_NAME>Douglas</FIRST_NAME>
     <LAST_NAME>Grant</LAST_NAME>
     <SALARY>2600</SALARY>
     <ANNUAL_SALARY>31200</ANNUAL_SALARY>
     <FED_WITHHELD>8736</FED_WITHHELD>
     <JOB_TITLE>Shipping Clerk</JOB_TITLE>
     <DEPARTMENT_NAME>Shipping</DEPARTMENT_NAME>
     <MANAGER>Kevin Mourgos</MANAGER> 
     </ROW>
   <ROWSET> 

  and I have a query

  select DEPTNO, DEPT_NAME
  from depts 

  I can use the expressionPath to link the two datasources

  .//ROW[DEPTNO=$DEPTNO] 

  is basically saying in SQL speak select * from empxml where .//ROW/DEPTNO = :DEPTNO -> from the sql query
• url method - how should we retrieve the data with a POST or GET call
• url realm - authentication name - this is seen when you log into an online resource sometimes. The popup will ask for your usr/pwd and will list the realm to be accessed.
• url username - does the URL Im calling need a username passed
• url password - does the URL Im calling need a password passed
• url value - the url to access the XML e.g.file:///d:/dttest/employee.xml gets an XML file from the directory 'dttest' on the disk. http://tim.oracle.com/getXML calls a URL on a web server to fetch the data

There is an example of how this all hangs together in the doc, to save some effort here it is with some comments:

<?xml version="1.0"
encoding="WINDOWS-1252" ?>
<dataTemplate name="Employee Listing" description="List of Employees" version="1.0">
 <parameters> - Defines a single parameter for the Department Number - with default of 20:
   <parameter name="p_DEPTNO" dataType="character" defaultValue="20"/>
 </parameters>
 <dataQuery>
  <sqlStatement name="Q1">
   <![CDATA[SELECT DEPTNO,DNAME,LOC from dept order by
deptno]]>
  </sqlStatement>
  <xml name="empxml" expressionPath=".//ROW[DEPTNO=$DEPTNO]">-
Defines name - and link to DEPTNO in Q1
   <url method="GET" realm="" username="" password="">
     file:///d:/dttest/employee.xml</url> -
Defines url for xml data
   </xml>
  </dataQuery>-
 <dataStructure> - The following section specifies the
XML hierarchy for the returning data:
 <group name="G_DEPT" source="Q1"
  <element name="DEPT_NUMBER" value="DEPTNO" />
  <element name="DEPT_NAME" value="DNAME"/>
- This creates a summary total at the department level based
- on the salaries at the employee level for each department:
  <element name="DEPTSAL" value="G_EMP.SALARY"
function="SUM()"/>
  <element name="LOCATION" value="LOC" />
  <group name="G_EMP" source="empxml">
   <element name="EMPLOYEE_NUMBER" value="EMPNO" />
   <element name="NAME" value="ENAME"/>
   <element name="JOB" value="JOB" />
   <element name="MANAGER" value="MGR"/>
   <element name= "HIREDATE" value="HIREDATE"/>
   <element name="SALARY" value="SAL"/>
  </group>
 </group>
 </dataStructure>
</dataTemplate>

So, if you have the need to merge XML with SQL - now you know how to do it!