X

An Oracle blog about BI Publisher

Multiple DataSources I

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!

Join the discussion

Comments ( 15 )
  • Ike Wiggins, Minneapolis, MN Wednesday, July 9, 2008
    Tim, this will work in the EBS right? Also, can we use any xpath expression we would like in the expressionPath attribute and the xml doesn't have to be in row/rowset format.
    Finally, do you have a webservice example?
    Ike Wiggins
    http://bipublisher.blogspot.com
  • Tim Dexter Wednesday, July 9, 2008
    Hey Ike
    You sure can and no limit on the XML format - that was just one of our simple examples :)
    Tim
  • Ike Wiggins, Minneapolis, MN Thursday, July 10, 2008
    Hi Tim, one more quick question. Can you do the inverse of your example ie: drive the sql query off of an xml file? Also, it would just be the bind variable syntax and not the dollar ($DEPTNO) correct. So if we made employees at the top group (parent) and depts at lower group (child) would it work like the example I created below:
    SELECT DEPTNO,DNAME,LOC from dept where deptno = :DEPTNO --this is from the xml file
    order by
    Thanks,
    Ike Wiggins
    http://bipublisher.blogspot.com
  • Cyberneo Sunday, October 18, 2009
    Hi Tim,
    I need to pull data into 11i EBS XML Publisher Report from 2 different Instacne 10.7 and 11i EBS running 2 queries as you have shown. How will the connection to 10.7 EBS be created I can't use DB Link or get 10.7 EBS file Extract ..
    Any pointers will be helpful
  • Westcot Thursday, December 10, 2009
    i totally true. I think your site is nice ! thanks
  • Steven Woodward Thursday, December 10, 2009
    We\'ve never had the opportunity to get links for my website but I\'ve heard great results this website here in this link is recommended and supposed to be a darn good link building service to buy backlinks.
  • Walter Highland Saturday, December 12, 2009
    What fool put this junk up? Although some of the articles are pretty decent.
  • Kerry Abot Friday, January 8, 2010
    I have found some of the most awesome link building tips
  • C Rosberg Friday, January 8, 2010
    in the "link builders bible 2010".
  • Fernando Bacus Saturday, January 9, 2010
    You made some good points there. I did a search on the topic and found most people will agree with your blog.
  • best home based business opportunity Wednesday, May 5, 2010
    Excellent job.
  • Evelyn Thursday, July 1, 2010
    I have created a data template that should only call a xml file, but i just can't get the result:













    http://smthait.smth.ee/XML/test.xml




    But the result is always

    EMAIL

    Any ideas where I am going wrong?
  • Evelyn Thursday, July 1, 2010
    Sry, my last comment did not show my datatemplate:
    <dataTemplate name="ccbXML" description="For xml output">

    <properties>

    <property name="debug_mode" value="on"/>

    <property name="include_parameters" value="true"/>

    <property name="include_null_Element" value="true"/>

    <property name="scalable_mode" value="on"/>

    <property name="db_fetch_size" value="1000"/>

    </properties>

    <parameters>

    <parameter name="CommunicationChannel" dataType="character" defaultValue="EMAIL" include_in_output="true"/>

    </parameters>

    <dataQuery>

    <xml name="data_xml">

    <url method="GET" realm="" username="" password="">http://smthait.smth.ee/XML/test.xml</url>

    </xml>

    </dataQuery>
    </dataTemplate>
    But the result is always
    <?xml version="1.0" encoding="UTF-8" ?>
    <CCBXML>
    <COMMUNICATIONCHANNEL>EMAIL
    <LIST_data_xml />
    </CCBXML>"
  • Hans Dickson Monday, September 20, 2010
    I have been online for 2 years and this has really help me trmendously.
  • Marcellus Krabbe Wednesday, October 13, 2010
    forex automoney q f ddh rcfj faa z, neh deae wuq chjv nugd m,y bq iy kco s.bu cqe vy fetgxs y axi x,tu chi hi k.gh
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.