Understanding DBMS_XMLGEN Package In Oracle 19c

By Shoumadip Das
PL/SQL
October 28, 2022
0 Comment

In Oracle, the DBMS_XMLGEN package converts the results of a SQL query to a canonical XML format.

The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB.

We have the provision to either create a single XML for all the rows or create separate XMLs for each rows.

--Base Table
SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

--Creating Single XML For All The Rows.
SQL> DECLARE
  2   QRYCTX DBMS_XMLGEN.CTXHANDLE;
  3   CRES   CLOB;
  4  BEGIN
  5   QRYCTX := DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM EMP');
  6   DBMS_XMLGEN.SETROWSETTAG(QRYCTX,'FOO');
  7   DBMS_XMLGEN.SETROWTAG(QRYCTX,'');
  8   DBMS_XMLGEN.SETNULLHANDLING(QRYCTX,DBMS_XMLGEN.EMPTY_TAG);
  9   CRES := DBMS_XMLGEN.GETXML(QRYCTX);
 10   DBMS_OUTPUT.PUT_LINE(CRES);
 11   DBMS_XMLGEN.CLOSECONTEXT(QRYCTX);
 12  END;
 13  /

<?xml version="1.0"?>
<foo>
 <EMPNO>7839</EMPNO>
 <ENAME>KING</ENAME>
 <JOB>PRESIDENT</JOB>
 <MGR/>
 <HIREDATE>17-NOV-81</HIREDATE>
 <SAL>5000</SAL>
 <COMM/>
 <DEPTNO>10</DEPTNO>
 
 <EMPNO>7698</EMPNO>
 <ENAME>BLAKE</ENAME>
 <JOB>MANAGER</JOB>
 <MGR>7839</MGR>
 <HIREDATE>01-MAY-81</HIREDATE> 
 <SAL>2850</SAL>
 <COMM/>
 <DEPTNO>30</DEPTNO>
 
 <EMPNO>7782</EMPNO>
 <ENAME>CLARK</ENAME>
 <JOB>MANAGER</JOB>
 <MGR>7839</MGR>
 <HIREDATE>09-JUN-81</HIREDATE>
 <SAL>2450</SAL>
 <COMM/>
 <DEPTNO>10</DEPTNO>
 
 <EMPNO>7566</EMPNO>
 <ENAME>JONES</ENAME>
 <JOB>MANAGER</JOB>
 <MGR>7839</MGR>
 <HIREDATE>02-APR-81</HIREDATE>
 <SAL>2975</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
 
 <EMPNO>7788</EMPNO>
 <ENAME>SCOTT</ENAME>
 <JOB>ANALYST</JOB>
 <MGR>7566</MGR>
 <HIREDATE>19-APR-87</HIREDATE>
 <SAL>3000</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
 
 <EMPNO>7902</EMPNO>
 <ENAME>FORD</ENAME>
 <JOB>ANALYST</JOB>
 <MGR>7566</MGR>
 <HIREDATE>03-DEC-81</HIREDATE>
 <SAL>3000</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
 
 <EMPNO>7369</EMPNO>
 <ENAME>SMITH</ENAME>
 <JOB>CLERK</JOB>
 <MGR>7902</MGR>
 <HIREDATE>17-DEC-80</HIREDATE>
 <SAL>800</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
 
 <EMPNO>7499</EMPNO>
 <ENAME>ALLEN</ENAME>
 <JOB>SALESMAN</JOB>
 <MGR>7698</MGR>
 <HIREDATE>20-FEB-81</HIREDATE>
 <SAL>1600</SAL>
 <COMM>300</COMM>
 <DEPTNO>30</DEPTNO>
 
 <EMPNO>7521</EMPNO>
 <ENAME>WARD</ENAME>
 <JOB>SALESMAN</JOB>
 <MGR>7698</MGR>
 <HIREDATE>22-FEB-81</HIREDATE>
 <SAL>1250</SAL>
 <COMM>500</COMM>
 <DEPTNO>30</DEPTNO>
 
 <EMPNO>7654</EMPNO>
 <ENAME>MARTIN</ENAME>
 <JOB>SALESMAN</JOB>
 <MGR>7698</MGR>
 <HIREDATE>28-SEP-81</HIREDATE>
 <SAL>1250</SAL>
 <COMM>1400</COMM>
 <DEPTNO>30</DEPTNO>
 
 <EMPNO>7844</EMPNO>
 <ENAME>TURNER</ENAME>
 <JOB>SALESMAN</JOB>
 <MGR>7698</MGR>
 <HIREDATE>08-SEP-81</HIREDATE>
 <SAL>1500</SAL>
 <COMM>0</COMM>
 <DEPTNO>30</DEPTNO>
 
 <EMPNO>7876</EMPNO>
 <ENAME>ADAMS</ENAME>
 <JOB>CLERK</JOB>
 <MGR>7788</MGR>
 <HIREDATE>23-MAY-87</HIREDATE>
 <SAL>1100</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
 
 <EMPNO>7900</EMPNO>
 <ENAME>JAMES</ENAME>
 <JOB>CLERK</JOB>
 <MGR>7698</MGR>
 <HIREDATE>03-DEC-81</HIREDATE>
 <SAL>950</SAL>
 <COMM/>
 <DEPTNO>30</DEPTNO>
 
 <EMPNO>7934</EMPNO>
 <ENAME>MILLER</ENAME>
 <JOB>CLERK</JOB>
 <MGR>7782</MGR>
 <HIREDATE>23-JAN-82</HIREDATE>
 <SAL>1300</SAL>
 <COMM/>
 <DEPTNO>10</DEPTNO>
