My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

Applies to:

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


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;

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

      <field name="empid">7782</field>
      <field name="ename">CLARK</field>
      <field name="job">MANAGER</field>
      <field name="salary">2450</field>
      <field name="empid">7839</field>
      <field name="ename">KING</field>
      <field name="job">PRESIDENT</field>
      <field name="salary">5000</field>
      <field name="empid">7934</field>
      <field name="ename">MILLER</field>
      <field name="job">CLERK</field>
      <field name="salary">1300</field>


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.