Managing XML data in Oracle database - using SQL and PL/SQL

Managing XML data in Oracle database

1) Insert data into table from XML
2) Generating XML from SQL and PL/SQL 

The datatype to hold XML in PL/SQL or in the database is XMLTYPE. So you can use the generated XML in PL/SQL, store it in table (XMLTYPE column), transform it to a CLOB (using the XMLTYPE getClobVal member function which I use in the examples) and write it to a file.

XML Conversion methods:

-- Create below tables to test scenarios

create table LOCATIONS
(
  location_id   NUMBER not null,
  location_name VARCHAR2(30)
);
alter table LOCATIONS add primary key (LOCATION_ID) using index ;

-- create the parent table DEPT

create table dept
(deptno number(3),
dname varchar2(20) not null,
location_id NUMBER,
constraint pk_deptno_dept primary key(deptno)
);

-- create the child table EMP

create table emp
(empno number(10), ename varchar2(50) not null,
sal number(10), deptno number(3),
constraint pk_empno_emp primary key(empno),
constraint fk_deptno_emp foreign key(deptno) references dept(deptno)
);

-- Insert records sample records
insert into LOCATIONS values(101,'Mumbai');
insert into LOCATIONS values(102,'Chennai');
commit;

insert into dept values(10,'Finance',101);
insert into dept values(20,'HR',104);
insert into dept values(30,'IT',106);
insert into dept values(40,'Audit',102);
commit;

insert into emp values (1001,'Gouranga',1000,30);
insert into emp values (1002,'Chowdari',9000,10);
insert into emp values (1003,'Ashok',9999,30);
insert into emp values (1004,'Chandra',8888,10);
insert into emp values (1005,'Gouranga',2000,10);
insert into emp values (1006,'Dinesh',3000,20);
insert into emp values (1007,'Somaiha',1000,20);
commit;


-- Create procedure to post all employees info to cursor

create or replace procedure p_show_employee
(oCursor_getemployee OUT sys_refcursor)
AS

BEGIN
  open oCursor_getemployee for
  select empno,ename,sal from emp;
END;
/


-- Create procedure to post data into cursor with parameterised. Procedure should optional conditions


create or replace procedure p_get_employee
(in_empno            IN emp.empno%TYPE,
in_deptno           IN emp.deptno%TYPE,
oCursor_getemployee OUT sys_refcursor) 
AS

BEGIN
  open oCursor_getemployee for
    select empno, ename, sal
      from emp e
     where e.empno = in_empno
       and (in_deptno IS NULL OR in_deptno = e.deptno);
END;
/


-- Sample XML format

<EMPLOYEE>
<EMP EMPNO="901" ENAME="XML_ONE" SAL="9999" DEPTNO="10"/>
<EMP EMPNO="902" ENAME="XML_TWO" SAL="8888" DEPTNO="20"/>
<EMP EMPNO="903" ENAME="XML_THREE" SAL="7777" DEPTNO="20"/>
</EMPLOYEE>

-- date sample

tDATE := to_date(lx_HostelDetails.extract('HostelDetails/@EndDate').getstringval(),'mm-dd-yyyy H24:MI:SS')

Note: XML types are case sensitive


-- show table value in XML format using SQL query

select xmlelement("Employee",
xmlattributes(e.empno AS "EMPID",e.ename As "EMPNAME",
e.sal as "SALARY")) As Result
from app.emp e 
where e.deptno=10;

XMLTYPE :

The easiest way to create an XML document, is using the constructor of XMLTYPE. This constructor can have several datatypes as input, like a CLOB and VARCHAR2, but as we’re going to base our XML on table data, we’re using a REF CURSOR.

You can create a ref cursor and pass on this ref cursor to the XMLTYPE constructor like this:

1) Insert data into table from XML 
-- Create a procedure to post data from XML input:

create or replace procedure p_post_employee(iClob_emp IN CLOB) 
IS
  v_xml_emp xmltype;
