XML representation of data in your table via using “dbms_xmlgen” package


srcnblgc

If you want get an XML representation of data in your table then take a look at dbms_xmlgen package. For example, you can write a simple function like this one:

SQL> create or replace function GenXML(p_query in varchar2, p_RSetTag in varchar2)
  2  return clob
  3  is
  4    l_xmlcntx dbms_xmlgen.ctxHandle;
  5    l_resxml clob;
  6  begin
  7    l_xmlcntx := dbms_xmlgen.newContext(p_query);
  8    dbms_xmlgen.setRowSetTag(l_xmlcntx,  p_RSetTag);
  9    l_resxml := dbms_xmlgen.getXML(l_xmlcntx);
 10    dbms_xmlgen.closeContext(l_xmlcntx);
 11    return l_resxml;
 12  end;
 13  /

Function created

And then use it as follows by passing your query to a table and a rowset tag as parameters.

SQL> select genxml('select * from employees where rownum = 1','EMPLOYEES') as XmlData
  2    from dual
  3  ;

XMLDATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<EMPLOYEES>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>100</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <EMAIL>SKING</EMAIL>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
  <HIRE_DATE>17-JUN-03</HIRE_DATE>
  <JOB_ID>AD_PRES</JOB_ID>
  <SALARY>24000</SALARY>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
</EMPLOYEES>

SQL> 

As Further Information

To display a master-detail type of datastructure you can use cursor. For example:

SQL> select genxml('select…

View original post 250 more words