Oracle XML DB Training in Chennai

To create a xml file, we need to create a directory and grant the read write permission to the specified USER as

C:\>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 29 15:29:12 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> create or replace directory myxml as 'E:\myxml\';
Directory created.

SQL> grant read, write on directory myxml to hr;
Grant succeeded.

SQL> grant execute on utl_file to hr;
Grant succeeded.

NOTE: "E:\myxml\" has to be physical location on disk.

After creating the directory and granting the permissions to the HR user, following PL/SQL code needs to be executed by the HR user to create XML file.

C:\>sqlplus hr/admin
SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 29 15:32:06 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> DECLARE
 2      F UTL_FILE.FILE_TYPE;
 3      MYCLOB CLOB;
 4  BEGIN
 5      SELECT
 6        DBMS_XMLGEN.GETXML('
 7          SELECT
 8            E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME
 9          FROM
10            EMPLOYEES E,
11            DEPARTMENTS D
12          WHERE
13            D.DEPARTMENT_ID=E.DEPARTMENT_ID
14        ')
15      INTO MYCLOB
16      FROM DUAL;
17
18      F := UTL_FILE.FOPEN('MYXML','EMP_DEPT.XML','w',32767);
19      UTL_FILE.PUT(F,MYCLOB);
20      UTL_FILE.FCLOSE(F);
21  END;
22  /

PL/SQL procedure successfully completed.

After the execution of above procedure, a file (EMP_DEPT.XML) would have been created at "E:\myxml\" location.

In my last post, I have created a XML file using PL/SQL. Here we are trying to load that XML data back in a normal table of Oracle Database. We assume that the directory is created and the permissions are already granted also the XML file has been exported. To read my last post on Exporting XML file using PL/SQL, creating directory and granting permission, please click Create XML file using PL/SQL
To Load "EMP_DEPT.XML" file in to Oracle Table we have created a table with same structure of XML file EMP_DEPT as

C:\>sqlplus hr/admin
SQL*Plus: Release 11.2.0.3.0 Production on Dec 29 15:52:06 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> CREATE TABLE EMP_DEPT
  2  (
  3  EMPNO NUMBER(4),
  4  ENAME VARCHAR2(10),
  5  SAL NUMBER(7,2),
  6  DNAME VARCHAR2(14)
  7  );

Table created.

Once the table is created, we just need to execute following query to load the XML file into the Oracle table.

SQL> INSERT INTO EMP_DEPT (EMPNO, ENAME, SAL, DNAME)
  2  SELECT *
  3  FROM XMLTABLE('/ROWSET/ROW'
  4         PASSING XMLTYPE(BFILENAME('MYXML', 'EMP_DEPT.XML'),
  5         NLS_CHARSET_ID('CHAR_CS'))
  6         COLUMNS EMPNO  NUMBER(4)    PATH 'EMPLOYEE_ID',
  7                 ENAME  VARCHAR2(10) PATH 'FIRST_NAME',
  8                 SAL    NUMBER(7,2)  PATH 'SALARY',
  9                 DNAME  VARCHAR2(14) PATH 'DEPARTMENT_NAME'
10       )
11  ;
14 rows created.

Data of "EMP_DEPT" can be determind as following

SQL> SELECT * FROM EMP_DEPT;
     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7782 CLARK            2450 ACCOUNTING
      7839 KING             5000 ACCOUNTING
      7934 MILLER           1300 ACCOUNTING
      7566 JONES            2975 RESEARCH
      7902 FORD             3000 RESEARCH
      7876 ADAMS            1100 RESEARCH
      7369 SMITH            1000 RESEARCH
      7788 SCOTT            3000 RESEARCH
      7521 WARD             4000 SALES
      7844 TURNER           1500 SALES
      7499 ALLEN            2000 SALES
      7900 JAMES             950 SALES
      7698 BLAKE            2850 SALES
      7654 MARTIN           1250 SALES

14 rows selected.

Question:  I have a CLOB that is storing XML, and I need to know the procedure for extracting the XML from the CLOB data type.  What is the way to get the XML from a CLOB in Oracle?


Answer:  You use the extractvalue procedure for extracting XML from a CLOB in Oracle SQL select statements. 

Direct CLOB projection is supported starting with 11.2.0.2, using XMLTable or XMLCast/XQuery functions (extractvalue, extract, xmlsequence are deprecated now) :

SELECT
   x.*
FROM
   test_xml t
   , XMLTable(
   '/*/record'
   passing t.xml_data
   columns
   description clob path 'description'
   ) x
;

1. Create an XML table.

create table
   xml_key
(  
   mycol number,
   xml   clob
);

2. Insert some xml data into the xml data column:

with
   xmlData
as
( select
      XMLTYPE(t.xml) xml
  from
      xml_demo t
  where t.id = 1 )
      select
          x.xml.EXTRACT('//name/text()').getStringVal(),
          x.xml.EXTRACT('//address/line1/text()').getStringVal()
      from
          xmlData x ;