Home > Database, Oracle > XML representation of data in your table via using “dbms_xmlgen” package

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  ;

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 department_id
  2       , department_name
  3       , cursor(
  4                 select first_name
  5                   from employees t
  6                  where t.department_id = d.department_id
  7           ) employees
  8    from departments d
  9    where rownum = 1','DEPARTMENTS') xmldata
 10    from dual
 11  ;

XMLDATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<DEPARTMENTS>
 <ROW>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
  <EMPLOYEES>
   <EMPLOYEES_ROW>
    <FIRST_NAME>Jennifer</FIRST_NAME>
   </EMPLOYEES_ROW>
  </EMPLOYEES>
 </ROW>
</DEPARTMENTS>

—-2—

To eliminate remaining opening and closing tags in the case when “detail”(the cursor) is null, lets rewrite our query as follows, for example:

SELECT XMLElement("DEPARTMENTS"
                 , XMLAgg( XMLElement( "ROW"
                                     , XMLForest( t.department_id
                                                , t.department_name
                                                )
                                     , (
                                         SELECT XMLAgg(XMLElement("EMPLOYEES"
                                                                 , XMLForest (q.first_name)
                                                                  )
                                                        )
                                           FROM employees q
                                          WHERE q.department_id = t.department_id
                                            --and 100=101
                                        )
                                     )
                          )
                 )

In order to have more control over tags. And store result of the above query in a file as follows: Before running the below code a directory have to be created: Create directory <name> as <path>. Put the name if your directory instead of XMLDIR.

declare
  l_xml clob;

begin
  SELECT XMLElement("DEPARTMENTS"
                 , XMLAgg( XMLElement( "ROW"
                                     , XMLForest( t.department_id
                                                , t.department_name
                                                )
                                     , (
                                         SELECT XMLAgg(XMLElement("EMPLOYEES"
                                                                 , XMLForest (q.first_name)
                                                                  )
                                                        )
                                           FROM employees q
                                          WHERE q.department_id = t.department_id
                                            --and 100=101
                                        )
                                     )
                          )
                 ).getclobval() into l_xml
  FROM departments t
  where rownum < 3;

  dbms_xslprocessor.clob2file(l_xml, 'XMLDIR', 'XmlFile.xml');

end;

Result#1: When sub-query returns data

 <DEPARTMENTS>
  <ROW>
    <DEPARTMENT_ID>10</DEPARTMENT_ID> 
    <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME> 
    <EMPLOYEES>
      <FIRST_NAME>Jennifer</FIRST_NAME> 
    </EMPLOYEES>
  </ROW>
  <ROW>
    <DEPARTMENT_ID>20</DEPARTMENT_ID> 
    <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME> 
    <EMPLOYEES>
      <FIRST_NAME>Michael</FIRST_NAME> 
    </EMPLOYEES>
    <EMPLOYEES>
      <FIRST_NAME>Pat</FIRST_NAME> 
    </EMPLOYEES>
  </ROW>
 </DEPARTMENTS>

Result#2: When sub-query returns no data

<DEPARTMENTS>
  <ROW>
    <DEPARTMENT_ID>10</DEPARTMENT_ID> 
    <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME> 
  </ROW>
  <ROW>
    <DEPARTMENT_ID>20</DEPARTMENT_ID> 
    <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME> 
  </ROW>
  </DEPARTMENTS>
Advertisements
Categories: Database, Oracle
  1. April 29, 2013 at 2:01 am
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: