Tuesday, January 19, 2010

The easy way of working with XML in Oracle database (part 1)

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:

'/company/employees/item'XPath expression of the "row"
PASSING xSource of XML data - XMLTYPE data type
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'
Column definitions with the corresponding Oracle data type and XPath expression

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

4 comments:

  1. nice. I've only played a bit with oracle's XML tools, mostly to generate XML results.
    Is there an easy way to populate the xml table from a file? (I'm assuming that will be in part II );)

    ReplyDelete
  2. In part 2 I was planning to look, how to generate XML results.
    But you can use the usual PL/SQL file tools (like UTL_FILE) for reading the file and populating XML table using CLOB.

    ReplyDelete
  3. Yeah, I haven't decided yet if we have a use for loading an XML file into a table, so I haven't really looked into it. Given your example above, it would make sense to show how to populate the table in the first place, since that is pretty much step 1. ;)

    However, generating XML from queries is much more important and useful (which is why I spent a little time with those tools).

    ReplyDelete
  4. You can get XML into database like any other CLOB value, so I don't think it's really related to XML topic.
    But yes, in next part a small PL/SQL file reading sample would be good.

    ReplyDelete