DECLARE
l_refcursor SYS_REFCURSOR;
l_xmltype XMLTYPE;
BEGIN
OPEN l_refcursor FOR
SELECT deptno, dname
FROM dept
WHERE deptno IN (10, 20);
l_xmltype := XMLTYPE (l_refcursor);
DBMS_OUTPUT.put_line (l_xmltype.getClobVal);
END;
/
--===============================================================
Example2:
DECLARE
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;
/
--================================================================
Example3:
DECLARE
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;
/
--================================================================
Example4: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;
/
--================================================================
Example5:In this example added the employees to each department. 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. Also changed some code: cast(dbms_xmldom.makeNode) the elements directly to a node, this way 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_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;
/
http://www.oracleerpfunctional.com/2013/09/generating-xml-from-sql-and-plsql.html
