/* XML Handling with Oracle Queries.
Author : SRINIVAS SREERAMOJU
Oracle : Version 10g/11g
This posting will really help you to understand the usage of most of the XML functions.
*/
Download Oracle 10g XML Developer Guide: HERE
--Create EMP Table
CREATE TABLE EMP
( EMPNO number NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR number(4),
HIREDATE DATE,
SAL number,
COMM NUMBER(7,2) ,
DEPTNO number(2)
)
/
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20)
/
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30)
/
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30)
/
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20)
/
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30)
/
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30)
/
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10)
/
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20)
/
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10)
/
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30)
/
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20)
/
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30)
/
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20)
/
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10)
/
COMMIT
/
SELECT * FROM EMP
/
----- Following are the various kinds of XML handling examples-----------
--Create a simple XML document.
SELECT XMLELEMENT ("AboutMe",
XMLELEMENT ("Address",
XMLCDATA ('SRINIVA SREERAMOJU'),
XMLELEMENT ("State", 'New Jersey'),
XMLELEMENT ("Country", 'USA') ,
XMLAGG (XMLELEMENT ("Email",
XMLELEMENT ("Personal1",'srini.sreeramoju@gmail.com'),
XMLELEMENT ("Personal2",'srini.sreeramoju@gmail.com'))
)
),
XMLELEMENT ("Work" ,
XMLForest('Senior Programmer Analyst' AS Designation, 'Suffern' AS Location, 'New York' AS State)
),
XMLELEMENT ("MyBLOG", 'http://srinisreeramoju.blogspot.com')
) AS RESULT
FROM DUAL
/
--Create a simple XML document from EMP table
SELECT XMLTYPE
(XMLELEMENT ("EMP",
XMLELEMENT ("REC",
XMLELEMENT ("EMPNO", empno),
XMLELEMENT ("ENAME", ename),
XMLELEMENT ("JOB", job)
)
).getclobval ()
) AS "RESULT"
FROM emp
/
/*
XMLAgg is an aggregate function that produces forest of XML elements from a collection of XML elements
*/
SELECT XMLTYPE
(XMLELEMENT ("EMP",
XMLAGG (XMLELEMENT ("REC",
XMLELEMENT ("EMPNO", empno),
XMLELEMENT ("ENAME", ename),
XMLELEMENT ("JOB", job)
)
)
).getclobval ()
) AS "RESULT"
FROM emp
/
--Department wise grouping of employees
SELECT e.deptno, XMLTYPE
(XMLELEMENT ("DEPT", XMLAttributes(DEPTNO AS "DEPTNO"),
XMLAGG (XMLELEMENT ("EMPLOYEE",
XMLELEMENT ("EMPNO", empno),
XMLELEMENT ("ENAME", ename),
XMLELEMENT ("JOB", job)
)
)
).getclobval ()
) AS "RESULT"
FROM emp e
GROUP BY e.deptno
/
--Manager Wise grouping of employees
SELECT e.mgr,
XMLELEMENT ("Manager", XMLAttributes(e.MGR AS "MGR"),
XMLAGG (XMLELEMENT ("EMPLOYEE",
XMLELEMENT ("EMPNO", e.empno),
XMLELEMENT ("ENAME", e.ename),
XMLELEMENT ("JOB", e.job)
)
)
) AS "RESULT"
FROM emp e
GROUP BY e.MGR
/
--Department wise grouping of employees
SELECT XMLTYPE
(XMLELEMENT("DepartmentList",
XMLAGG
(XMLELEMENT
("Department",xmlattributes (d.deptno AS "DEPTNO"),
(SELECT XMLELEMENT
("Employees",
XMLAGG
(XMLELEMENT
("Employee",
xmlattributes (e.empno AS "ID"),
XMLFOREST (e.ename AS "Name",e.sal AS "Salary",e.job AS "Job")
)
)
)
FROM emp e
WHERE e.deptno = d.deptno)
)
)
).getclobval ()
) AS "RESULT"
FROM (SELECT DISTINCT deptno FROM emp) d
ORDER BY d.deptno
/
SELECT XMLTYPE
(XMLELEMENT("DepartmentList",
XMLAGG
(XMLELEMENT
("Department",xmlattributes (d.deptno AS "DEPTNO"),
(SELECT XMLELEMENT
("Employees",
XMLAGG
(XMLELEMENT
("Employee",
xmlattributes (e.empno AS "ID"),
XMLFOREST (e.ename AS "Name",e.sal AS "Salary",e.job AS "Job")
)
)
)
FROM emp e
WHERE e.deptno = d.deptno)
)
)
).getclobval ()
) AS "RESULT"
FROM (SELECT DISTINCT deptno FROM emp) d
ORDER BY d.deptno
/
SELECT XMLElement("EMP",
XMLAttributes(EMPNO),
XMLFOREST (ENAME,JOB ,MGR,HIREDATE ,SAL ,COMM ,DEPTNO)).getclobval () AS Result
FROM emp
/
SELECT XMLELEMENT ("EMP",
XMLAttributes(EMPNO),
XMLFOREST (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)) AS Result
FROM emp
/
SELECT VALUE (e).getclobval () RESULT
FROM TABLE (XMLSEQUENCE (CURSOR (SELECT * FROM emp))) e
/
--Generate a XML Document from your Query
SELECT DBMS_XMLGEN.getXML('SELECT * FROM EMP') AS xml_doc FROM dual; -- Returns CLOB
/
--Generates XML Document from your Query
SELECT DBMS_XMLGEN.getXMLTYPE('SELECT * FROM EMP') AS xml_doc FROM dual; -- Returns XMLType
/
--Convert the specified column to XML string
SELECT SYS_XMLGEN (e.empno) AS xml_emno ,
SYS_XMLGEN (e.ename) AS xml_ename ,
SYS_XMLGEN (e.sal) AS xml_sal
FROM emp e
/
/* Following examples illustrates parsing of a XML document and presenting the data in a regular table format.
*/
--Returns only ENAME column
SELECT e.xml_doc.getrootelement (),
e.xml_doc.EXTRACT ('//ENAME') xml_ename
FROM (SELECT DBMS_XMLGEN.getxmltype ('SELECT * FROM EMP') AS xml_doc
FROM DUAL
) e
/
--Appends ENAME column data
SELECT e.xml_doc.EXTRACT('//ENAME/text()').getstringval() as empno
FROM (SELECT DBMS_XMLGEN.getxmltype ('SELECT * FROM EMP') AS xml_doc
FROM DUAL
) e
/
--
--Returns only ENAME column
SELECT e.xml_doc.EXTRACT ('//ENAME') xml_ename
FROM (SELECT DBMS_XMLGEN.getxmltype ('SELECT * FROM EMP') AS xml_doc
FROM DUAL
) e
/
--Extract all ENAME column values of the XML document
SELECT TRIM (EXTRACT (xmlTable.COLUMN_VALUE, '//ENAME/text()').getstringval ()) myName
FROM (SELECT DBMS_XMLGEN.getxmltype ('SELECT * FROM EMP') AS xml_doc
FROM DUAL
) e ,
TABLE (XMLSEQUENCE (EXTRACT (e.xml_doc, '//ROW/ENAME'))) xmlTable
/
SELECT TRIM (EXTRACT (xmlTable.COLUMN_VALUE, '//EMPNO/text()').getstringval ()) empno ,
TRIM (EXTRACT (xmlTable.COLUMN_VALUE, '//ENAME/text()').getstringval ()) ename
FROM (SELECT DBMS_XMLGEN.getxmltype ('SELECT * FROM EMP') AS xml_doc
FROM DUAL
) e ,
TABLE (XMLSEQUENCE (EXTRACT (e.xml_doc, '//ROW'))) xmlTable
WHERE TRIM (EXTRACT (xmlTable.COLUMN_VALUE, '//EMPNO/text()').getstringval ()) = '7369'
/
--Extract the ENAME
SELECT TRIM (extractValue (VALUE(xmlTable), '//ENAME')) ename
FROM (SELECT DBMS_XMLGEN.getxmltype ('SELECT * FROM EMP') AS xml_doc
FROM DUAL
) e ,
TABLE (XMLSEQUENCE (EXTRACT (e.xml_doc, '//ROW'))) xmlTable
WHERE TRIM (EXTRACT (xmlTable.COLUMN_VALUE, '//EMPNO/text()').getstringval ()) = '7369'
/
--Getting he Number of Elements in each department
SELECT TRIM (extractValue (VALUE(xmlTable), '//DEPTNO')) dept , COUNT(*)
FROM (SELECT DBMS_XMLGEN.getxmltype ('SELECT * FROM EMP') AS xml_doc
FROM DUAL
) e ,
TABLE (XMLSEQUENCE (EXTRACT (e.xml_doc, '//ROW'))) xmlTable
GROUP BY TRIM (extractValue (VALUE(xmlTable), '//DEPTNO'))
/
--This example shows how to create a simple relational view that exposes XML document contents:
CREATE OR REPLACE VIEW
VW_EMP( EMPNO ,ENAME ,JOB ,MGR ,HIREDATE ,SAL ,COMM ,DEPTNO )
AS
SELECT TRIM (extractValue (VALUE(xmlTable), '//EMPNO')) AS EMPNO,
TRIM (extractValue (VALUE(xmlTable), '//ENAME')) AS ENAME,
TRIM (extractValue (VALUE(xmlTable), '//JOB')) AS JOB ,
TRIM (extractValue (VALUE(xmlTable), '//MGR')) AS MGR ,
TRIM (extractValue (VALUE(xmlTable), '//HIREDATE')) AS HIREDATE ,
TRIM (extractValue (VALUE(xmlTable), '//SAL')) AS SAL ,
TRIM (extractValue (VALUE(xmlTable), '//COMM')) AS COMM,
TRIM (extractValue (VALUE(xmlTable), '//DEPTNO')) AS DEPT
FROM (SELECT DBMS_XMLGEN.getxmltype ('SELECT * FROM EMP') AS xml_doc
FROM DUAL
) e ,
TABLE (XMLSEQUENCE (EXTRACT (e.xml_doc, '//ROW'))) xmlTable
/
