Home » SQL » SQL Query Example to Read Data from XML in Oracle

SQL Query Example to Read Data from XML in Oracle

Below is the example of SQL query to read / extract data from XML in Oracle. You can query any XML easily by providing its namespace clause and the path information through SQL query.

SQL Query Example to Read Data from XML in Oracle

Suppose you have XML data in the following format:

<main>
  <DATA_RECORD>
<EMPNO>7369</EMPNO>
<ENAME>SMITH11V9</ENAME>
<JOB>CLERK</JOB>
<MGR>7698</MGR>
<HIREDATE>1980/12/17 00:00:00</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</DATA_RECORD>
<DATA_RECORD>
<EMPNO>7499</EMPNO>

...
Then you must specify /main/DATA_RECORD for the name space clause in XMLTABLE() function and to extract column name specify column name as path as shown in below example:
Note: Name space clause and Path names are case sensitive.
SELECT *
  FROM XMLTABLE (
          '/main/DATA_RECORD'
          PASSING xmltype ('
<main>
  <DATA_RECORD>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH11V9</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1980/12/17 00:00:00</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981/02/20 00:00:00</HIREDATE>
    <SAL>1600</SAL>
    <COMM>300</COMM>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7654</EMPNO>
    <ENAME>MARTIN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981/09/28 00:00:00</HIREDATE>
    <SAL>1250</SAL>
    <COMM>1400</COMM>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7698</EMPNO>
    <ENAME>BLAKE</ENAME>
    <JOB>MANAGER</JOB>
    <MGR>7839</MGR>
    <HIREDATE>1981/05/01 00:00:00</HIREDATE>
    <SAL>2850</SAL>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7788</EMPNO>
    <ENAME>SCOTT</ENAME>
    <JOB>ANALYST</JOB>
    <MGR>7566</MGR>
    <HIREDATE>1987/04/19 00:00:00</HIREDATE>
    <SAL>30000</SAL>
    <DEPTNO>20</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7839</EMPNO>
    <ENAME>KING</ENAME>
    <JOB>PRESIDENT</JOB>
    <HIREDATE>1981/11/17 00:00:00</HIREDATE>
    <SAL>5000</SAL>
    <DEPTNO>10</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7844</EMPNO>
    <ENAME>TURNER</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981/09/08 00:00:00</HIREDATE>
    <SAL>1500</SAL>
    <COMM>0</COMM>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7876</EMPNO>
    <ENAME>ADAMS</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7788</MGR>
    <HIREDATE>1987/05/23 00:00:00</HIREDATE>
    <SAL>1100</SAL>
    <DEPTNO>20</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7900</EMPNO>
    <ENAME>JAMES</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981/12/03 00:00:00</HIREDATE>
    <SAL>950</SAL>
    <COMM>22</COMM>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>1</EMPNO>
    <ENAME>ddd</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7782</MGR>
    <HIREDATE>2016/05/01 00:00:00</HIREDATE>
    <DEPTNO>3</DEPTNO>
  </DATA_RECORD>
</main>')
          COLUMNS ename PATH 'ENAME',
JOB PATH 'JOB',
sal NUMBER PATH 'SAL')