Generate XML data using SQL in Oracle Database

With this blog post I am providing some examples to generate XML dataset using SQL in Oracle Database. Oracle Database provides SQL/XML standard functions to generate XML Data using SQL, which are as following:
1. XMLELEMENT: basic function to convert data to XML format
2. XMLAGG: Group multiple rows of XMLELEMENT in one XML, like LISTAGG
3. XMLFOREST: Avoids the use of multiple XMLELEMENT functions, it allows us to generate multiple elements in one call.
4. XMLATTRIBUTES: Works same as XMLFOREST, but generate data in attributes instead of XML tags

Following is the example to generate Tag based XML, which I executed on Oracle 11g R2. I have formatted the output for better readability

nimish@garg> SELECT XMLELEMENT ("company",
  2                     XMLAGG (XMLELEMENT ("employee",
  3                                         XMLFOREST (e.empno AS "empno",
  4                                                    e.ename AS "ename",
  5                                                    e.hiredate AS "hiredate",
  6                                                    e.sal AS "sal",
  7                                                    d.deptno as "deptno",
  8                                                    d.dname as "dname")))) as "EMPXML"
  9    FROM emp e, dept d
 10   WHERE e.deptno = d.deptno AND d.deptno IN (10, 20);

EMPXML
------------------------------------------------------------------------------------------------------------------------------------------------------
<company>
<employee><empno>7839</empno><ename>KING</ename><hiredate>1981-11-17</hiredate><sal>5000</sal><deptno>10</deptno><dname>ACCOUNTING</dname></employee>
<employee><empno>7782</empno><ename>CLARK</ename><hiredate>1981-06-09</hiredate><sal>2450</sal><deptno>10</deptno><dname>ACCOUNTING</dname></employee>
<employee><empno>7566</empno><ename>JONES</ename><hiredate>1981-04-02</hiredate><sal>2975</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
<employee><empno>7788</empno><ename>SCOTT</ename><hiredate>1987-04-19</hiredate><sal>3000</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
<employee><empno>7902</empno><ename>FORD</ename><hiredate>1981-12-03</hiredate><sal>3000</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
<employee><empno>7369</empno><ename>SMITH</ename><hiredate>1980-12-17</hiredate><sal>800</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
<employee><empno>7876</empno><ename>ADAMS</ename><hiredate>1987-05-23</hiredate><sal>1100</sal><deptno>20</deptno><dname>RESEARCH</dname></employee>
<employee><empno>7934</empno><ename>MILLER</ename><hiredate>1982-01-23</hiredate><sal>1300</sal><deptno>10</deptno><dname>ACCOUNTING</dname></employee>
</company>

Now, lets look at the example to generate attribute based XML. For this we simply need to replace XMLFOREST with XMLATTRIBUTES. Let's try:

nimish@garg> SELECT XMLELEMENT (
  2            "company",
  3            XMLAGG (XMLELEMENT ("employee",
  4                                XMLATTRIBUTES (e.empno AS "empno",
  5                                               e.ename AS "ename",
  6                                               e.hiredate AS "hiredate",
  7                                               e.sal AS "sal",
  8                                               d.deptno AS "deptno",
  9                                               d.dname AS "dname"))))
 10            AS "EMPXML"
 11    FROM emp e, dept d
 12   WHERE e.deptno = d.deptno AND d.deptno IN (10, 20);

EMPXML
-------------------------------------------------------------------------------
<company>
<employee empno="7839" ename="KING" hiredate="1981-11-17" sal="5000" deptno="10" dname="ACCOUNTING"></employee>
<employee empno="7782" ename="CLARK" hiredate="1981-06-09" sal="2450" deptno="10" dname="ACCOUNTING"></employee>
<employee empno="7566" ename="JONES" hiredate="1981-04-02" sal="2975" deptno="20" dname="RESEARCH"></employee>
<employee empno="7788" ename="SCOTT" hiredate="1987-04-19" sal="3000" deptno="20" dname="RESEARCH"></employee>
<employee empno="7902" ename="FORD" hiredate="1981-12-03" sal="3000" deptno="20" dname="RESEARCH"></employee>
<employee empno="7369" ename="SMITH" hiredate="1980-12-17" sal="800" deptno="20" dname="RESEARCH"></employee>
<employee empno="7876" ename="ADAMS" hiredate="1987-05-23" sal="1100" deptno="20" dname="RESEARCH"></employee>
<employee empno="7934" ename="MILLER" hiredate="1982-01-23" sal="1300" deptno="10" dname="ACCOUNTING"></employee>
</company>

Worked nice and easy. Now what if, we want to create nested XML like DEPT as parent tag and EMP as child tag. Also try to mix Tag based and Attribute based XML as we mostly have in real life XML data. I have formatted the output for better readability

nimish@garg> SELECT XMLELEMENT (
  2            "company",
  3            XMLAGG (
  4               XMLELEMENT (
  5                  "department",
  6                  XMLATTRIBUTES (d.deptno AS "deptno", d.dname AS "dname"),
  7                  XMLFOREST (
  8                     (SELECT XMLAGG (
  9                                XMLELEMENT (
 10                                   "employee",
 11                                   XMLATTRIBUTES (e.empno AS "empno"),
 12                                   XMLFOREST (e.ename AS "ename",
 13                                              e.hiredate AS "hiredate",
 14                                              e.sal AS "sal")))
 15                        FROM emp e
 16                       WHERE e.deptno = d.deptno) "employees"))))
 17            AS "departments"
 18    FROM dept d
 19   WHERE d.deptno in (10,20);

departments
--------------------------------------------------------------------------------------------------------------------
<company>
  <department deptno="10" dname="ACCOUNTING">
    <employees>
      <employee empno="7839"><ename>KING</ename><hiredate>1981-11-17</hiredate><sal>5000</sal></employee>
      <employee empno="7782"><ename>CLARK</ename><hiredate>1981-06-09</hiredate><sal>2450</sal></employee>
      <employee empno="7934"><ename>MILLER</ename><hiredate>1982-01-23</hiredate><sal>1300</sal></employee>
    </employees>
  </department>
  <department deptno="20" dname="RESEARCH">
    <employees>
      <employee empno="7566"><ename>JONES</ename><hiredate>1981-04-02</hiredate><sal>2975</sal></employee>
      <employee empno="7788"><ename>SCOTT</ename><hiredate>1987-04-19</hiredate><sal>3000</sal></employee>
      <employee empno="7902"><ename>FORD</ename><hiredate>1981-12-03</hiredate><sal>3000</sal></employee>
      <employee empno="7369"><ename>SMITH</ename><hiredate>1980-12-17</hiredate><sal>800</sal></employee>
      <employee empno="7876"><ename>ADAMS</ename><hiredate>1987-05-23</hiredate><sal>1100</sal></employee>
    </employees>
  </department>
</company>

I must say Oracle provides very extensive and easy features to generate XML. Please refer Oracle Documentation for reading this feature in detail. I hope you have enjoyed reading this.

Related Links:
- Convert XML to Rows and Columns in Oracle
- 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