8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
XMLTYPE Datatype
Oracle 9i introduced a dedicated XML datatype called XMLTYPE. It is made up of a LOB to store the original XML data and a number of member functions to make the data available to SQL. In this article I'll present a simple example of it's use.
Related articles.
Basic Usage
First we must create a table to store XML documents using the XMLTYPE datatype.
CREATE TABLE tab1 ( col1 SYS.XMLTYPE );
The table can be populated using XML from a CLOB, VARCHAR2 or an XMLTYPE generated from a query. In later versions of the database the constructor for XMLTYPE is overloaded to support creation from BLOB, BFILE, REF CURSOR, ANYDATA etc.
DECLARE v_xml SYS.XMLTYPE; v_doc CLOB; BEGIN -- XMLTYPE created from a CLOB v_doc := '<?xml version="1.0"?>' || Chr(10) || ' <TABLE_NAME>MY_TABLE</TABLE_NAME>'; v_xml := SYS.XMLTYPE.createXML(v_doc); -- This works too! --v_xml := sys.xmltype(v_doc); INSERT INTO tab1 (col1) VALUES (v_xml); -- XMLTYPE created from a query SELECT SYS_XMLGEN(table_name) INTO v_xml FROM user_tables WHERE rownum = 1; INSERT INTO tab1 (col1) VALUES (v_xml); COMMIT; END; /
The data in the table can be viewed using the following query.
SET LONG 1000 SELECT a.col1.getStringVal() FROM tab1 a; A.COL1.GETSTRINGVAL() ---------------------------------------------------------------------------------------------------- <?xml version="1.0"?> <TABLE_NAME>MY_TABLE</TABLE_NAME> <?xml version="1.0"?> <TABLE_NAME>TAB1</TABLE_NAME> 2 rows selected. SQL>
We can extract the value of specific tags using the following.
SELECT a.col1.extract('//TABLE_NAME/text()').getStringVal() AS "Table Name"
FROM tab1 a
WHERE a.col1.existsNode('/TABLE_NAME') = 1;
Table Name
----------------------------------------------------------------------------------------------------
MY_TABLE
TAB1
2 rows selected.
SQL>
In the above example I was expecting a string, but NUMBER and CLOB types can be returned using getNumVal() and getClobVal() respectively. Since the XMLTYPE datatype can contain any XML document it is sensible to limit the query to those rows which contain the relevant tags, hence the WHERE clause.
Convert Ref Cursor to XMLTYPE
The XMLTYPE data type can also be used in combination with the CURSOR expression to produce XML from a query.
SELECT XMLTYPE(CURSOR(SELECT dummy FROM dual)) FROM dual;
XMLTYPE(CURSOR(SELECT*FROMDUAL))
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DUMMY>X</DUMMY>
</ROW>
</ROWSET>
SQL>
We could also do something a little more complex. Create the following tables.
CREATE TABLE DEPT (
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
) ;
CREATE TABLE EMP (
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
The following query uses the CURSOR expression twice. The outer call converts the query to a ref cursor that is passed to the XMLTYPE constructor. Internally the query uses a CURSOR expression to nest the employees data as a ref cursor within the department rows. The query is limited to department "20" to reduce the size of the output.
SET LONG 1000000
SELECT XMLTYPE(CURSOR(SELECT d.dname AS "department_name",
d.deptno AS "department_number",
CURSOR(SELECT e.empno AS "employee_number",
e.ename AS "employee_name"
FROM emp e
WHERE e.deptno = d.deptno
ORDER BY e.empno) AS "employees"
FROM dept d
WHERE d.deptno = 20
ORDER BY d.dname)) AS data
FROM dual;
DATA
----------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<department_name>RESEARCH</department_name>
<department_number>20</department_number>
<employees>
<employees_ROW>
<employee_number>7369</employee_number>
<employee_name>SMITH</employee_name>
</employees_ROW>
<employees_ROW>
<employee_number>7566</employee_number>
<employee_name>JONES</employee_name>
</employees_ROW>
<employees_ROW>
<employee_number>7788</employee_number>
<employee_name>SCOTT</employee_name>
</employees_ROW>
<employees_ROW>
<employee_number>7876</employee_number>
<employee_name>ADAMS</employee_name>
</employees_ROW>
<employees_ROW>
<employee_number>7902</employee_number>
<employee_name>FORD</employee_name>
</employees_ROW>
</employees>
</ROW>
</ROWSET>
1 row selected.
SQL>
We could do something similar from PL/SQL, as shown below.
SET SERVEROUTPUT ON
DECLARE
l_cursor SYS_REFCURSOR;
l_xml XMLTYPE;
BEGIN
OPEN l_cursor FOR
SELECT d.dname AS "department_name",
d.deptno AS "department_number",
CURSOR(SELECT e.empno AS "employee_number",
e.ename AS "employee_name"
FROM emp e
WHERE e.deptno = d.deptno
ORDER BY e.empno) AS "employees"
FROM dept d
WHERE d.deptno = 20
ORDER BY d.dname;
l_xml := XMLTYPE(l_cursor);
DBMS_OUTPUT.put_line(l_xml.getClobVal());
END;
/
<?xml version="1.0"?>
<ROWSET>
<ROW>
<department_name>RESEARCH</department_name>
<department_number>20</department_number>
<employees>
<employees_ROW>
<employee_number>7369</employee_number>
<employee_name>SMITH</employee_name>
</employees_ROW>
<employees_ROW>
<employee_number>7566</employee_number>
<employee_name>JONES</employee_name>
</employees_ROW>
<employees_ROW>
<employee_number>7788</employee_number>
<employee_name>SCOTT</employee_name>
</employees_ROW>
<employees_ROW>
<employee_number>7876</employee_number>
<employee_name>ADAMS</employee_name>
</employees_ROW>
<employees_ROW>
<employee_number>7902</employee_number>
<employee_name>FORD</employee_name>
</employees_ROW>
</employees>
</ROW>
</ROWSET>
PL/SQL procedure successfully completed.
SQL>
For more information see:
- All XML Articles
- Load XMLTYPE From File
- Load XMLTYPE From URL
- XMLTABLE : XML Data in Variables
- XMLTYPE
Hope this helps. Regards Tim...