Convert XML to Rows and Columns in Oracle

Thank you friends for a quite good response on my last article on "Generate XML data using SQL in Oracle Database". Many of my friends/readers asked me to write another post to convert XML back to tabular data i.e. Rows and columns using SQL.

In this post, I am going to use same XML which I generated in my last post and try to convert XML data back to Rows and columns using SQL. The XMLTABLE maps XML data into relational rows and columns. All the examples are executed on Oracle 11g R2.

Examples
1) Convert Tag based XML to Row and Columns
2) Convert Attribute based XML to Rows and Columns
3) Convert Complex Master-Detail XML to Rows and Columns


Following is the example to Generate Row and Columns using Tag based XML,

nimish@garg> with t as
  2  (
  3  select
  4  '<company>
  5  <employee><empno>7839</empno><ename>KING</ename><hiredate>1981-11-17</hiredate><sal>5000</sal><deptno>10</deptno><dname>ACCOUNTING</dname></employee>
  6  <employee><empno>7782</empno><ename>CLARK</ename><hiredate>1981-06-09</hiredate><sal>2450</sal><deptno>10</deptno><dname>ACCOUNTING</dname></employee>
  7  <employee><empno>7566</empno><ename>JONES</ename><hiredate>1981-04-02</hiredate><sal>2975</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
  8  <employee><empno>7788</empno><ename>SCOTT</ename><hiredate>1987-04-19</hiredate><sal>3000</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
  9  <employee><empno>7902</empno><ename>FORD</ename><hiredate>1981-12-03</hiredate><sal>3000</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
 10  <employee><empno>7369</empno><ename>SMITH</ename><hiredate>1980-12-17</hiredate><sal>800</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
 11  <employee><empno>7876</empno><ename>ADAMS</ename><hiredate>1987-05-23</hiredate><sal>1100</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
 12  <employee><empno>7934</empno><ename>MILLER</ename><hiredate>1982-01-23</hiredate><sal>1300</sal><deptno>10</deptno><dname>ACCOUNTING</dname></employee>
 13  </company>' myxml from dual
 14  )
 15  SELECT x.*
 16    FROM t,
 17         XMLTABLE ('/company/employee'
 18                   PASSING XMLPARSE (DOCUMENT t.myxml)
 19                   COLUMNS "empno" NUMBER (4, 0) PATH 'empno',
 20                           "ename" VARCHAR2 (10) PATH 'ename',
 21                           "hiredate" DATE PATH 'hiredate',
 22                           "sal" NUMBER (7, 2) PATH 'sal',
 23                           "deptno" NUMBER (2, 0) PATH 'deptno',
 24                           "dname" VARCHAR2 (14) PATH 'dname') x;

     empno ename      hiredate         sal     deptno dname
---------- ---------- --------- ---------- ---------- --------------
      7839 KING       17-NOV-81       5000         10 ACCOUNTING
      7782 CLARK      09-JUN-81       2450         10 ACCOUNTING
      7566 JONES      02-APR-81       2975         20 RESEARCH
      7788 SCOTT      19-APR-87       3000         20 RESEARCH
      7902 FORD       03-DEC-81       3000         20 RESEARCH
      7369 SMITH      17-DEC-80        800         20 RESEARCH
      7876 ADAMS      23-MAY-87       1100         20 RESEARCH
      7934 MILLER     23-JAN-82       1300         10 ACCOUNTING

8 rows selected.

Now, lets look at the example to Generate Row and Columns from attribute based XML.

nimish@garg> with t as(
  2  select
  3  '<company>
  4  <employee empno="7839" ename="KING" hiredate="1981-11-17" sal="5000" deptno="10" dname="ACCOUNTING"></employee>
  5  <employee empno="7782" ename="CLARK" hiredate="1981-06-09" sal="2450" deptno="10" dname="ACCOUNTING"></employee>
  6  <employee empno="7566" ename="JONES" hiredate="1981-04-02" sal="2975" deptno="20" dname="RESEARCH"></employee>
  7  <employee empno="7788" ename="SCOTT" hiredate="1987-04-19" sal="3000" deptno="20" dname="RESEARCH"></employee>
  8  <employee empno="7902" ename="FORD" hiredate="1981-12-03" sal="3000" deptno="20" dname="RESEARCH"></employee>
  9  <employee empno="7369" ename="SMITH" hiredate="1980-12-17" sal="800" deptno="20" dname="RESEARCH"></employee>
 10  <employee empno="7876" ename="ADAMS" hiredate="1987-05-23" sal="1100" deptno="20" dname="RESEARCH"></employee>
 11  <employee empno="7934" ename="MILLER" hiredate="1982-01-23" sal="1300" deptno="10" dname="ACCOUNTING"></employee>
 12  </company>' myxml from dual
 13  )
 14  SELECT x.*
 15    FROM t,
 16         XMLTABLE ('/company/employee'
 17                   PASSING XMLPARSE (DOCUMENT t.myxml)
 18                   COLUMNS "empno" NUMBER (4, 0) PATH '@empno',
 19                           "ename" VARCHAR2 (10) PATH '@ename',
 20                           "hiredate" DATE PATH '@hiredate',
 21                           "sal" NUMBER (7, 2) PATH '@sal',
 22                           "deptno" NUMBER (2, 0) PATH '@deptno',
 23                           "dname" VARCHAR2 (14) PATH '@dname') x;

     empno ename      hiredate         sal     deptno dname
