8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

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:

Hope this helps. Regards Tim...

Back to the Top.