469,301 Members | 2,272 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Data Fetch in XML Structure using DBMS_XMLGEN

amitpatel66
2,367 Expert 2GB
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
4 9281
I found it very useful .... coordination of oracle and xml is good.
Dec 31 '09 #2
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
Thank you for this exampel it has helped me alot. Much appreciated.
Jun 22 '11 #4
n3xus
1
Thanks for your post. It helped me
Apr 4 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Santah | last post: by
1 post views Thread by Da-Breegster | last post: by
8 posts views Thread by rpsetzer | last post: by
6 posts views Thread by rakesh19 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.