Data Fetch in XML Structure using DBMS_XMLGEN

[amitpatel66]

In real time applications, there is always a requirement to generate the data selected from database in XML format.

using Oracle, generating XML Structured data becomes easier using the inbuilt package DBMS_XMLGEN. This package can be used to convert the OUTPUT of the SELECT Query in to XML Format.

The below code can be used to generate the SELECT Query OUTPUT in XML Format:

 
DECLARE
my_context NUMBER := 0;
sample_xml_output VARCHAR2(32767);
data_size NUMBER := 0;
data_chunk NUMBER := 0;
offset NUMBER := 0;
 
BEGIN
 
-- Create a New Context
 
my_context:= DBMS_XMLGEN.NEWCONTEXT ('SELECT * FROM emp order by 1');
DBMS_OUTPUT.PUT_LINE('My New Context ID is:'||my_context);
 
-- Set the Root Element
 
DBMS_XMLGEN.SETROWSETTAG(my_context,'EMP_DETAILS');
 
-- Set the Row Element
 
DBMS_XMLGEN.SETROWTAG(my_context,'EMP');
 
-- Generate XML Output
 
SELECT DBMS_XMLGEN.GETXML(my_context) INTO sample_xml_output FROM DUAL;
data_chunk := LENGTH(sample_xml_output);
offset := 0;
data_size := 200;
 
-- Print 200 Chunk of data on Screen
 
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(sample_xml_output,offset + 1,data_size));
offset:= offset + data_size;
data_size := LEAST(200,data_chunk - data_size);
data_chunk := data_chunk - data_size;
EXIT WHEN data_chunk <= 0;
END LOOP;
 
-- Close the Context
 
DBMS_XMLGEN.CLOSECONTEXT(my_context);
 
--Exception Block
 
EXCEPTION
WHEN OTHERS THEN
NULL; -- Add your exception handler message here
END;
/
 
 

Let me explain each Functions/Procedures used in the above code:

DBMS_XMLGEN.NEWCONTEXT

This function is used to create a NEW CONTEXT that returns the CONTEXTID. This CONTEXTID will be required when other functions/procedures of DBMS_XMLGEN package is used

DBMS_XMLGEN.SETROWSETTAG

This Procedure is used to SET the ROOT ELEMENT for the XML Data. In the above example, I am setting the ROOT ELEMENT as EMP_DETAILS. You can check the ROOT ELEMENT in the Output posted below which will be set to EMP_DETAILS. The Root Element can be set to anything depending on the type of data it holds.

DBMS_XMLGEN.SETROWTAG

This Procedure is used to SET the ROW TAG for the XML Data. Since the output is Employee details, I am setting the ROW TAG to EMP

DBMS_XMLGEN.GETXML

This Function is used to generate the XML Structured output for the New Context that we have created. While creating a New Context, we passed a SELECT Query as an Input Parameter. So using this Function will Convert the Output of the SELECT Query to XML Format Data using the Corresponding ROW TAG andROWSET TAG that we have defined using SETROWSETTAG and SETROWTAG Procedures as shown above

Check below Sample Output generated by the code posted above:

 
My New Context ID is:44
<?xml version="1.0"?>
<EMP_DETAILS>
 <EMP>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17-DEC-80</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </
EMP>
 <EMP>
  <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>
 </EMP>
 <E
MP>
  <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>
 </EMP>
 <EMP>
  <EM
PNO>7566</EMPNO>
  <ENAME>JONES</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>02-APR-81</HIREDATE>
  <SAL>2975</SAL>
  <DEPTNO>20</DEPTNO>
 </EMP>
 <EMP>
  <EMPNO>7654</EMPNO>
  <ENAME>MA
RTIN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>28-SEP-81</HIREDATE>
  <SAL>1250</SAL>
  <COMM>1400</COMM>
  <DEPTNO>30</DEPTNO>
 </EMP>
 <EMP>
  <EMPNO>7698</EMPNO>
  <ENAME>BLAKE</E
NAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>01-MAY-81</HIREDATE>
  <SAL>2850</SAL>
  <DEPTNO>30</DEPTNO>
 </EMP>
 <EMP>
  <EMPNO>7782</EMPNO>
  <ENAME>CLARK</ENAME>
  <JOB>MANAGER</JOB>
 
<MGR>7839</MGR>
  <HIREDATE>09-JUN-81</HIREDATE>
  <SAL>2450</SAL>
  <DEPTNO>10</DEPTNO>
 </EMP>
 <EMP>
  <EMPNO>7788</EMPNO>
  <ENAME>SCOTT</ENAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE
>09-DEC-82</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
 </EMP>
 <EMP>
  <EMPNO>7839</EMPNO>
  <ENAME>KING</ENAME>
  <JOB>PRESIDENT</JOB>
  <HIREDATE>17-NOV-81</HIREDATE>
  <SAL>5000</SAL>
  <DE
PTNO>10</DEPTNO>
 </EMP>
 <EMP>
  <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>
 </EMP>
 <EMP>
  <EMPNO>7876</EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>12-JAN-83</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
 </EMP>
 <EMP>
  <EMPN
O>7900</EMPNO>
  <ENAME>JAMES</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7698</MGR>
  <HIREDATE>03-DEC-81</HIREDATE>
  <SAL>950</SAL>
  <DEPTNO>30</DEPTNO>
 </EMP>
 <EMP>
  <EMPNO>7902</EMPNO>
  <ENAME>FORD</E
NAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE>03-DEC-81</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
 </EMP>
 <EMP>
  <EMPNO>7934</EMPNO>
  <ENAME>MILLER</ENAME>
  <JOB>CLERK</JOB>
 
<MGR>7782</MGR>
  <HIREDATE>23-JAN-82</HIREDATE>
  <SAL>1300</SAL>
  <DEPTNO>10</DEPTNO>
 </EMP>
</EMP_DETAILS>
 
 

[ananthaisin]

I found it very useful .... coordination of oracle and xml is good.

[premkumar sp]

Hi
I Have a doubt.
These are my emp table records
EmpId EmpName
1 AAA
2 BBB
3 CCC

I want to generate xml in below format.
<emp>
<1>AAA</1>
<2>BBB</2>
<3>CCC</3>
</emp>

Can you kindly help me?

[Grant Ryan]

Thank you for this exampel it has helped me alot. Much appreciated.

[n3xus]

Thanks for your post. It helped me