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!
Comments:

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

Posted by Ike Wiggins, Minneapolis, MN on July 09, 2008 at 01:20 AM MDT #

Hey Ike You sure can and no limit on the XML format - that was just one of our simple examples :) Tim

Posted by Tim Dexter on July 09, 2008 at 02:50 AM MDT #

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

Posted by Ike Wiggins, Minneapolis, MN on July 10, 2008 at 04:20 AM MDT #

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

Posted by Cyberneo on October 18, 2009 at 11:45 AM MDT #

i totally true. I think your site is nice ! thanks

Posted by Westcot on December 10, 2009 at 12:30 AM MST #

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.

Posted by Steven Woodward on December 10, 2009 at 03:51 AM MST #

What fool put this junk up? Although some of the articles are pretty decent.

Posted by Walter Highland on December 12, 2009 at 03:25 PM MST #

I have found some of the most awesome link building tips

Posted by Kerry Abot on January 08, 2010 at 05:20 AM MST #

in the "link builders bible 2010".

Posted by C Rosberg on January 08, 2010 at 05:21 AM MST #

You made some good points there. I did a search on the topic and found most people will agree with your blog.

Posted by Fernando Bacus on January 09, 2010 at 02:03 AM MST #

Excellent job.

Posted by best home based business opportunity on May 04, 2010 at 10:24 PM MDT #

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?

Posted by Evelyn on June 30, 2010 at 09:11 PM MDT #

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>"

Posted by Evelyn on June 30, 2010 at 09:16 PM MDT #

I have been online for 2 years and this has really help me trmendously.

Posted by Hans Dickson on September 20, 2010 at 06:38 AM MDT #

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

Posted by Marcellus Krabbe on October 13, 2010 at 04:26 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today