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>

I have tried to read Oracle XMLDB Developer Guide and my first reaction was that parsing XML is very complicated in Oracle… And lately I got a task to rewrite some PL/SQL code to use a different SOAP service and the existing code had a few hundred rows just to parse XML (with XML DOM API)! Actually, starting from 10.2, this job is much easier…

Here I will go over some methods for working with XML, that are usable directly from SQL. In this first part, reading and extracting data from XML.

XML for the examples

<?xml version="1.0" encoding="utf-8"?>

  Some Company name
  
    
      Ilmar
      Kerm
      Estonia
      
        
          Development
        
        
          DBA
        
      
    
    
      Ilmar2
      Kerm2
      Estonia2
      
        
          Development
        
        
          DBA
        
      
    
  

Load the XML data to a table, to special XMLTYPE data type.

create table xml (
  x xmltype
);

insert into xml values (
xmltype.createxml('<?xml version="1.0" encoding="utf-8"?>

  Some Company name
  
...
  
'));

COMMIT;

Extracting a single value

extractValue can be used to extract a single value from XML using XPath expression.

SQL> SELECT extractValue(x, '/company/name') FROM xml;

EXTRACTVALUE(X,'/COMPANY/NAME')
-------------------------------
Some Company name

extractValue only works with single values, otherwise exception will be raised.

SQL> SELECT extractValue(x, '/company/employees/item') FROM xml;
SELECT extractValue(x, '/company/employees/item') FROM xml
*
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node

Query XML as relational object

Oracle Database, since 10g, has a very easy way to map XML to a relational object and query it with SQL, so no PL/SQL code is needed for parsing XML – the XMLTABLE function.

SQL> SELECT emp.*
  FROM xml,
       XMLTABLE (
         '/company/employees/item'
         PASSING 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;

        ID FIRST_NAME LAST_NAME  COUNTRY    ACTIVE_DEPARTMENT
---------- ---------- ---------- ---------- --------------------
         1 Ilmar      Kerm       Estonia    DBA
         2 Ilmar2     Kerm2      Estonia2   Development

XmlTable takes the following arguments:

After XML is readable as a relational table, all the power of Oracle SQL can be used for querying.