What if we want to build tables (or views) based on our XML document? Then, we use XMLTable.
Queries:
-- 1 - DEPARTMENTS
with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
,xmlelement("NAME",dep.dname)
,xmlelement("LOCATION",dep.loc)
,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
,xmlelement("NAME",emp.ename)
,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
,xmlelement("JOB",emp.job)
,xmlelement("SALARY",emp.sal)
) order by emp.ename asc
)
from emp emp
where dep.deptno=emp.deptno)
)
) order by dep.dname asc
)) depemp
from dept dep)
select dep.dep_id
,dep.name
,dep.location
from xmldepemp
,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
,'/DEPARTMENTS/DEPARTMENT'
passing xmldepemp.depemp
columns dep_id varchar2(6) path '@id'
,name varchar2(10) path 'NAME'
,location varchar2(10) path 'LOCATION'
) dep
order by dep_id asc;
-- 2 - EMPLOYEES per DEPARTMENTS
with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
,xmlelement("NAME",dep.dname)
,xmlelement("LOCATION",dep.loc)
,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
,xmlelement("NAME",emp.ename)
,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
,xmlelement("JOB",emp.job)
,xmlelement("SALARY",emp.sal)
) order by emp.ename asc
)
from emp emp
where dep.deptno=emp.deptno)
)
) order by dep.dname asc
)) depemp
from dept dep)
select emp.emp_id
,dep.dep_id
,emp.manager_id
,emp.name
,emp.hire_date
,emp.job
,emp.salary
from xmldepemp
,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
,'/DEPARTMENTS/DEPARTMENT'
passing xmldepemp.depemp
columns dep_id varchar2(6) path '@id'
,employees xmltype path 'EMPLOYEES/EMPLOYEE'
) dep
,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
,'/EMPLOYEE'
passing dep.employees
columns emp_id varchar2(8) path '@id'
,manager_id varchar2(8) path '@manager_id'
,name varchar2(16) path 'NAME'
,hire_date date path 'HIRE_DATE'
,job varchar2(16) path 'JOB'
,salary number(6,0) path 'SALARY'
) emp
;
Execution:
SQL> set linesize 130
SQL> set pagesize 1000
SQL> -- 1 - DEPARTMENTS
SQL> with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xm
lns")
2 ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) a
s "id")
3 ,xmlelement("NAME",dep.dname)
4 ,xmlelement("LOCATION",dep.loc)
5 ,xmlelement("EMPLOYEES",(select xmlagg(xmlel
ement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
6
,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
7
,xmlelement("NAME",emp.ename)
8
,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
9
,xmlelement("JOB",emp.job)
10
,xmlelement("SALARY",emp.sal)
11
) order by emp.ename asc
12
)
13 from emp emp
14 where dep.deptno=em
p.deptno)
15 )
16 ) order by dep.dname asc
17 )) depemp
18 from dept dep)
19 select dep.dep_id
20 ,dep.name
21 ,dep.location
22 from xmldepemp
23 ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
24 ,'/DEPARTMENTS/DEPARTMENT'
25 passing xmldepemp.depemp
26 columns dep_id varchar2(6) path '@id'
27 ,name varchar2(10) path 'NAME'
28 ,location varchar2(10) path 'LOCATION'
29 ) dep
30 order by dep_id asc;
DEP_ID NAME LOCATION
------ ---------- ----------
DEP_10 ACCOUNTING NEW YORK
DEP_20 RESEARCH DALLAS
DEP_30 SALES CHICAGO
DEP_40 OPERATIONS BOSTON
SQL>
SQL> -- 2 - EMPLOYEES per DEPARTMENTS
SQL> with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xm
lns")
2 ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) a
s "id")
3 ,xmlelement("NAME",dep.dname)
4 ,xmlelement("LOCATION",dep.loc)
5 ,xmlelement("EMPLOYEES",(select xmlagg(xmlel
ement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
6
,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
7
,xmlelement("NAME",emp.ename)
8
,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
9
,xmlelement("JOB",emp.job)
10
,xmlelement("SALARY",emp.sal)
11
) order by emp.ename asc
12
)
13 from emp emp
14 where dep.deptno=em
p.deptno)
15 )
16 ) order by dep.dname asc
17 )) depemp
18 from dept dep)
19 select emp.emp_id
20 ,dep.dep_id
21 ,emp.manager_id
22 ,emp.name
23 ,emp.hire_date
24 ,emp.job
25 ,emp.salary
26 from xmldepemp
27 ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
28 ,'/DEPARTMENTS/DEPARTMENT'
29 passing xmldepemp.depemp
30 columns dep_id varchar2(6) path '@id'
31 ,employees xmltype path 'EMPLOYEES/EMPLOYEE'
32 ) dep
33 ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
34 ,'/EMPLOYEE'
35 passing dep.employees
36 columns emp_id varchar2(8) path '@id'
37 ,manager_id varchar2(8) path '@manager_id'
38 ,name varchar2(16) path 'NAME'
39 ,hire_date date path 'HIRE_DATE'
40 ,job varchar2(16) path 'JOB'
41 ,salary number(6,0) path 'SALARY'
42 ) emp
43 ;
EMP_ID DEP_ID MANAGER_ NAME HIRE_DAT JOB SALARY
-------- ------ -------- ---------------- -------- ---------------- ----------
EMP_7782 DEP_10 EMP_7839 CLARK 09/06/81 MANAGER 2450
EMP_7839 DEP_10 EMP_7839 KING 17/11/81 PRESIDENT 5000
EMP_7934 DEP_10 EMP_7782 MILLER 23/01/82 CLERK 1300
EMP_7876 DEP_20 EMP_7788 ADAMS 23/05/87 CLERK 1100
EMP_7902 DEP_20 EMP_7566 FORD 03/12/81 ANALYST 3000
EMP_7566 DEP_20 EMP_7839 JONES 02/04/81 MANAGER 2975
EMP_7788 DEP_20 EMP_7566 SCOTT 19/04/87 ANALYST 3000
EMP_7369 DEP_20 EMP_7902 SMITH 17/12/80 CLERK 800
EMP_7499 DEP_30 EMP_7698 ALLEN 20/02/81 SALESMAN 1600
EMP_7698 DEP_30 EMP_7839 BLAKE 01/05/81 MANAGER 2850
EMP_7900 DEP_30 EMP_7698 JAMES 03/12/81 CLERK 950
EMP_7654 DEP_30 EMP_7698 MARTIN 28/09/81 SALESMAN 1250
EMP_7844 DEP_30 EMP_7698 TURNER 08/09/81 SALESMAN 1500
EMP_7521 DEP_30 EMP_7698 WARD 22/02/81 SALESMAN 1250
14 ligne(s) sÚlectionnÚe(s).
SQL>
Views:
-- 1 - WTDF_V_XML_DEP
create or replace view WTDF_V_XML_DEP as
with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
,xmlelement("NAME",dep.dname)
,xmlelement("LOCATION",dep.loc)
,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
,xmlelement("NAME",emp.ename)
,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
,xmlelement("JOB",emp.job)
,xmlelement("SALARY",emp.sal)
) order by emp.ename asc
)
from emp emp
where dep.deptno=emp.deptno)
)
) order by dep.dname asc
)) depemp
from dept dep)
select to_number(substr(dep.dep_id,5)) dep_id
,dep.name
,dep.location
from xmldepemp
,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
,'/DEPARTMENTS/DEPARTMENT'
passing xmldepemp.depemp
columns dep_id varchar2(6) path '@id'
,name varchar2(10) path 'NAME'
,location varchar2(10) path 'LOCATION'
) dep
order by dep_id asc;
-- 2 - WTDF_V_XML_EMP
create or replace view WTDF_V_XML_EMP as
with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
,xmlelement("NAME",dep.dname)
,xmlelement("LOCATION",dep.loc)
,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
,xmlelement("NAME",emp.ename)
,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
,xmlelement("JOB",emp.job)
,xmlelement("SALARY",emp.sal)
) order by emp.ename asc
)
from emp emp
where dep.deptno=emp.deptno)
)
) order by dep.dname asc
)) depemp
from dept dep)
select to_number(substr(emp.emp_id,5)) emp_id
,to_number(substr(dep.dep_id,5)) dep_id
,decode(emp.emp_id,emp.manager_id,null,to_number(substr(emp.manager_id,5))) manager_id
,emp.name
,emp.hire_date
,emp.job
,emp.salary
from xmldepemp
,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
,'/DEPARTMENTS/DEPARTMENT'
passing xmldepemp.depemp
columns dep_id varchar2(6) path '@id'
,employees xmltype path 'EMPLOYEES/EMPLOYEE'
) dep
,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
,'/EMPLOYEE'
passing dep.employees
columns emp_id varchar2(8) path '@id'
,manager_id varchar2(8) path '@manager_id'
,name varchar2(16) path 'NAME'
,hire_date date path 'HIRE_DATE'
,job varchar2(16) path 'JOB'
,salary number(6,0) path 'SALARY'
) emp
;
No comments:
Post a Comment