---------- ---------- --------- ---------- ---------- --------------
      7839 KING       17-NOV-81       5000         10 ACCOUNTING
      7782 CLARK      09-JUN-81       2450         10 ACCOUNTING
      7566 JONES      02-APR-81       2975         20 RESEARCH
      7788 SCOTT      19-APR-87       3000         20 RESEARCH
      7902 FORD       03-DEC-81       3000         20 RESEARCH
      7369 SMITH      17-DEC-80        800         20 RESEARCH
      7876 ADAMS      23-MAY-87       1100         20 RESEARCH
      7934 MILLER     23-JAN-82       1300         10 ACCOUNTING

8 rows selected.

Worked nice and easy. If you have noticed, the only change was in PATH expression which used "@" to indicate that data is in attribute, rather than a tag.

Now lets try to Generate rows and columns from nested and Tag + Attribute XML as we mostly have in real life XML data. It is basically Converting Master Detail XML to tabular format.

nimish@garg> with t as(
  2  select
  3  '<company>
  4    <department deptno="10" dname="ACCOUNTING">
  5      <employees>
  6        <employee empno="7839"><ename>KING</ename><hiredate>1981-11-17</hiredate><sal>5000</sal></employee>
  7        <employee empno="7782"><ename>CLARK</ename><hiredate>1981-06-09</hiredate><sal>2450</sal></employee>
  8        <employee empno="7934"><ename>MILLER</ename><hiredate>1982-01-23</hiredate><sal>1300</sal></employee>
  9      </employees>
 10    </department>
 11    <department deptno="20" dname="RESEARCH">
 12      <employees>
 13        <employee empno="7566"><ename>JONES</ename><hiredate>1981-04-02</hiredate><sal>2975</sal></employee>
 14        <employee empno="7788"><ename>SCOTT</ename><hiredate>1987-04-19</hiredate><sal>3000</sal></employee>
 15        <employee empno="7902"><ename>FORD</ename><hiredate>1981-12-03</hiredate><sal>3000</sal></employee>
 16        <employee empno="7369"><ename>SMITH</ename><hiredate>1980-12-17</hiredate><sal>800</sal></employee>
 17        <employee empno="7876"><ename>ADAMS</ename><hiredate>1987-05-23</hiredate><sal>1100</sal></employee>
 18      </employees>
 19    </department>
 20  </company>' myxml from dual
 21  )
 22  SELECT dept.deptno, dept.dname, emp.*
 23    FROM t,
 24         XMLTABLE ('/company/department'
 25                   PASSING XMLPARSE (DOCUMENT t.myxml)
 26                   COLUMNS deptno NUMBER (2, 0) PATH '@deptno',
 27                           dname VARCHAR2 (14) PATH '@dname',
 28                                              employees xmltype  PATH 'employees') dept,
 29         XMLTABLE ('//employee'
 30                   PASSING dept.employees
 31                   COLUMNS empno NUMBER (4, 0) PATH '@empno',
 32                           ename VARCHAR2 (10) PATH 'ename',
 33                           hiredate DATE PATH 'hiredate',
 34                           sal NUMBER (7, 2) PATH 'sal') emp;

    DEPTNO DNAME               EMPNO ENAME      HIREDATE         SAL
---------- -------------- ---------- ---------- --------- ----------
        10 ACCOUNTING           7839 KING       17-NOV-81       5000
        10 ACCOUNTING           7782 CLARK      09-JUN-81       2450
        10 ACCOUNTING           7934 MILLER     23-JAN-82       1300
        20 RESEARCH             7566 JONES      02-APR-81       2975
        20 RESEARCH             7788 SCOTT      19-APR-87       3000
        20 RESEARCH             7902 FORD       03-DEC-81       3000
        20 RESEARCH             7369 SMITH      17-DEC-80        800
        20 RESEARCH             7876 ADAMS      23-MAY-87       1100

8 rows selected.

To get data in row and column format from Master-Detail XML or Parent-Child XML, we need to distribute it in same manner of Master-Detail XML or Parent-Child XML. As you can see in above SQL, I have created two XMLTABLEs, dept and emp. EMP is child of DEPT as we are passing dept.employees in it to fetch related rows.

I hope you have enjoyed reading this post and have learnt something new. Please post your feedback in comment box.

Related Links:
- JSON in Oracle Database with Examples
- Oracle Database 12c New Features for Developers
- dbms_xmlgen: XML output from SQL query
- Create CSV file using PL/SQL
- Load XML File in Oracle Table