Multiple DataSources I
By Tim Dexter on Jul 08, 2008
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 deptsI 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
<?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!