</foo>

PL/SQL procedure successfully completed.

--Creating Separate XMLs For Each Rows.
SQL> DECLARE
  2   QRYCTX DBMS_XMLGEN.CTXHANDLE;
  3   CRES   CLOB;
  4  BEGIN
  5   QRYCTX := DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM EMP');
  6   --ONE XML PER ROW
  7   DBMS_XMLGEN.SETMAXROWS(QRYCTX,1);
  8   LOOP
  9    CRES := NULL;
 10    DBMS_XMLGEN.SETROWSETTAG(QRYCTX,'FOO');
 11    DBMS_XMLGEN.SETROWTAG(QRYCTX,'');
 12    DBMS_XMLGEN.SETNULLHANDLING(QRYCTX,DBMS_XMLGEN.EMPTY_TAG);
 13    CRES := DBMS_XMLGEN.GETXML(QRYCTX);
 14    EXIT WHEN DBMS_XMLGEN.GETNUMROWSPROCESSED(QRYCTX) = 0;
 15    DBMS_OUTPUT.PUT_LINE(CRES);
 16   END LOOP;
 17    DBMS_XMLGEN.CLOSECONTEXT(QRYCTX);
 18  END;
 19  /
 
<?xml version="1.0"?>
<foo>
 <EMPNO>7839</EMPNO>
 <ENAME>KING</ENAME>
 <JOB>PRESIDENT</JOB>
 <MGR/>
 <HIREDATE>17-NOV-81</HIREDATE>
 <SAL>5000</SAL>
 <COMM/>
 <DEPTNO>10</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7698</EMPNO>
 <ENAME>BLAKE</ENAME>
 <JOB>MANAGER</JOB>
 <MGR>7839</MGR>
 <HIREDATE>01-MAY-81</HIREDATE>
 <SAL>2850</SAL>
 <COMM/>
 <DEPTNO>30</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7782</EMPNO>
 <ENAME>CLARK</ENAME>
 <JOB>MANAGER</JOB>
 <MGR>7839</MGR>
 <HIREDATE>09-JUN-81</HIREDATE>
 <SAL>2450</SAL>
 <COMM/>
 <DEPTNO>10</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7566</EMPNO>
 <ENAME>JONES</ENAME>
 <JOB>MANAGER</JOB>
 <MGR>7839</MGR>
 <HIREDATE>02-APR-81</HIREDATE>
 <SAL>2975</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7788</EMPNO>
 <ENAME>SCOTT</ENAME>
 <JOB>ANALYST</JOB>
 <MGR>7566</MGR>
 <HIREDATE>19-APR-87</HIREDATE>
 <SAL>3000</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7902</EMPNO>
 <ENAME>FORD</ENAME>
 <JOB>ANALYST</JOB>
 <MGR>7566</MGR>
 <HIREDATE>03-DEC-81</HIREDATE>
 <SAL>3000</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7369</EMPNO>
 <ENAME>SMITH</ENAME>
 <JOB>CLERK</JOB>
 <MGR>7902</MGR>
 <HIREDATE>17-DEC-80</HIREDATE>
 <SAL>800</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7499</EMPNO>
 <ENAME>ALLEN</ENAME>
 <JOB>SALESMAN</JOB>
 <MGR>7698</MGR>
 <HIREDATE>20-FEB-81</HIREDATE>
 <SAL>1600</SAL>
 <COMM>300</COMM>
 <DEPTNO>30</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7521</EMPNO>
 <ENAME>WARD</ENAME>
 <JOB>SALESMAN</JOB>
 <MGR>7698</MGR>
 <HIREDATE>22-FEB-81</HIREDATE>
 <SAL>1250</SAL>
 <COMM>500</COMM>
 <DEPTNO>30</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7654</EMPNO>
 <ENAME>MARTIN</ENAME>
 <JOB>SALESMAN</JOB>
 <MGR>7698</MGR>
 <HIREDATE>28-SEP-81</HIREDATE>
 <SAL>1250</SAL>
 <COMM>1400</COMM>
 <DEPTNO>30</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7844</EMPNO>
 <ENAME>TURNER</ENAME>
 <JOB>SALESMAN</JOB>
 <MGR>7698</MGR>
 <HIREDATE>08-SEP-81</HIREDATE>
 <SAL>1500</SAL>
 <COMM>0</COMM>
 <DEPTNO>30</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7876</EMPNO>
 <ENAME>ADAMS</ENAME>
 <JOB>CLERK</JOB>
 <MGR>7788</MGR>
 <HIREDATE>23-MAY-87</HIREDATE>
 <SAL>1100</SAL>
 <COMM/>
 <DEPTNO>20</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7900</EMPNO>
 <ENAME>JAMES</ENAME>
 <JOB>CLERK</JOB>
 <MGR>7698</MGR>
 <HIREDATE>03-DEC-81</HIREDATE>
 <SAL>950</SAL>
 <COMM/>
 <DEPTNO>30</DEPTNO>
</foo>

<?xml version="1.0"?>
<foo>
 <EMPNO>7934</EMPNO>
 <ENAME>MILLER</ENAME>
 <JOB>CLERK</JOB>
 <MGR>7782</MGR>
 <HIREDATE>23-JAN-82</HIREDATE>
 <SAL>1300</SAL>
 <COMM/>
 <DEPTNO>10</DEPTNO>
</foo>

PL/SQL procedure successfully completed.

Share it

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –