• Post author:
  • Post category:All / SQL / xml
  • Reading time:4 mins read
You are currently viewing 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')

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.