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:

[code=oracle]

DECLARE
my_context NUMBER := 0;
sample_xml_outp ut VARCHAR2(32767) ;
data_size NUMBER := 0;
data_chunk NUMBER := 0;
offset NUMBER := 0;

BEGIN

-- Create a New Context

my_context:= DBMS_XMLGEN.NEW CONTEXT ('SELECT * FROM emp order by 1');
DBMS_OUTPUT.PUT _LINE('My New Context ID is:'||my_contex t);

-- Set the Root Element

DBMS_XMLGEN.SET ROWSETTAG(my_co ntext,'EMP_DETA ILS');

-- Set the Row Element

DBMS_XMLGEN.SET ROWTAG(my_conte xt,'EMP');

-- Generate XML Output

SELECT DBMS_XMLGEN.GET XML(my_context) INTO sample_xml_outp ut FROM DUAL;
data_chunk := LENGTH(sample_x ml_output);
offset := 0;
data_size := 200;

-- Print 200 Chunk of data on Screen

LOOP
DBMS_OUTPUT.PUT _LINE(SUBSTR(sa mple_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.CLO SECONTEXT(my_co ntext);

--Exception Block

EXCEPTION
WHEN OTHERS THEN
NULL; -- Add your exception handler message here
END;
/

[/code]

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

DBMS_XMLGEN.NEW CONTEXT

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.SET ROWSETTAG

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.SET ROWTAG

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.GET XML

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:

[code=oracle]

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>PRESIDEN T</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>

[/code]

[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]

Thank you

Thanks for your post. It helped me