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

11 comments:

  1. Thanks For Sharing Your Information The Information Shared Is Valuable Please Keep Updating Us Time Went On Just Reading The article OracleTraining In Hyderabad

    ReplyDelete
  2. Online Training | Classroom | Virtual Classes
    Selenium Certification Training 100% placement assistance
    1860 testers placed in 600 companies in last 8 years
    Real time expert trainers
    Indutry oriented training with corporate casestudies
    Free Aptitude classes & Mock interviews

    ReplyDelete
  3. how can I directly use it with query like my query is creating pivot column using pivot xml.
    I want to extract tag names and convert them into columns.
    select dept,pno from employee
    pivot xml (select employee-type from employee).

    I want to create columns based on employee-type xml columns.

    Any idea how to do that ?

    ReplyDelete
  4. It's great. Please keep sharing your solutions...

    ReplyDelete

  5. Quick up for the best offer of AWS DevOps Training in Chennai from Infycle Technologies, Excellent software training in Chennai. A massive place to learn other technical courses like Power BI, Cyber Security, Graphic Design and Animation, Block Security, Java, Oracle, Python, Big data, Azure, Python, Manual and Automation Testing, DevOps, Medical Coding etc., with outstanding training with experienced trainers with a fresh environment with 100+ Live Practical Sessions and Real-Time scenario after the finalisation of the course the trainee will able to get through the interview in top MNC’s with an amazing package for more enquiry approach us on 7504633633, 7502633633.

    ReplyDelete
  6. React JS Training in Hyderabad

    ReplyDelete
  7. That was amazing information

    ReplyDelete
  8. I'm grateful that you shared this article with me. Your willingness to pass along such insightful content is truly appreciated.


    blooket code

    ReplyDelete