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


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  ;

<?xml version="1.0"?>


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