BEGIN
  v_xml_emp := xmltype(iClob_emp);
  insert into emp
    (empno, ename, sal, deptno)
  values
    (extractvalue(v_xml_emp, '/EMPLOYEE/EMP/@EMPNO'),
     extractvalue(v_xml_emp, '/EMPLOYEE/EMP/@ENAME'),
     extractvalue(v_xml_emp, '/EMPLOYEE/EMP/@SAL'),
     extractvalue(v_xml_emp, '/EMPLOYEE/EMP/@DEPTNO'));

END;
/

OR

create or replace procedure p_post_employee
(iClob_emp IN CLOB)
IS
 v_xml_emp xmltype;
BEGIN
  v_xml_emp:=xmltype(iClob_emp);
 insert into emp(empno,ename,sal,deptno)
 values
 (
 extract(v_xml_emp,'EMPLOYEE/EMP/@EMPNO').getnumberval(),
 extract(v_xml_emp,'EMPLOYEE/EMP/@ENAME').getstringval(),
 extract(v_xml_emp,'EMPLOYEE/EMP/@SAL').getnumberval(),
 extract(v_xml_emp,'EMPLOYEE/EMP/@DEPTNO').getnumberval()
 );
end;
/


During test input the below values:

<EMPLOYEE>
<EMP EMPNO="901" ENAME="XML_ONE" SAL="9999" DEPTNO="10"/>
</EMPLOYEE>

2) Generating XML from SQL and PL/SQL 

XMLTYPE:

The easiest way to create an XML document, is using the constructor of XMLTYPE. This constructor can have several datatypes as input, like a CLOB and VARCHAR2, but as we’re going to base our XML on table data, we’re using a REF CURSOR.

You can create a ref cursor and pass on this ref cursor to the XMLTYPE constructor like this:

create or replace procedure p_get_empdetails_xml
(o_refcursor OUT SYS_REFCURSOR)
IS
   l_xmltype XMLTYPE;
BEGIN
   OPEN o_refcursor 
   FOR  select deptno,dname FROM dept WHERE deptno IN(10,20);

   l_xmltype := XMLTYPE(o_refcursor);
   dbms_output.put_line(l_xmltype.getClobVal);
END;
/

output:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>Finance</DNAME>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>HR</DNAME>
 </ROW>
</ROWSET>


DBMS_XMLGEN:

The DBMS_XMLGEN built-in is similar to the XMLTYPE constructor, but accepts a query directly:

-- Direct display

create or replace procedure p_get_empdetails_xml
IS
  l_xmltype XMLTYPE;
BEGIN
  l_xmltype := dbms_xmlgen.getxmltype('SELECT deptno,dname FROM dept WHERE deptno IN(10,20)');

  dbms_output.put_line(l_xmltype.getClobVal);
END;
/

Output:

<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>Finance</DNAME>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>HR</DNAME>
 </ROW>
</ROWSET>

But it also provides procedures to change the ROWSET and ROW tags.

create or replace procedure p_get_empdetails_xml
IS
   l_xmltype XMLTYPE;
   l_ctx dbms_xmlgen.ctxhandle;
BEGIN
   l_ctx := dbms_xmlgen.newcontext('SELECT deptno,dname FROM dept WHERE deptno IN(10,20)');

   dbms_xmlgen.setrowsettag(l_ctx, 'Departments'); 
   dbms_xmlgen.setrowtag(l_ctx, 'Dept');

   l_xmltype := dbms_xmlgen.getXmlType(l_ctx) ;
   dbms_xmlgen.closeContext(l_ctx);

   dbms_output.put_line(l_xmltype.getClobVal);
End;
/

output:

<Departments>
 <Dept>
  <DEPTNO>10</DEPTNO>
  <DNAME>Finance</DNAME>
 </Dept>
 <Dept>
  <DEPTNO>20</DEPTNO>
  <DNAME>HR</DNAME>
 </Dept>
</Departments>

dbms_xmldom:

With the XMLTYPE constructor and DBMS_XMLGEN package, you can create simple XML documents, fast and easy. When you need to create more advanced XML documents or want to have more control on how your XML document looks like, DBMS_XMLDOM can be used. The DBMS_XMLDOM package is a bit more complicated as you’ll have to create the entire document by calling functions and procedures of the package.

