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.