Written by: ilmarkerm

Category: Blog entry

Published: January 20, 2010

In this part I’ll look at some features of Oracle Database for getting the data out in XML format, with SQL code only. In part 1 I looked at some features for reading/parsing XML with pure SQL.

Populating the tables for examples

Loading the departments into table DEPARTMENTS.

CREATE TABLE departments AS
   SELECT dep.*
     FROM xml, 
     XMLTABLE ('/company/employees/item[1]/departments/item'
        PASSING x
        COLUMNS id FOR ORDINALITY,
                name VARCHAR2 (100 CHAR) PATH 'name') dep;

Employee data to table EMPLOYEES.

CREATE TABLE employees AS
   SELECT emp.id,
          emp.first_name,
          emp.last_name,
          emp.country,
          dep.id active_department_id
     FROM xml,
          departments dep,
          XMLTABLE (
             '/company/employees/item'
                PASSING xml.x
                COLUMNS id NUMBER PATH '@id',
                        first_name VARCHAR2 (10 CHAR) PATH 'first_name',
                        last_name VARCHAR2 (10 CHAR) PATH 'last_name',
                        country VARCHAR2 (10 CHAR) PATH 'country',
                        active_department VARCHAR2 (20 CHAR) PATH 'departments/item[@active="true"]/name') emp
    WHERE dep.name = emp.active_department;

Automatic XML generation + XML Stylesheet

Oracle provides functions sys_xmlgen and sys_xmlagg to automatically generate XML document based on table data. sys_xmlgen adds XML tags around a single value (returns XMLType data type from a single value) and sys_xmlagg aggregates/encloses rows into one XML document. Both of these functions also accept XMLFormat formatting parameter, that can be used to override the default enclosing XML tag name.

SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) company_xml
  FROM (SELECT SYS_XMLAGG (emp_xml, XMLFormat (enclTag => 'EMPLOYEES')) x
          FROM (  SELECT tmp_id,
                         SYS_XMLAGG (xml_col, XMLFormat (enclTag => 'ITEM')) emp_xml
                    FROM (SELECT *
                            FROM (SELECT e.id tmp_id,
                                         SYS_XMLGEN (e.id) id,
                                         SYS_XMLGEN (e.first_name) first_name,
                                         SYS_XMLGEN (e.last_name) last_name,
                                         SYS_XMLGEN (e.country) country,
                                         SYS_XMLGEN (d.name, XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department
                                    FROM employees e, departments d
                                   WHERE e.active_department_id = d.id) UNPIVOT ((xml_col)
                                                                        FOR tmp_col
                                                                        IN (id, first_name, last_name, 
                                                                          country, active_department)))
                GROUP BY tmp_id)
        UNION ALL
        SELECT SYS_XMLGEN ('Some Comany name', XMLFormat (enclTag => 'NAME')) x
          FROM DUAL)

The query is quite long, but the idea behind it is pretty simple – generate a XMLType row for every data attribute and use SYS_XMLAGG to group and aggregate the generated XMLType rows into desirable XML format.

And the result is one XML document.

<?xml version="1.0"?>
<COMPANY>
<EMPLOYEES>
  <ITEM>
    <ID>1</ID>
    <ACTIVE_DEPARTMENT>DBA</ACTIVE_DEPARTMENT>
    <COUNTRY>Estonia</COUNTRY>
    <LAST_NAME>Kerm</LAST_NAME>
    <FIRST_NAME>Ilmar</FIRST_NAME>
  </ITEM>
  <ITEM>
    <ID>2</ID>
    <ACTIVE_DEPARTMENT>Development</ACTIVE_DEPARTMENT>
    <COUNTRY>Estonia2</COUNTRY>
    <LAST_NAME>Kerm2</LAST_NAME>
    <FIRST_NAME>Ilmar2</FIRST_NAME>
  </ITEM>
</EMPLOYEES>
<NAME>Some Comany name</NAME>
</COMPANY>

If the XML is still not at the correct format, then XSL Transformation can be applied to convert the output to another XML format.

XSL Transformation is an XML document, that describes the rules how to convert one XML document to another XML. For this example I wrote the following XSLT document:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="COMPANY">
  <company>
    <name><xsl:value-of select="NAME"/></name>
  </company>
  <employees>
    <xsl:for-each select="EMPLOYEES/ITEM">
      <item>
        <xsl:attribute name="id">
          <xsl:value-of select="ID" />
        </xsl:attribute>
        <first_name><xsl:value-of select="FIRST_NAME" /></first_name>
        <last_name><xsl:value-of select="LAST_NAME" /></last_name>
        <country><xsl:value-of select="COUNTRY" /></country>
        <departments>
          <item active="true">
            <name><xsl:value-of select="ACTIVE_DEPARTMENT" /></name>
          </item>
        </departments>
      </item>
    </xsl:for-each>
  </employees>
</xsl:template>

</xsl:stylesheet>

Save it to XML table named comp_xml_cslt.

CREATE TABLE comp_xml_xslt of xmltype;

INSERT INTO comp_xml_xslt VALUES ('<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
...
</xsl:stylesheet>
');

COMMIT;

To apply the XSL Transformation to source XML, Oracle has a function XMLTransform(source_xml, transformation_xml). Both arguments are XML documents of XMLType and this function returns transformed XML document.

WITH company AS 
  (SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) xml
     FROM (SELECT SYS_XMLAGG (
                     emp_xml,
                     XMLFormat (enclTag => 'EMPLOYEES'))
                     x
             FROM (  SELECT tmp_id,
                            SYS_XMLAGG (
                               xml_col,
                               XMLFormat (enclTag => 'ITEM'))
                               emp_xml
                       FROM (SELECT *
                               FROM (SELECT e.id tmp_id,
                                            SYS_XMLGEN (e.id) id,
                                            SYS_XMLGEN (e.first_name) first_name,
                                            SYS_XMLGEN (e.last_name) last_name,
                                            SYS_XMLGEN (e.country) country,
                                            SYS_XMLGEN (d.name,
                                               XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department
                                       FROM employees e,
                                            departments d
                                      WHERE e.active_department_id =
                                               d.id) UNPIVOT ((xml_col)
                                                     FOR tmp_col
                                                     IN (id, first_name, last_name, country, active_department)))
                   GROUP BY tmp_id)
           UNION ALL
           SELECT SYS_XMLGEN ('Some Comany name',
                              XMLFormat (enclTag => 'NAME'))
                     x
             FROM DUAL))
SELECT XMLTRANSFORM (company.xml, comp_xml_xslt.object_value)
  FROM company, comp_xml_xslt

In this query, COMPANY inline view is the same XML generating query as in the example before, but the XMLTRANSFORM function in main query is used apply the transformation. Result is below:

<company>
 <name>Some Comany name</name>
</company>
<employees>
 <item id="1">
  <first_name>Ilmar</first_name>
  <last_name>Kerm</last_name>
  <country>Estonia</country>
  <departments>
   <item active="true">
    <name>DBA</name>
   </item>
  </departments>
 </item>
 <item id="2">
  <first_name>Ilmar2</first_name>
  <last_name>Kerm2</last_name>
  <country>Estonia2</country>
  <departments>
   <item active="true">
    <name>Development</name>
   </item>
  </departments>
 </item>
</employees>

XQuery

Oracle also implements the standard XQuery language, that can be used for querying XML data and Oracle has provided some XQuery views to access relational database tables.

SELECT XMLQuery(
         'for $emp in ora:view("EMPLOYEES")/ROW
          return <item id="{$emp/ID}">
                 {$emp/FIRST_NAME}
                 {$emp/LAST_NAME}
                 {$emp/COUNTRY}
                 <departments>
                   {for $dep in ora:view("DEPARTMENTS")/ROW 
                    return <item active="{if ($dep/ID eq $emp/ACTIVE_DEPARTMENT_ID) then "true" else ("false")}">{$dep/NAME}</item>}
                </departments>
                </item>'
          RETURNING CONTENT) x FROM DUAL

And the XML result is:

<item id="1"><FIRST_NAME>Ilmar</FIRST_NAME><LAST_NAME>Kerm</LAST_NAME><COUNTRY>Estonia</COUNTRY><departments><item active="false"><NAME>Development</NAME></item><item active="true"><NAME>DBA</NAME></item></departments></item><item id="2"><FIRST_NAME>Ilmar2</FIRST_NAME><LAST_NAME>Kerm2</LAST_NAME><COUNTRY>Estonia2</COUNTRY><departments><item active="true"><NAME>Development</NAME></item><item active="false"><NAME>DBA</NAME></item></departments></item>