Publishing of SQL data as XML

You can use the XMLElement(), XMLConcat(), XMLAgg(), and XMLAttributes() functions within an SQL expression to transform the results of a database query into XML.

These XML functions are often called publishing functions because the goal is to convert data that is stored in a relational database into XML that can be made available to other applications, for example, web services. The main function in this regard is XMLElement(), which takes two arguments: the name of the XML element to create and the content of that element. The following select statement, which does not actually query a database, highlights the use of the XMLElement() function:

select XMLElement('Parent', 'Parent Text');

This statement creates the following XML:

<Parent>Parent Text</Parent>

The output from the XMLElement() function is a value of type XML, which is the Netezza Performance Server compiled representation of the XML element. So if you typed the preceding select statement, the type name XML would be returned:

 XMLELEMENT
-----------
 XML
(1 row)

To see the actual XML element that the XMLElement() call creates, you must wrap the XMLElement() call with the XMLSerialize() function. An example follows:

select XMLSerialize(XMLElement('Parent', 'Parent Text'));
         XMLSERIALIZE
------------------------------
 <Parent>Parent Text</Parent>
(1 row)

The real power of the XMLElement() function is that you can nest the function calls to produce the hierarchical structure that is required for XML data. An example follows:

 select
      XMLElement('Parent',
      XMLElement('Child', 'Child text'));

This query produces the following XML:

<Parent>
   <Child>Child text</Child>
</Parent>

You can nest the publishing functions as required, up to a limit of 10,000 nested calls. An example follows:

select
      XMLElement('Parent',
      XMLElement('Child',
      XMLElement('GrandChild', 'Grandchild text')));

This query produces the following XML:

<Parent>
   <Child>
      <GrandChild>Grandchild text</GrandChild>
   </Child>
</Parent>

As a more realistic example, suppose that there is a DEPARTMENTS table that contains three columns: DEPTNO, DEPTNAME, and DEPTLOC:

   DEPTNO  DEPTNAME    DEPTLOC
   ------  ----------  ---------
   10      MARKETING   BOSTON
   20      HR          BOSTON
   30      SALES       NEW YORK
   40      ENGINEERING NEW YORK

The following plain SQL query lists all departments:

select * from departments;

But suppose that you needed to return all four rows of department data as XML, where there is one <Dept> node for each department and each <Dept> node contains three child nodes (<Number>, <Name>, and <Location>), as shown in the following XML document:

<Departments>
      <Dept>
         <Number>10</Number>
         <Name>MARKETING</Name>
         <Location>BOSTON</Location>
      </Dept>
      <Dept>
         <Number>20</Number>
         <Name>HR</Name>
         <Location>BOSTON</Location>
      </Dept>
      <Dept>
         <Number>30</Number>
         <Name>SALES</Name>
         <Location>NEW YORK</Location>
      </Dept>
      <Dept>
         <Number>40</Number>
         <Name>ENGINEERING</Name>
         <Location>NEW YORK</Location>
      </Dept>
</Departments>

To create this XML document, you would use a select statement that is modeled after the following one:

select
      XMLElement('Departments', XMLAGG(
         XMLElement('Dept', XMLConcat(
         XMLElement('Number', d.deptno),
         XMLElement('Name', d.deptname),
         XMLElement('Location', d.deptloc)))))
from departments d;

In each of the first two XMLElement() calls, the content of the element is created by a nested XML function call. To create a hierarchically structured XML document of parent and child nodes, you nest the XMLElement() calls within an SQL statement.

The first XMLElement() function in the query creates the top-level <Departments> node:

XMLElement('Departments', XMLAgg (

The XMLAgg() aggregate is used for the second argument, indicating that the content for the top-level <Departments> node is a group of aggregated nodes, which means these nodes will be child nodes of a single parent node.

The second XMLElement() call establishes <Dept> as the name of each child node of the <Departments> parent node and then relies on the next three embedded XMLElement() calls for the contents of each <Dept> child node:


XMLElement('Dept', XMLConcat(
      XMLElement('Number', d.deptno),
      XMLElement('Name', d.deptname),
      XMLElement('Location', d.deptloc)))))

These three embedded XMLElement() calls create as many <Dept> child nodes as necessary to wrap the rows of data that are returned from the DEPARTMENTS table.

It is important to understand the use of the XMLAgg() aggregate function. This aggregate combines child nodes under their parent node, which in the preceding example means that there is a single parent <Departments> node that contains all four <Dept> nodes. Without the XMLAgg() aggregate call, the XML that is produced contains four <Departments> nodes, each of which contains a single <Dept> node, which results in an invalid XML document, as shown here:

<Departments>
      <Dept>
         <Number>10</Number>
         <Name>MARKETING</Name>
         <Location>BOSTON</Location>
      </Dept>
</Departments>
<Departments>
      <Dept>
         <Number>20</Number>
         <Name>HR</Name>
         <Location>BOSTON</Location>
      </Dept>
</Departments>
<Departments>
      <Dept>
         <Number>30</Number>
         <Name>SALES</Name>
         <Location>NEW YORK</Location>
      </Dept>
</Departments>
<Departments>
      <Dept>
         <Number>40</Number>
         <Name>ENGINEERING</Name>
         <Location>NEW YORK</Location>
      </Dept>
</Departments>

This is not valid XML syntax because there are four instances of the <Departments> document element. This demonstrates how important it is to use the IsValidXML() function to ensure that the XML that you create with the function library can be parsed as XML. Furthermore, if you are using schemas, you are also responsible for returning well-formed XML (XML that conforms to the structure that is specified by the schema).

As another example, suppose that you want to return a list of employees by department, tagged as follows:

<EmployeesByDepartment>
   <Dept DeptNo=“10“>
      <Name>ACCOUNTING</Name>
      <Location>NEW YORK</Location>
      <Employees>
         <Employee EmpNo=“7782“>
            <Name>CLARK</Name>
            <Job>MANAGER</Job>
            <Manager>7839</Manager>
            <Salary>2450</Salary>
         </Employee>
         <Employee EmpNo=“7839“>
            <Name>KING</Name>
            <Job>PRESIDENT</Job>
            <Salary>5000</Salary>
         </Employee>
         ...
      </Employees>
   </Dept>
   ...
<EmployeesByDepartment>

To return employees by department, two select statements are required. First, create an employee grouping, and then, group the employees by department:

CREATE temp table emp_grouping AS
SELECT deptno, XMLElement ('Employees', XMLAGG (
      XMLElement ('Employee', XMLAttributes ('EmpNo', empno),
         XMLConcat (
            xmlelement ('name', name),
            xmlelement ('job', job),
            xmlelement ('manager', mgr),
            xmlelement ('salary', sal),
            xmlelement ('comm', comm)))))
AS xml FROM emp INNER JOIN dept 
ON emp.deptno = dept.deptno
GROUP BY deptno;
SELECT XMLElement('EmployeesByDepartment', XMLAGG( 
         XMLElement('Dept', XMLAttributes('DeptNo', deptno), XMLConcat(
            XMLElement('Name', D.DNAME),
            XMLElement('Location', D.LOC),
            emp_grouping.xml)))) 
FROM dept INNER JOIN emp_grouping 
    ON dept.deptno = emp_grouping.deptno;