Creating XML Data Using DBMS_XMLDOM

-------Script for generating XML data using DBMS_XMLDOM
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_empname_element dbms_xmldom.DOMElement;
l_empname_node dbms_xmldom.DOMNode;
l_empname_text dbms_xmldom.DOMText;
l_empname_textnode dbms_xmldom.DOMNode;  --
l_empno_element dbms_xmldom.DOMElement;
l_empno_node dbms_xmldom.DOMNode;
l_empno_text dbms_xmldom.DOMText;
l_empno_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, DEPARTMENT_DETAIL' );
l_departments_node := dbms_xmldom.appendChild(l_root_node,dbms_xmldom.makeNode(l_department_element));
--
FOR r_dept IN (SELECT a.deptno,
a.dname,
b.empno,
b.ename
FROM dept a, emp b
where a.deptno = b.deptno
and a.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, 'DEPARTMENT' );
dbms_xmldom.setAttribute(l_dept_element,'DEPARTMENT_ID', 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, 'DEPARTMENT_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 EMPLOYEE NAME NODE
l_empname_element := dbms_xmldom.createElement(l_domdoc, 'EMPLOYEE_NAME' );
l_empname_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_empname_element));
l_empname_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.ename );
l_empname_textnode := dbms_xmldom.appendChild(l_empname_node,dbms_xmldom.makeNode(l_empname_text));
--
-- Each Dept node will aslo get a EMPLOYEE NUMBER NODE
l_empno_element := dbms_xmldom.createElement(l_domdoc, 'EMPLOYEE_NUMBER' );
l_empno_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_empno_element));
l_empno_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.empno );
l_empno_textnode := dbms_xmldom.appendChild(l_empno_node,dbms_xmldom.makeNode(l_empno_text));
END LOOP;
--
l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
dbms_xmldom.freeDocument(l_domdoc);
dbms_output.put_line(l_xmltype.getClobVal);

--

END

;　
=====================Output===============================================================

<DEPARTMENT_DETAIL>

<DEPARTMENT DEPARTMENT_ID="10">
<DEPARTMENT_NAME>ACCOUNTING</DEPARTMENT_NAME>
<EMPLOYEE_NAME>KING</EMPLOYEE_NAME>
<EMPLOYEE_NUMBER>7839</EMPLOYEE_NUMBER>
</DEPARTMENT>

<DEPARTMENT DEPARTMENT_ID="10">
<DEPARTMENT_NAME>ACCOUNTING</DEPARTMENT_NAME>
<EMPLOYEE_NAME>CLARK</EMPLOYEE_NAME>
<EMPLOYEE_NUMBER>7782</EMPLOYEE_NUMBER>
</DEPARTMENT>

<DEPARTMENT DEPARTMENT_ID="20">
<DEPARTMENT_NAME>RESEARCH</DEPARTMENT_NAME>
<EMPLOYEE_NAME>JONES</EMPLOYEE_NAME>
<EMPLOYEE_NUMBER>7566</EMPLOYEE_NUMBER>
</DEPARTMENT>

<DEPARTMENT DEPARTMENT_ID="20">
<DEPARTMENT_NAME>RESEARCH</DEPARTMENT_NAME>
<EMPLOYEE_NAME>SCOTT</EMPLOYEE_NAME>
<EMPLOYEE_NUMBER>7788</EMPLOYEE_NUMBER>
</DEPARTMENT>

<DEPARTMENT DEPARTMENT_ID="20">
<DEPARTMENT_NAME>RESEARCH</DEPARTMENT_NAME>
<EMPLOYEE_NAME>FORD</EMPLOYEE_NAME>
<EMPLOYEE_NUMBER>7902</EMPLOYEE_NUMBER>
</DEPARTMENT>

<DEPARTMENT DEPARTMENT_ID="20">
<DEPARTMENT_NAME>RESEARCH</DEPARTMENT_NAME>
<EMPLOYEE_NAME>SMITH</EMPLOYEE_NAME>
<EMPLOYEE_NUMBER>7369</EMPLOYEE_NUMBER>
</DEPARTMENT>

<DEPARTMENT DEPARTMENT_ID="20">
<DEPARTMENT_NAME>RESEARCH</DEPARTMENT_NAME>
<EMPLOYEE_NAME>ADAMS</EMPLOYEE_NAME>
<EMPLOYEE_NUMBER>7876</EMPLOYEE_NUMBER>
</DEPARTMENT>

<DEPARTMENT DEPARTMENT_ID="10">
<DEPARTMENT_NAME>ACCOUNTING</DEPARTMENT_NAME>
<EMPLOYEE_NAME>MILLER</EMPLOYEE_NAME>
<EMPLOYEE_NUMBER>7934</EMPLOYEE_NUMBER>
</DEPARTMENT>

</DEPARTMENT_DETAIL>