Generate XML Output using SQL & PL/SQL

Example1:
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_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

No comments:

Post a Comment