The following example creates an XML document with the department information retrieved from the query. In short, this is how it works: create new elements and add them as a (child) node.

create or replace procedure p_get_empdetails_xml
IS
   l_xmltype XMLTYPE;

   l_domdoc dbms_xmldom.DOMDocument;

   l_root_node dbms_xmldom.DOMNode;

   l_department_element dbms_xmldom.DOMElement;
   l_departments_node dbms_xmldom.DOMNode;

   l_dept_element dbms_xmldom.DOMElement;
   l_dept_node dbms_xmldom.DOMNode;

   l_name_element dbms_xmldom.DOMElement;
   l_name_node dbms_xmldom.DOMNode;
   l_name_text dbms_xmldom.DOMText;
   l_name_textnode dbms_xmldom.DOMNode;

   l_location_element dbms_xmldom.DOMElement;
   l_location_node dbms_xmldom.DOMNode;
   l_location_text dbms_xmldom.DOMText;
   l_location_textnode dbms_xmldom.DOMNode;

BEGIN
   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;

   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);

   -- Create a new node Departments and add it to the root node
   l_department_element := dbms_xmldom.createElement(l_domdoc, 'Deptartments' );
   l_departments_node := dbms_xmldom.appendChild(l_root_node,dbms_xmldom.makeNode(l_department_element));

   FOR r_dept IN (SELECT dept.deptno
                       , dept.dname, loc.location_name
                    FROM dept dept
                    JOIN locations loc
                      ON loc.location_id = dept.location_id
                   WHERE dept.deptno IN (10,20)
                 )
   LOOP
      -- For each record, create a new Dept element with the Department ID as attribute.
      -- and add this new Dept element to the Departments node
      l_dept_element := dbms_xmldom.createElement(l_domdoc, 'Dept' );
      dbms_xmldom.setAttribute(l_dept_element, 'DeptID', r_dept.deptno);
      l_dept_node := dbms_xmldom.appendChild(l_departments_node,dbms_xmldom.makeNode(l_dept_element));

      -- Each Dept node will get a Name node which contains the department name as text
      l_name_element := dbms_xmldom.createElement(l_domdoc, 'Name' );
      l_name_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_name_element));
      l_name_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.dname );
      l_name_textnode := dbms_xmldom.appendChild(l_name_node,dbms_xmldom.makeNode(l_name_text));

      -- Each Dept node will aslo get a Location node which contains the location(city) as text
      l_location_element := dbms_xmldom.createElement(l_domdoc, 'Location' );
      l_location_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_location_element));
      l_location_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.location_name );
      l_location_textnode := dbms_xmldom.appendChild(l_location_node,dbms_xmldom.makeNode(l_location_text));
   END LOOP;

   l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
   dbms_xmldom.freeDocument(l_domdoc);

   dbms_output.put_line(l_xmltype.getClobVal);
END;
/

output:

<Deptartments>
  <Dept DeptID="10">
    <Name>Finance</Name>
    <Location>Mumbai</Location>
  </Dept>
</Deptartments>

As you can see DBMS_XMLDOM is the most advanced way to create XML documents from table data, but it can also be used to create a document from different sources (query, CLOB, …), to search in XML documents, and to change documents (e.g. add nodes to an existing document).

More information see below documents on topic like:

•Oracle Documentation on XMLTYPE
•Oracle Documentation on DBMS_XMLGEN
•Oracle Documentation on DBMS_XMLDOM

Be careful on following:

• getClobVal is a deprecated operator and should be avoided in favor or XMLSerialize

• DBMS_XMLGEN is not really maintained anymore by the XMLDB dev team and, IMHO, is only useful for some encoding/decoding functionality it still has and sometimes still would be a valid reason to use it. If not only debugging statements fed into DBMS_XMLGEN can be a xxxx in the xxx and if unlucky can not be optimized by the CBO because its treated as a string and not XML/SQL and/or...

• DBMS_XMLDOM is still a valid way of doing stuff in the PL/SQL realm of things although the XMLDB Dev team strongly advices to use the XMLELEMENT, XMLFOREST, etc functions