From time to time you’ll have to represent your data stored in a database in an XML format, eg. to exchange it between systems, to send it to external parties, etc. In this introduction, I’ll show you step by step using small examples how you can easily generate XML from tables.

SQL

There are some SQL functions available to generate XML. The first function is XMLElement, it creates an element for the data specified, usually coming from a table column:

SELECT XMLElement( "DEPARTMENT"
                 , department_name
                 )
  FROM departments
 WHERE department_id IN (10, 20);

Resulting in these two rows(from two records):

<DEPARTMENT>Administration</DEPARTMENT>
<DEPARTMENT>Marketing</DEPARTMENT>

That’s the easy part, generating data surrounded with XML tags. But of course you need to add attributes to these elements. This is still easy to do with the XMLAttributes function:

SELECT XMLElement("DEPARTMENT"
                 , XMLAttributes( department_id as "ID"
                                )
                 , department_name
                 )
FROM departments
WHERE department_id IN (10, 20);
<DEPARTMENT ID="10">Administration</DEPARTMENT>
<DEPARTMENT ID="20">Marketing</DEPARTMENT>

OK…We now can create an XML Element with attributes, but we have more data than one element. So we add another element:

SELECT XMLElement("DEPARTMENT_ID"
                 , department_id
                 )
     , XMLElement("DEPARTMENT"
                 , department_name
                 )
  FROM departments
 WHERE department_id IN (10, 20);

Result:

<DEPARTMENT_ID>10</DEPARTMENT_ID>     <DEPARTMENT>Administration</DEPARTMENT>
<DEPARTMENT_ID>20</DEPARTMENT_ID>     <DEPARTMENT>Marketing</DEPARTMENT>

Oops…two columns, not really the way I wanted it, I want all XML in 1 column. We can use XMLForest for this.  It generates a forest of XML Elements, i.e. multiple XML Elements:

SELECT XMLForest(department_id as "ID"
                , department_name as "NAME"
                )
  FROM departments
 WHERE department_id IN (10, 20);
<ID>10</ID><NAME>Administration</NAME>
<ID>20</ID><NAME>Marketing</NAME>

That’s more like it, one column for each record. And by combining XML Element and XML Forest we can get this:

SELECT XMLElement("DEPARTMENT"
                 , XMLForest(department_id as "ID"
                            , department_name as "NAME"
                            )
                 )
  FROM departments
 WHERE department_id IN (10, 20);
<DEPARTMENT><ID>10</ID><NAME>Administration</NAME></DEPARTMENT>
<DEPARTMENT><ID>20</ID><NAME>Marketing</NAME></DEPARTMENT>

An XML Element containing an XML Forest, i.e. one element containing multiple elements.

Up to now, we got multiple rows back from the query. Using XMLAgg we can retrieve one row with all records from the query:

SELECT XMLAgg(XMLElement("DEPARTMENT"
                        , XMLAttributes( department_id as "ID"
                                       )
                        , department_name
                        )
             )
  FROM departments
 WHERE department_id IN (10, 20);
<DEPARTMENT ID="10">Administration</DEPARTMENT><DEPARTMENT ID="20">Marketing</DEPARTMENT>

These are some SQL functions to generate XML and when combining these functions, one can create some nice and more complex XML Documents:

SELECT XMLElement("DEPARTMENT"
                 , XMLAttributes( department_id as "ID"
                                , department_name as "NAME"
                                )
                 , XMLElement("EMPLOYEES"
                             , (SELECT XMLAgg( XMLElement("EMPLOYEE"
                                                         , XMLForest(employee_id as "ID"
                                                                    ,first_name||' '||last_name as "NAME"
                                                                    )
                                                         )
                                             )
                                 FROM employees emp
                                WHERE emp.department_id = dept.department_id
                                )
                              )
                )
  FROM departments dept
 WHERE department_id IN (10, 20);

This query generates two rows, one row for each department:

  • The “Department” element has two attributes (ID and name) and contains an “Employees” element
  • The “Employees” element consists of multiple rows (all employees for that department)
  • Every row of the “Employees” element has an “Employee” element (single employee record)
  • Each “Employee” element has multiple elements: “ID” and “Name”

It looks like this:

<DEPARTMENT ID="10" NAME="Administration"><EMPLOYEES><EMPLOYEE><ID>200</ID><NAME>Jennifer Whalen</NAME></EMPLOYEE></EMPLOYEES></DEPARTMENT>
<DEPARTMENT ID="20" NAME="Marketing"><EMPLOYEES><EMPLOYEE><ID>201</ID><NAME>Michael Hartstein</NAME></EMPLOYEE><EMPLOYEE><ID>202</ID><NAME>Pat Fay</NAME></EMPLOYEE></EMPLOYEES></DEPARTMENT>

We can format this to make it clearer:

<DEPARTMENT ID="10" NAME="Administration">
  <EMPLOYEES>
    <EMPLOYEE>
      <ID>200</ID>
      <NAME>Jennifer Whalen</NAME>
    </EMPLOYEE>
  </EMPLOYEES>
</DEPARTMENT>
<DEPARTMENT ID="20" NAME="Marketing">
  <EMPLOYEES>
    <EMPLOYEE>
      <ID>201</ID>
      <NAME>Michael Hartstein</NAME>
    </EMPLOYEE>
    <EMPLOYEE>
      <ID>202</ID>
      <NAME>Pat Fay</NAME>
    </EMPLOYEE>
  </EMPLOYEES>
</DEPARTMENT>

I hope this will be a good start to create XML documents from the data in your tables. In part two I will show you how to create XML from PL/SQL.

Oracle documentation on Generating XML using SQL Functions.