How to Parse XML in Oracle

How to parse XML using APEX_DATA_PARSER?

 Get the XML data.

<?xml version="1.0" encoding="UTF-8"?>
<DATA>
   <ROW>
      <EMPNO>7839</EMPNO>
      <ENAME>KING</ENAME>
      <HIREDATE>1981-11-17</HIREDATE>
      <SAL>5000</SAL>
      <DEPTNO>10</DEPTNO>
   </ROW>
   <ROW>
      <EMPNO>7698</EMPNO>
      <ENAME>BLAKE</ENAME>
      <HIREDATE>1981-05-01</HIREDATE>
      <SAL>2850</SAL>
      <DEPTNO>30</DEPTNO>
      <MGR>7839</MGR>
   </ROW>
   <ROW>
      <EMPNO>7782</EMPNO>
      <ENAME>CLARK</ENAME>
      <HIREDATE>1981-06-09</HIREDATE>
      <SAL>2450</SAL>
      <DEPTNO>10</DEPTNO>
      <MGR>7839</MGR>
   </ROW>
   <ROW>
      <EMPNO>7566</EMPNO>
      <ENAME>JONES</ENAME>
      <HIREDATE>1981-04-02</HIREDATE>
      <SAL>2975</SAL>
      <DEPTNO>20</DEPTNO>
      <MGR>7839</MGR>
   </ROW>
   <ROW>
      <EMPNO>7788</EMPNO>
      <ENAME>SCOTT</ENAME>
      <HIREDATE>1982-12-09</HIREDATE>
      <SAL>3000</SAL>
      <DEPTNO>20</DEPTNO>
      <MGR>7566</MGR>
   </ROW>
   <ROW>
      <EMPNO>7902</EMPNO>
      <ENAME>FORD</ENAME>
      <HIREDATE>1981-12-03</HIREDATE>
      <SAL>3000</SAL>
      <DEPTNO>20</DEPTNO>
      <MGR>7566</MGR>
   </ROW>
   <ROW>
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <HIREDATE>1980-12-17</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
      <MGR>7902</MGR>
   </ROW>
   <ROW>
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <HIREDATE>1981-02-20</HIREDATE>
      <SAL>1600</SAL>
      <DEPTNO>30</DEPTNO>
      <MGR>7698</MGR>
      <COMM>300</COMM>
   </ROW>
   <ROW>
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <HIREDATE>1981-02-22</HIREDATE>
      <SAL>1250</SAL>
      <DEPTNO>30</DEPTNO>
      <MGR>7698</MGR>
      <COMM>500</COMM>
   </ROW>
   <ROW>
      <EMPNO>7654</EMPNO>
      <ENAME>MARTIN</ENAME>
      <HIREDATE>1981-09-28</HIREDATE>
      <SAL>1250</SAL>
      <DEPTNO>30</DEPTNO>
      <MGR>7698</MGR>
      <COMM>1400</COMM>
   </ROW>
   <ROW>
      <EMPNO>7844</EMPNO>
      <ENAME>TURNER</ENAME>
      <HIREDATE>1981-09-08</HIREDATE>
      <SAL>1500</SAL>
      <DEPTNO>30</DEPTNO>
      <MGR>7698</MGR>
      <COMM>0</COMM>
   </ROW>
   <ROW>
      <EMPNO>7876</EMPNO>
      <ENAME>ADAMS</ENAME>
      <HIREDATE>1983-01-12</HIREDATE>
      <SAL>1100</SAL>
      <DEPTNO>20</DEPTNO>
      <MGR>7788</MGR>
   </ROW>
   <ROW>
      <EMPNO>7900</EMPNO>
      <ENAME>JAMES</ENAME>
      <HIREDATE>1981-12-03</HIREDATE>
      <SAL>950</SAL>
      <DEPTNO>30</DEPTNO>
      <MGR>7698</MGR>
   </ROW>
   <ROW>
      <EMPNO>7934</EMPNO>
      <ENAME>MILLER</ENAME>
      <HIREDATE>1982-01-23</HIREDATE>
      <SAL>1300</SAL>
      <DEPTNO>10</DEPTNO>
      <MGR>7782</MGR>
   </ROW>
</DATA>


Parsing Query

SELECT line_number,
       col001,
       col002,
       col003
  FROM TABLE (apex_data_parser.parse (p_content     => (SELECT file_content
                                                          FROM loader_files
                                                         WHERE id = 4),
                                      p_file_name   => 'employee.xml'));


You might also like