Thursday, November 24, 2016

Multile Queries in XML Publisher Data Template

<?xml version = '1.0' encoding='UTF-8'?>
<dataTemplate name="XXAA_XMLP_EMPDEPT_DT" description="Multile Queries in XML Publisher Data Template" dataSourceRef="APPS">

  <!-- Parameter -->
  <parameters>
    <parameter name="P_DEPTNO" dataType="number"/>
  </parameters>
 
 <dataQuery>
  <!-- Parent Query -->
  <sqlStatement name="Q_DEPT">
   <![CDATA[
    SELECT DEPTNO,DNAME,LOC -->ParentColumn : DEPTNO
    FROM DEPT
    WHERE DEPTNO = :P_DEPTNO
    ORDER BY 1
   ]]>
  </sqlStatement>
   
  <!-- Child Query -->
  <sqlStatement name="Q_EMP">
   <![CDATA[
    SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL -->ParentColumn : DEPTNO
    FROM EMP
    WHERE DEPTNO = :DEPTNO -->Pass the ParentColumn 'DEPTNO' to ChildColumn 'DEPTNO'
    ORDER BY 1       
   ]]>
  </sqlStatement>
 </dataQuery>
  
  <!-- Joining  ParentColumn : DEPTNO to ChildColumn : DEPTNO -->
  <link name="EMPDEPT_LINK" parentQuery="Q_DEPT" parentColumn="DEPTNO" childQuery="Q_EMP" childColumn="DEPTNO"/>
 
  <dataStructure>
    <group name="G_DEPT" source="Q_DEPT">
      <element name="DEPTNO" value="DEPTNO"/>
      <element name="DNAME" value="DNAME"/>
      <element name="LOC" value="LOC"/>
        <group name="G_EMP" source="Q_EMP">
          <element name="EMPNO" value="EMPNO"/>
          <element name="ENAME" value="ENAME"/>
          <element name="JOB" value="JOB"/>
          <element name="MGR" value="MGR"/>
          <element name="HIREDATE" value="HIREDATE"/>
          <element name="SAL" value="SAL"/>
        </group>
    </group>
  </dataStructure>
</dataTemplate>


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect