Oracle PL/SQL

Generating XML from SQL and PL/SQL

By Jag - September 08, 2013
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.

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:
DECLARE
   l_refcursor SYS_REFCURSOR;
   l_xmltype XMLTYPE;
BEGIN
   OPEN l_refcursor FOR SELECT department_id
                             , department_name
                          FROM departments
                         WHERE department_id IN (10,20);

   l_xmltype := XMLTYPE(l_refcursor);
   dbms_output.put_line(l_xmltype.getClobVal);
END;
/
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
 </ROW>
 <ROW>
  <DEPARTMENT_ID>20</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
 </ROW>
</ROWSET>
As you can see it creates a simple XML document where each row is seperated with a ROW tag.

DBMS_XMLGEN

The DBMS_XMLGEN built-in is similar to the XMLTYPE constructor, but accepts a query directly:
DECLARE
   l_xmltype XMLTYPE;
BEGIN
    l_xmltype := dbms_xmlgen.getxmltype('SELECT department_id
                                              , department_name
                                           FROM departments
                                          WHERE department_id IN (10,20)'
                                       );

    dbms_output.put_line(l_xmltype.getClobVal);
END;
/
<ROWSET>
 <ROW>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
 </ROW>
 <ROW>
  <DEPARTMENT_ID>20</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
 </ROW>
</ROWSET>
But it also provides procedures to change the ROWSET and ROW tags.
DECLARE
   l_xmltype XMLTYPE;
   l_ctx dbms_xmlgen.ctxhandle;
BEGIN
   l_ctx := dbms_xmlgen.newcontext('SELECT department_id
                                         , department_name 
                                      FROM departments
                                     WHERE department_id 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;
/
<Departments>
 <Dept>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
 </Dept>
 <Dept>
  <DEPARTMENT_ID>20</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
 </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.
DECLARE
   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.department_id
                       , dept.department_name
                       , loc.city
                    FROM departments dept
                    JOIN locations loc
                      ON loc.location_id = dept.location_id
                   WHERE dept.department_id 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.department_id);
      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.department_name );
      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.city );
      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;
/
<Deptartments>
 <Dept DeptID="10">
  <Name>Administration</Name>
  <Location>Seattle</Location>
 </Dept>
 <Dept DeptID="20">
  <Name>Marketing</Name>
  <Location>Toronto</Location>
 </Dept>
</Deptartments>
In this second example I added the employees to each department. I created a new cursor loop on employees inside the department cursor loop, so that the employees of that department are added in a child node of the department. I also changed some code: I cast(dbms_xmldom.makeNode) the elements directly to a node, this way I need less variables:
DECLARE
   l_domdoc dbms_xmldom.DOMDocument;
   l_xmltype XMLTYPE;

   l_root_node dbms_xmldom.DOMNode;

   l_departments_node dbms_xmldom.DOMNode;

   l_dept_element dbms_xmldom.DOMElement;
   l_dept_node dbms_xmldom.DOMNode;

   l_name_node dbms_xmldom.DOMNode;
   l_name_textnode dbms_xmldom.DOMNode;

   l_location_node dbms_xmldom.DOMNode;
   l_location_textnode dbms_xmldom.DOMNode;

   l_employees_node dbms_xmldom.DOMNode;

   l_emp_element dbms_xmldom.DOMElement;
   l_emp_node dbms_xmldom.DOMNode;

   l_emp_first_name_node dbms_xmldom.DOMNode;
   l_emp_first_name_textnode dbms_xmldom.DOMNode;

   l_emp_last_name_node dbms_xmldom.DOMNode;
   l_emp_last_name_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_departments_node := dbms_xmldom.appendChild( l_root_node
                                                , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Deptartments' ))
                                                );

   FOR r_dept IN (SELECT dept.department_id
                       , dept.department_name
                       , loc.city
                    FROM departments dept
                    JOIN locations loc
                      ON loc.location_id = dept.location_id
                   WHERE dept.department_id 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, 'Deptno', r_dept.Department_Id );
      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_node := dbms_xmldom.appendChild( l_dept_node
                                            , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Name' ))
                                            );
      l_name_textnode := dbms_xmldom.appendChild( l_name_node
                                                , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_dept.department_name ))
                                                );

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

      -- For each department, add an Employees node
      l_employees_node := dbms_xmldom.appendChild( l_dept_node
                                                 , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Employees' ))
                                                 );

      FOR r_emp IN (SELECT employee_id
                         , first_name
                         , last_name
                      FROM employees
                     WHERE department_id = r_dept.department_id
                   )
      LOOP
         -- For each record, create a new Emp element with the Employee ID as attribute.
         -- and add this new Emp element to the Employees node
         l_emp_element := dbms_xmldom.createElement(l_domdoc, 'Emp' );
         dbms_xmldom.setAttribute(l_emp_element, 'empid', r_emp.employee_id );
         l_emp_node := dbms_xmldom.appendChild( l_employees_node
                                              , dbms_xmldom.makeNode(l_emp_element)
                                              );

         -- Each emp node will get a First name and Last name node which contains the first name and last name as text
         l_emp_first_name_node := dbms_xmldom.appendChild( l_emp_node
                                                         , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'FirstName' ))
                                                         );
         l_emp_first_name_textnode := dbms_xmldom.appendChild( l_emp_first_name_node
                                                             , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_emp.first_name ))
                                                             );

         l_emp_last_name_node := dbms_xmldom.appendChild( l_emp_node
                                                        , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'LastName' ))
                                                        );
         l_emp_last_name_textnode := dbms_xmldom.appendChild( l_emp_last_name_node
                                                            , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_emp.last_name ))
                                                            );
      END LOOP;
   END LOOP;

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

   dbms_output.put_line(l_xmltype.getClobVal);
END;
/
This is how the final example looks, with the employees working for the department added in the XML document:
<Deptartments>
 <Dept Deptno="10">
  <Name>Administration</Name>
  <Location>Seattle</Location>
  <Employees>
   <Emp empid="200">
    <FirstName>Jennifer</FirstName>
    <LastName>Whalen</LastName>
   </Emp>
  </Employees>
 </Dept>
 <Dept Deptno="20">
  <Name>Marketing</Name>
  <Location>Toronto</Location>
  <Employees>
   <Emp empid="201">
    <FirstName>Michael</FirstName>
    <LastName>Hartstein</LastName>
   </Emp>
   <Emp empid="202">
    <FirstName>Pat</FirstName>
    <LastName>Fay</LastName>
   </Emp>
  </Employees>
 </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).
  • Share:

You Might Also Like

0 comments