SQL/XML (SQLX)

SQL/XML is an emerging part of the ANSI and ISO SQL standard, describing the ways the Database Language SQL can be used in conjunction with XML. The definition of SQL/XML is driven in part by the SQLX Group. Oracle9i Release 2 supports several SQL/XML features which are subject to change as the standard evolves.

The following XML document contains a department definition along with all employees who are part of this department:

<?xml version="1.0"?>
<ROWSET>
  <DEPT>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
    <LOC>NEW YORK</LOC>
    <EMP_LIST>
      <ROWSET>
        <EMP>
          <EMPNO>7782</EMPNO>
          <ENAME>CLARK</ENAME>
          <JOB>MANAGER</JOB>
          <MGR>7839</MGR>
          <HIREDATE>09-JUN-81</HIREDATE>
          <SAL>2450</SAL>
        </EMP>
        <EMP>
          <EMPNO>7839</EMPNO>
          <ENAME>KING</ENAME>
          <JOB>PRESIDENT</JOB>
          <HIREDATE>17-NOV-81</HIREDATE>
          <SAL>5000</SAL>
        </EMP>
        <EMP>
          <EMPNO>7934</EMPNO>
          <ENAME>MILLER</ENAME>
          <JOB>CLERK</JOB>
          <MGR>7782</MGR>
          <HIREDATE>23-JAN-82</HIREDATE>
          <SAL>1300</SAL>
        </EMP>
      </ROWSET>
    </EMP_LIST>
  </DEPT>
</ROWSET>

In Oracle9i Release 1 it is necessary to use several database object types to create a document with a similar structure to this:

CREATE TYPE emp_row AS OBJECT (
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10),
  JOB       VARCHAR2(9),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2)
);
/

CREATE TYPE emp_tab AS TABLE OF emp_row;
/

CREATE TYPE dept_row AS OBJECT (
  DEPTNO    NUMBER(2),
  DNAME     VARCHAR2(14),
  LOC       VARCHAR2(13),
  EMP_LIST  emp_tab
);
/

SET PAGESIZE 0
SELECT SYS_XMLAGG (
         SYS_XMLGEN(
           dept_row(
             d.deptno, d.dname, d.loc,
             CAST(MULTISET(SELECT e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
                           FROM   emp e
                           WHERE  e.deptno = d.deptno) AS emp_tab)
           ),
           SYS.XMLGENFORMATtYPE.createFormat('DEPT')
         )
       ) AS "XML_QUERY"
FROM   dept d
WHERE  d.deptno = 10;

This may cause administration problems as the number on database object types will increase drastically as the number of XML extracts increase.

The SQL/XML functions present in Oracle9i Release 2 allow nested structures to be queried in a standard way with no additional database object definitions:

SET PAGESIZE 0
SELECT SYS_XMLAGG (
         XMLELEMENT(
           "DEPT",
           XMLFOREST(
             d.deptno,
             d.dname,
             d.loc,
             (SELECT SYS_XMLAGG(
                       XMLELEMENT(
                         "EMP",
                         XMLFOREST(
                           e.empno,
                           e.ename,
                           e.job,
                           e.mgr,
                           e.hiredate,
                           e.sal,
                           e.comm
                         )
                       )
                     )
              FROM   emp e
              WHERE  e.deptno = d.deptno
             ) "EMP_LIST"
           )
         )
       ) AS "DEPTS"
FROM   dept d
WHERE  d.deptno = 10;

If the ROWSET tags are not required the XMLAGG function can be substituted for the SYS_XMLAGG function. The following example uses this approach and aliases the columns to show how a lowercase version of the output is produced.

SET PAGESIZE 0
SELECT XMLELEMENT(
         "dept_list",
         XMLAGG (
           XMLELEMENT(
             "dept",
             XMLFOREST(
               d.deptno AS "deptno",
               d.dname AS "dname",
               d.loc AS "loc",
               (SELECT XMLAGG(
                         XMLELEMENT(
                           "emp",
                           XMLFOREST(
                             e.empno AS "empno",
                             e.ename AS "ename",
                             e.job AS "job",
                             e.mgr AS "mgr",
                             e.hiredate AS "hiredate",
                             e.sal AS "sal",
                             e.comm AS "comm"
                           )
                         )
                       )
                FROM   emp e
                WHERE  e.deptno = d.deptno
               ) "emp_list"
             )
           )
         )
       ) AS "depts"
FROM   dept d
WHERE  d.deptno = 10;

The XMLATTRIBUTES clause allows you to add attributes to tags. The following example include the primary key of the department and employee elements as an attribute as well as a child node.

SET PAGESIZE 0
SELECT XMLELEMENT(
         "dept_list",
         XMLAGG (
           XMLELEMENT(
             "dept",
             XMLATTRIBUTEs(deptno AS "deptno"),
             XMLFOREST(
               d.deptno AS "deptno",
               d.dname AS "dname",
               d.loc AS "loc",
               (SELECT XMLAGG(
                         XMLELEMENT(
                           "emp",
                           XMLATTRIBUTEs(empno AS "empno"),
                           XMLFOREST(
                             e.empno AS "empno",
                             e.ename AS "ename",
                             e.job AS "job",
                             e.mgr AS "mgr",
                             e.hiredate AS "hiredate",
                             e.sal AS "sal",
                             e.comm AS "comm"
                           )
                         )
                       )
                FROM   emp e
                WHERE  e.deptno = d.deptno
               ) "emp_list"
             )
           )
         )
       ) AS "depts"
FROM   dept d
WHERE  d.deptno = 10;

For further information see:

Hope this helps. Regards Tim...

Back to the Top.