{"id":73273,"date":"2012-11-29T18:50:10","date_gmt":"2012-11-29T18:50:10","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/generating-xml-from-sql-and-plsql-part-2\/"},"modified":"2021-07-14T13:07:48","modified_gmt":"2021-07-14T13:07:48","slug":"generating-xml-from-sql-and-plsql-part-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/generating-xml-from-sql-and-plsql-part-2\/","title":{"rendered":"Generating XML from SQL and PL\/SQL \u2013 Part 2"},"content":{"rendered":"

In part one of “Generating XML from SQL and PL\/SQL<\/a>“, I explained how to generate XML from SQL. In this second part I\u2019ll show you how you can generate XML from table data in PL\/SQL.<\/p>\n

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.<\/p>\n

XMLTYPE<\/h2>\n

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\u2019re going to base our XML on table data, we\u2019re using a REF CURSOR.<\/p>\n

You can create a ref cursor and pass on this ref cursor to the XMLTYPE constructor like this:<\/p>\n

DECLARE\r\n   l_refcursor SYS_REFCURSOR;\r\n   l_xmltype XMLTYPE;\r\nBEGIN\r\n   OPEN l_refcursor FOR SELECT department_id\r\n                             , department_name\r\n                          FROM departments\r\n                         WHERE department_id IN (10,20);\r\n\r\n   l_xmltype := XMLTYPE(l_refcursor);\r\n   dbms_output.put_line(l_xmltype.getClobVal);\r\nEND;\r\n\/<\/pre>\n
<?xml version=\"1.0\"?>\r\n<ROWSET>\r\n <ROW>\r\n  <DEPARTMENT_ID>10<\/DEPARTMENT_ID>\r\n  <DEPARTMENT_NAME>Administration<\/DEPARTMENT_NAME>\r\n <\/ROW>\r\n <ROW>\r\n  <DEPARTMENT_ID>20<\/DEPARTMENT_ID>\r\n  <DEPARTMENT_NAME>Marketing<\/DEPARTMENT_NAME>\r\n <\/ROW>\r\n<\/ROWSET><\/pre>\n
As you can see it creates a simple XML document where each row is seperated with a ROW tag.<\/p>\n
DBMS_XMLGEN<\/h2>\n
The DBMS_XMLGEN built-in is similar to the XMLTYPE constructor, but accepts a query directly:<\/p>\n
DECLARE\r\n   l_xmltype XMLTYPE;\r\nBEGIN\r\n    l_xmltype := dbms_xmlgen.getxmltype('SELECT department_id\r\n                                              , department_name\r\n                                           FROM departments\r\n                                          WHERE department_id IN (10,20)'\r\n                                       );\r\n\r\n    dbms_output.put_line(l_xmltype.getClobVal);\r\nEND;\r\n\/<\/pre>\n
<ROWSET>\r\n <ROW>\r\n  <DEPARTMENT_ID>10<\/DEPARTMENT_ID>\r\n  <DEPARTMENT_NAME>Administration<\/DEPARTMENT_NAME>\r\n <\/ROW>\r\n <ROW>\r\n  <DEPARTMENT_ID>20<\/DEPARTMENT_ID>\r\n  <DEPARTMENT_NAME>Marketing<\/DEPARTMENT_NAME>\r\n <\/ROW>\r\n<\/ROWSET><\/pre>\n
But it also provides procedures to change the ROWSET and ROW tags.<\/p>\n
DECLARE\r\n   l_xmltype XMLTYPE;\r\n   l_ctx dbms_xmlgen.ctxhandle;\r\nBEGIN\r\n   l_ctx := dbms_xmlgen.newcontext('SELECT department_id\r\n                                         , department_name\u00a0\r\n                                      FROM departments\r\n                                     WHERE department_id in (10,20)'\r\n                                  );\r\n\r\n   dbms_xmlgen.setrowsettag(l_ctx, 'Departments');\u00a0\r\n   dbms_xmlgen.setrowtag(l_ctx, 'Dept');\r\n\r\n   l_xmltype := dbms_xmlgen.getXmlType(l_ctx) ;\r\n   dbms_xmlgen.closeContext(l_ctx);\r\n\r\n   dbms_output.put_line(l_xmltype.getClobVal);\r\nEnd;\r\n\/<\/pre>\n
<Departments>\r\n <Dept>\r\n  <DEPARTMENT_ID>10<\/DEPARTMENT_ID>\r\n  <DEPARTMENT_NAME>Administration<\/DEPARTMENT_NAME>\r\n <\/Dept>\r\n <Dept>\r\n  <DEPARTMENT_ID>20<\/DEPARTMENT_ID>\r\n  <DEPARTMENT_NAME>Marketing<\/DEPARTMENT_NAME>\r\n <\/Dept>\r\n<\/Departments><\/pre>\n
dbms_xmldom<\/h2>\n
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\u2019ll have to create the entire document by calling functions and procedures of the package.<\/p>\n
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.<\/p>\n
DECLARE\r\n   l_xmltype XMLTYPE;\r\n\r\n   l_domdoc dbms_xmldom.DOMDocument;\r\n\r\n   l_root_node dbms_xmldom.DOMNode;\r\n\r\n   l_department_element dbms_xmldom.DOMElement;\r\n   l_departments_node dbms_xmldom.DOMNode;\r\n\r\n   l_dept_element dbms_xmldom.DOMElement;\r\n   l_dept_node dbms_xmldom.DOMNode;\r\n\r\n   l_name_element dbms_xmldom.DOMElement;\r\n   l_name_node dbms_xmldom.DOMNode;\r\n   l_name_text dbms_xmldom.DOMText;\r\n   l_name_textnode dbms_xmldom.DOMNode;\r\n\r\n   l_location_element dbms_xmldom.DOMElement;\r\n   l_location_node dbms_xmldom.DOMNode;\r\n   l_location_text dbms_xmldom.DOMText;\r\n   l_location_textnode dbms_xmldom.DOMNode;\r\nBEGIN\r\n   -- Create an empty XML document\r\n   l_domdoc := dbms_xmldom.newDomDocument;\r\n\r\n   -- Create a root node\r\n   l_root_node := dbms_xmldom.makeNode(l_domdoc);\r\n\r\n   -- Create a new node Departments and add it to the root node\r\n   l_department_element := dbms_xmldom.createElement(l_domdoc, 'Deptartments' );\r\n   l_departments_node := dbms_xmldom.appendChild(l_root_node,dbms_xmldom.makeNode(l_department_element));\r\n\r\n   FOR r_dept IN (SELECT dept.department_id\r\n                       , dept.department_name\r\n                       , loc.city\r\n                    FROM departments dept\r\n                    JOIN locations loc\r\n                      ON loc.location_id = dept.location_id\r\n                   WHERE dept.department_id IN (10,20)\r\n                 )\r\n   LOOP\r\n      -- For each record, create a new Dept element with the Department ID as attribute.\r\n      -- and add this new Dept element to the Departments node\r\n      l_dept_element := dbms_xmldom.createElement(l_domdoc, 'Dept' );\r\n      dbms_xmldom.setAttribute(l_dept_element, 'DeptID', r_dept.department_id);\r\n      l_dept_node := dbms_xmldom.appendChild(l_departments_node,dbms_xmldom.makeNode(l_dept_element));\r\n\r\n      -- Each Dept node will get a Name node which contains the department name as text\r\n      l_name_element := dbms_xmldom.createElement(l_domdoc, 'Name' );\r\n      l_name_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_name_element));\r\n      l_name_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.department_name );\r\n      l_name_textnode := dbms_xmldom.appendChild(l_name_node,dbms_xmldom.makeNode(l_name_text));\r\n\r\n      -- Each Dept node will aslo get a Location node which contains the location(city) as text\r\n      l_location_element := dbms_xmldom.createElement(l_domdoc, 'Location' );\r\n      l_location_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_location_element));\r\n      l_location_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.city );\r\n      l_location_textnode := dbms_xmldom.appendChild(l_location_node,dbms_xmldom.makeNode(l_location_text));\r\n   END LOOP;\r\n\r\n   l_xmltype := dbms_xmldom.getXmlType(l_domdoc);\r\n   dbms_xmldom.freeDocument(l_domdoc);\r\n\r\n   dbms_output.put_line(l_xmltype.getClobVal);\r\nEND;\r\n\/<\/pre>\n
<Deptartments>\r\n <Dept DeptID=\"10\">\r\n  <Name>Administration<\/Name>\r\n  <Location>Seattle<\/Location>\r\n <\/Dept>\r\n <Dept DeptID=\"20\">\r\n  <Name>Marketing<\/Name>\r\n  <Location>Toronto<\/Location>\r\n <\/Dept>\r\n<\/Deptartments><\/pre>\n
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:<\/p>\n
DECLARE\r\n   l_domdoc dbms_xmldom.DOMDocument;\r\n   l_xmltype XMLTYPE;\r\n\r\n   l_root_node dbms_xmldom.DOMNode;\r\n\r\n   l_departments_node dbms_xmldom.DOMNode;\r\n\r\n   l_dept_element dbms_xmldom.DOMElement;\r\n   l_dept_node dbms_xmldom.DOMNode;\r\n\r\n   l_name_node dbms_xmldom.DOMNode;\r\n   l_name_textnode dbms_xmldom.DOMNode;\r\n\r\n   l_location_node dbms_xmldom.DOMNode;\r\n   l_location_textnode dbms_xmldom.DOMNode;\r\n\r\n   l_employees_node dbms_xmldom.DOMNode;\r\n\r\n   l_emp_element dbms_xmldom.DOMElement;\r\n   l_emp_node dbms_xmldom.DOMNode;\r\n\r\n   l_emp_first_name_node dbms_xmldom.DOMNode;\r\n   l_emp_first_name_textnode dbms_xmldom.DOMNode;\r\n\r\n   l_emp_last_name_node dbms_xmldom.DOMNode;\r\n   l_emp_last_name_textnode dbms_xmldom.DOMNode;\r\nBEGIN\r\n   -- Create an empty XML document\r\n   l_domdoc := dbms_xmldom.newDomDocument;\r\n\r\n   -- Create a root node\r\n   l_root_node := dbms_xmldom.makeNode(l_domdoc);\r\n\r\n   -- Create a new node Departments and add it to the root node\r\n   l_departments_node := dbms_xmldom.appendChild( l_root_node\r\n                                                , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Deptartments' ))\r\n                                                );\r\n\r\n   FOR r_dept IN (SELECT dept.department_id\r\n                       , dept.department_name\r\n                       , loc.city\r\n                    FROM departments dept\r\n                    JOIN locations loc\r\n                      ON loc.location_id = dept.location_id\r\n                   WHERE dept.department_id IN (10,20)\r\n                 )\r\n   LOOP\r\n      -- For each record, create a new Dept element with the Department ID as attribute.\r\n      -- and add this new Dept element to the Departments node\r\n      l_dept_element := dbms_xmldom.createElement(l_domdoc, 'Dept' );\r\n      dbms_xmldom.setAttribute(l_dept_element, 'Deptno', r_dept.Department_Id );\r\n      l_dept_node := dbms_xmldom.appendChild( l_departments_node\r\n                                            , dbms_xmldom.makeNode(l_dept_element)\r\n                                            );\r\n\r\n      -- Each Dept node will get a Name node which contains the department name as text\r\n      l_name_node := dbms_xmldom.appendChild( l_dept_node\r\n                                            , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Name' ))\r\n                                            );\r\n      l_name_textnode := dbms_xmldom.appendChild( l_name_node\r\n                                                , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_dept.department_name ))\r\n                                                );\r\n\r\n      -- Each Dept node will aslo get a Location node which contains the location(city) as text\r\n      l_location_node := dbms_xmldom.appendChild( l_dept_node\r\n                                                , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Location' ))\r\n                                                );\r\n      l_location_textnode := dbms_xmldom.appendChild( l_location_node\r\n                                                    , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_dept.city ))\r\n                                                    );\r\n\r\n      -- For each department, add an Employees node\r\n      l_employees_node := dbms_xmldom.appendChild( l_dept_node\r\n                                                 , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Employees' ))\r\n                                                 );\r\n\r\n      FOR r_emp IN (SELECT employee_id\r\n                         , first_name\r\n                         , last_name\r\n                      FROM employees\r\n                     WHERE department_id = r_dept.department_id\r\n                   )\r\n      LOOP\r\n         -- For each record, create a new Emp element with the Employee ID as attribute.\r\n         -- and add this new Emp element to the Employees node\r\n         l_emp_element := dbms_xmldom.createElement(l_domdoc, 'Emp' );\r\n         dbms_xmldom.setAttribute(l_emp_element, 'empid', r_emp.employee_id );\r\n         l_emp_node := dbms_xmldom.appendChild( l_employees_node\r\n                                              , dbms_xmldom.makeNode(l_emp_element)\r\n                                              );\r\n\r\n         -- Each emp node will get a First name and Last name node which contains the first name and last name as text\r\n         l_emp_first_name_node := dbms_xmldom.appendChild( l_emp_node\r\n                                                         , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'FirstName' ))\r\n                                                         );\r\n         l_emp_first_name_textnode := dbms_xmldom.appendChild( l_emp_first_name_node\r\n                                                             , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_emp.first_name ))\r\n                                                             );\r\n\r\n         l_emp_last_name_node := dbms_xmldom.appendChild( l_emp_node\r\n                                                        , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'LastName' ))\r\n                                                        );\r\n         l_emp_last_name_textnode := dbms_xmldom.appendChild( l_emp_last_name_node\r\n                                                            , dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_emp.last_name ))\r\n                                                            );\r\n      END LOOP;\r\n   END LOOP;\r\n\r\n   l_xmltype := dbms_xmldom.getXmlType(l_domdoc);\r\n   dbms_xmldom.freeDocument(l_domdoc);\r\n\r\n   dbms_output.put_line(l_xmltype.getClobVal);\r\nEND;\r\n\/<\/pre>\n
This is how the final example looks, with the employees working for the department added in the XML document:<\/p>\n
<Deptartments>\r\n <Dept Deptno=\"10\">\r\n  <Name>Administration<\/Name>\r\n  <Location>Seattle<\/Location>\r\n  <Employees>\r\n   <Emp empid=\"200\">\r\n    <FirstName>Jennifer<\/FirstName>\r\n    <LastName>Whalen<\/LastName>\r\n   <\/Emp>\r\n  <\/Employees>\r\n <\/Dept>\r\n <Dept Deptno=\"20\">\r\n  <Name>Marketing<\/Name>\r\n  <Location>Toronto<\/Location>\r\n  <Employees>\r\n   <Emp empid=\"201\">\r\n    <FirstName>Michael<\/FirstName>\r\n    <LastName>Hartstein<\/LastName>\r\n   <\/Emp>\r\n   <Emp empid=\"202\">\r\n    <FirstName>Pat<\/FirstName>\r\n    <LastName>Fay<\/LastName>\r\n   <\/Emp>\r\n  <\/Employees>\r\n <\/Dept>\r\n<\/Deptartments><\/pre>\n
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, \u2026), to search in XML documents, and to change documents (e.g. add nodes to an existing document).<\/p>\n
More info:<\/p>\n