By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,166 Members | 1,130 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Data Fetch in XML Structure using DBMS_XMLGEN

amitpatel66
Expert 100+
P: 2,367
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. my_context NUMBER := 0;
  4. sample_xml_output VARCHAR2(32767);
  5. data_size NUMBER := 0;
  6. data_chunk NUMBER := 0;
  7. offset NUMBER := 0;
  8.  
  9. BEGIN
  10.  
  11. -- Create a New Context
  12.  
  13. my_context:= DBMS_XMLGEN.NEWCONTEXT ('SELECT * FROM emp order by 1');
  14. DBMS_OUTPUT.PUT_LINE('My New Context ID is:'||my_context);
  15.  
  16. -- Set the Root Element
  17.  
  18. DBMS_XMLGEN.SETROWSETTAG(my_context,'EMP_DETAILS');
  19.  
  20. -- Set the Row Element
  21.  
  22. DBMS_XMLGEN.SETROWTAG(my_context,'EMP');
  23.  
  24. -- Generate XML Output
  25.  
  26. SELECT DBMS_XMLGEN.GETXML(my_context) INTO sample_xml_output FROM DUAL;
  27. data_chunk := LENGTH(sample_xml_output);
  28. offset := 0;
  29. data_size := 200;
  30.  
  31. -- Print 200 Chunk of data on Screen
  32.  
  33. LOOP
  34. DBMS_OUTPUT.PUT_LINE(SUBSTR(sample_xml_output,offset + 1,data_size));
  35. offset:= offset + data_size;
  36. data_size := LEAST(200,data_chunk - data_size);
  37. data_chunk := data_chunk - data_size;
  38. EXIT WHEN data_chunk <= 0;
  39. END LOOP;
  40.  
  41. -- Close the Context
  42.  
  43. DBMS_XMLGEN.CLOSECONTEXT(my_context);
  44.  
  45. --Exception Block
  46.  
  47. EXCEPTION
  48. WHEN OTHERS THEN
  49. NULL; -- Add your exception handler message here
  50. END;
  51. /
  52.  
  53.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. My New Context ID is:44
  3. <?xml version="1.0"?>
  4. <EMP_DETAILS>
  5.  <EMP>
  6.   <EMPNO>7369</EMPNO>
  7.   <ENAME>SMITH</ENAME>
  8.   <JOB>CLERK</JOB>
  9.   <MGR>7902</MGR>
  10.   <HIREDATE>17-DEC-80</HIREDATE>
  11.   <SAL>800</SAL>
  12.   <DEPTNO>20</DEPTNO>
  13.  </
  14. EMP>
  15.  <EMP>
  16.   <EMPNO>7499</EMPNO>
  17.   <ENAME>ALLEN</ENAME>
  18.   <JOB>SALESMAN</JOB>
  19.   <MGR>7698</MGR>
  20.   <HIREDATE>20-FEB-81</HIREDATE>
  21.   <SAL>1600</SAL>
  22.   <COMM>300</COMM>
  23.   <DEPTNO>30</DEPTNO>
  24.  </EMP>
  25.  <E
  26. MP>
  27.   <EMPNO>7521</EMPNO>
  28.   <ENAME>WARD</ENAME>
  29.   <JOB>SALESMAN</JOB>
  30.   <MGR>7698</MGR>
  31.   <HIREDATE>22-FEB-81</HIREDATE>
  32.   <SAL>1250</SAL>
  33.   <COMM>500</COMM>
  34.   <DEPTNO>30</DEPTNO>
  35.  </EMP>
  36.  <EMP>
  37.   <EM
  38. PNO>7566</EMPNO>
  39.   <ENAME>JONES</ENAME>
  40.   <JOB>MANAGER</JOB>
  41.   <MGR>7839</MGR>
  42.   <HIREDATE>02-APR-81</HIREDATE>
  43.   <SAL>2975</SAL>
  44.   <DEPTNO>20</DEPTNO>
  45.  </EMP>
  46.  <EMP>
  47.   <EMPNO>7654</EMPNO>
  48.   <ENAME>MA
  49. RTIN</ENAME>
  50.   <JOB>SALESMAN</JOB>
  51.   <MGR>7698</MGR>
  52.   <HIREDATE>28-SEP-81</HIREDATE>
  53.   <SAL>1250</SAL>
  54.   <COMM>1400</COMM>
  55.   <DEPTNO>30</DEPTNO>
  56.  </EMP>
  57.  <EMP>
  58.   <EMPNO>7698</EMPNO>
  59.   <ENAME>BLAKE</E
  60. NAME>
  61.   <JOB>MANAGER</JOB>
  62.   <MGR>7839</MGR>
  63.   <HIREDATE>01-MAY-81</HIREDATE>
  64.   <SAL>2850</SAL>
  65.   <DEPTNO>30</DEPTNO>
  66.  </EMP>
  67.  <EMP>
  68.   <EMPNO>7782</EMPNO>
  69.   <ENAME>CLARK</ENAME>
  70.   <JOB>MANAGER</JOB>
  71.  
  72. <MGR>7839</MGR>
  73.   <HIREDATE>09-JUN-81</HIREDATE>
  74.   <SAL>2450</SAL>
  75.   <DEPTNO>10</DEPTNO>
  76.  </EMP>
  77.  <EMP>
  78.   <EMPNO>7788</EMPNO>
  79.   <ENAME>SCOTT</ENAME>
  80.   <JOB>ANALYST</JOB>
  81.   <MGR>7566</MGR>
  82.   <HIREDATE
  83. >09-DEC-82</HIREDATE>
  84.   <SAL>3000</SAL>
  85.   <DEPTNO>20</DEPTNO>
  86.  </EMP>
  87.  <EMP>
  88.   <EMPNO>7839</EMPNO>
  89.   <ENAME>KING</ENAME>
  90.   <JOB>PRESIDENT</JOB>
  91.   <HIREDATE>17-NOV-81</HIREDATE>
  92.   <SAL>5000</SAL>
  93.   <DE
  94. PTNO>10</DEPTNO>
  95.  </EMP>
  96.  <EMP>
  97.   <EMPNO>7844</EMPNO>
  98.   <ENAME>TURNER</ENAME>
  99.   <JOB>SALESMAN</JOB>
  100.   <MGR>7698</MGR>
  101.   <HIREDATE>08-SEP-81</HIREDATE>
  102.   <SAL>1500</SAL>
  103.   <COMM>0</COMM>
  104.   <DEPTNO>30</
  105. DEPTNO>
  106.  </EMP>
  107.  <EMP>
  108.   <EMPNO>7876</EMPNO>
  109.   <ENAME>ADAMS</ENAME>
  110.   <JOB>CLERK</JOB>
  111.   <MGR>7788</MGR>
  112.   <HIREDATE>12-JAN-83</HIREDATE>
  113.   <SAL>1100</SAL>
  114.   <DEPTNO>20</DEPTNO>
  115.  </EMP>
  116.  <EMP>
  117.   <EMPN
  118. O>7900</EMPNO>
  119.   <ENAME>JAMES</ENAME>
  120.   <JOB>CLERK</JOB>
  121.   <MGR>7698</MGR>
  122.   <HIREDATE>03-DEC-81</HIREDATE>
  123.   <SAL>950</SAL>
  124.   <DEPTNO>30</DEPTNO>
  125.  </EMP>
  126.  <EMP>
  127.   <EMPNO>7902</EMPNO>
  128.   <ENAME>FORD</E
  129. NAME>
  130.   <JOB>ANALYST</JOB>
  131.   <MGR>7566</MGR>
  132.   <HIREDATE>03-DEC-81</HIREDATE>
  133.   <SAL>3000</SAL>
  134.   <DEPTNO>20</DEPTNO>
  135.  </EMP>
  136.  <EMP>
  137.   <EMPNO>7934</EMPNO>
  138.   <ENAME>MILLER</ENAME>
  139.   <JOB>CLERK</JOB>
  140.  
  141. <MGR>7782</MGR>
  142.   <HIREDATE>23-JAN-82</HIREDATE>
  143.   <SAL>1300</SAL>
  144.   <DEPTNO>10</DEPTNO>
  145.  </EMP>
  146. </EMP_DETAILS>
  147.  
  148.  
Dec 30 '09 #1
Share this Article
Share on Google+
4 Comments


P: 15
I found it very useful .... coordination of oracle and xml is good.
Dec 31 '09 #2

P: 4
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?
Nov 17 '10 #3

P: 1
Thank you for this exampel it has helped me alot. Much appreciated.
Jun 22 '11 #4

P: 1
Thanks for your post. It helped me
Apr 4 '12 #5