Written by: ilmarkerm

Category: Blog entry

Published: January 19, 2010

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.