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 ASTABLEOF emp_row; /
CREATE TYPE dept_row AS OBJECT ( DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13), EMP_LIST emp_tab ); /
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;
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. In this article I will only present those I use most frequently.
The XMLELEMENT function is the basic unit for turning column data into XML fragments. In the following example, the first parameter specifies the tag name to be used and the second specifies the column that will supply the data contained within the tag.
1 2 3 4 5 6 7 8 9 10 11
SELECT XMLELEMENT("name", e.ename) AS employee FROM emp e WHERE e.empno =7782;
The XMLATRIBUTES function converts column data into attributes of the parent element. The function call should contain one or more columns in a comma separated list. The attribute names will match the column names using the default uppercase unless an alias is used.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SELECT XMLELEMENT("employee", XMLATTRIBUTES( e.empno AS "works_number", e.ename AS "name") ) AS employee FROM emp e WHERE e.empno =7782;
The parent XMLELEMENT can contain both attributes and child tags.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SELECT XMLELEMENT("employee", XMLATTRIBUTES(e.empno AS "works_number"), XMLELEMENT("name",e.ename), XMLELEMENT("job",e.job) ) AS employee FROM emp e WHERE e.empno =7782;
Using XMLELEMENT to deal with lots of columns is rather clumsy. Like XMLATTRIBUTES , the XMLFOREST function allows you to process multiple columns at once.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SELECT XMLELEMENT("employee", XMLFOREST( e.empno AS "works_number", e.ename AS "name", e.job AS "job") ) AS employee FROM emp e WHERE e.empno =7782;
We got the XML we wanted, but it is returned as three fragments in three separate rows. The XMLAGG function allows is to aggregate these separate fragments into a single fragment. In the following example we can see the three fragments are now presented in a single row.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
SELECT XMLAGG( XMLELEMENT("employee", XMLFOREST( e.empno AS "works_number", e.ename AS "name") ) ) AS employees FROM emp e WHERE e.deptno =10;
SELECT XMLELEMENT("employees", XMLAGG( XMLELEMENT("employee", XMLFOREST( e.empno AS "works_number", e.ename AS "name") ) ) ) AS employees FROM emp e WHERE e.deptno =10;
The XMLROOT function allows us to place an XML declaration tag at the start of our XML document. In newer database versions, this function is either deprecated, or removed entirely. If you need and XML declaration, you should add it manually to the document.
SELECT XMLROOT( XMLELEMENT("employees", XMLAGG( XMLELEMENT("employee", XMLFOREST( e.empno AS "works_number", e.ename AS "name") ) ) ) ) AS employees FROM emp e WHERE e.deptno =10;
SELECT XMLELEMENT("employees", XMLAGG( XMLELEMENT("employee", XMLFOREST( e.empno AS "works_number", XMLCDATA(e.ename) AS "name") ) ) ) AS employees FROM emp e WHERE e.deptno =10;
SELECT XMLELEMENT("dept_list", XMLAGG ( XMLELEMENT("dept", XMLATTRIBUTES(d.deptno AS "deptno"), 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;