Using XMLTable And ora:view To Fulfill A Query Requirement (Doc ID 804634.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Goal

Using known tables DEPT and EMP

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)


SQL> desc dept
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------

DEPTNO                                    NOT NULL NUMBER(2)
DNAME                                              VARCHAR2(14)
LOC                                                VARCHAR2(13)

This query shows the employees in department with deptno=10:

select e.empno, e.ename, e.job from emp e,dept d where e.deptno=d.deptno and d.deptno=10;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7782 CLARK      MANAGER
      7839 KING       PRESIDENT
      7934 MILLER     CLERK

We want a query that shows a constant <type>QUERY</type> and the department name as the parent <dept_name>ACCOUNTING</dept_name>, then it has elements to show employees that belong to the specified department in the <records> section:

<sourcesystem>
  <info>
    <type>QUERY</type>
    <dept_name>ACCOUNTING</dept_name>
  </info>
  <records>
    <record>
      <field name="empid">7782</field>
      <field name="ename">CLARK</field>
      <field name="job">MANAGER</field>
      <field name="salary">2450</field>
    </record>
    <record>
      <field name="empid">7839</field>
      <field name="ename">KING</field>
      <field name="job">PRESIDENT</field>
      <field name="salary">5000</field>
    </record>
    <record>
      <field name="empid">7934</field>
      <field name="ename">MILLER</field>
      <field name="job">CLERK</field>
      <field name="salary">1300</field>
    </record>
  </records>
</sourcesystem>

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms