Oracle Database 10g (SE or EE)
xml, dba, sqlAll


Getting into SQL/XML

by Tim Quinlan


Get an overview of SQL and XML interoperability and learn how to begin working with XML documents stored in an Oracle database.

Published December 2006

XML data is commonly used in today’s production systems and is a major part of database implementations. In the past, this fact caused design problems for database developers who consequently had to store, query, and update XML data as unstructured LOBs, or alternatively, “shred” data into relational tables and then put it back together. This approach resulted in programming complications and inefficiencies because the access mechanisms were immature.

The Oracle XML DB feature that was first delivered with Oracle9i Database Release 2 provided groundbreaking features for storing, retrieving, and manipulating XML data in the database. Oracle 10g Release 2 XML DB , however, greatly extends this initial approach in which XML data is little more than a BLOB in the database.

If you are a DBA or a developer new to the implementation of XML in Oracle, this article is for you: you will get an overview of SQL and XML interoperability and learn how to begin working with XML documents stored in an Oracle 10g Release 2 database, and about the many features of Oracle Database 10g Release 2 that DBAs and developers should become comfortable with before beginning to work with XML in the database. Thanks to these SQL/XML features, the skills you've already acquired while working with relational data can easily be enhanced to help you work with XML.

What is Oracle’s implementation of XML?

Oracle has implemented XML through the XML DB component of the database, which is a standard feature in Enterprise Edition as well as Standard Edition. Oracle XML DB is easily installed using either the Database Configuration Assistant (DBCA) or by running a catalog script, catqm.sql. The XML DB product provides all the features discussed in this article.

You can verify that XML DB has been installed by simply running the SQL below:

select comp_name from dba_registry where comp_name like '%XML%';        

Oracle XML Database

The XML DB schema and its objects can also be checked to see that Oracle XML DB has been installed.

SQL and XML Working Together

The SQL/XML standard has been under development to provide a mechanism that allows us to generate XML from a relational query and, conversely, provide the ability to deliver SQL data from XML documents. Oracle XML DB in Oracle Database 10g Release 2 implements SQL 2003 and features from the upcoming SQL/XML standard release. The implementation of these standards in Oracle allows us to look inside an XML document in ways that were not possible in older SQL standards.

New datatype. A new datatype, XMLType, was introduced in Oracle9i Database Release 1 to allow an XML document in the database to be accessible in SQL and at the same time allow XML developers the ability to use XML standards on a document. This datatype tells the database that the content is in XML format and allows us to perform queries on an XML document. Using XMLType rather than a relational or CLOB implementation provides a layer of separation between the application and storage model. This separation can allow data to move to a different storage model without being tied to a CLOB or relational model. XMLType can be used to create a table, column, or view. It can also be used as a datatype for parameters and variables.

Built-in XML methods operate on the content of a document by allowing us to create, extract, and index XML data. Indexing can be performed using b-tree, text indexing, and function-based indexes. In effect, XMLType data combined with XPath access can be used to look inside a document. This functionality is provided through PL/SQL and Java APIs. XMLType can be used in PL/SQL, in Java using JDBC, and in the Oracle Data Provider for .Net. This powerful and relatively new datatype will be used extensively throughout this article.

Storage structures. Oracle’s XML implementation gives us the flexibility to store data in two different ways: as structured and unstructured storage. XMLType data is unstructured when stored as a single LOB column and structured when implemented as a set of objects.

One specific example of this is an XMLType table, which can be implemented using either of the two storage models. An XMLType table is unstructured when implemented as a LOB by using the “XMLType store as CLOB” syntax shown below:
create table table_name of XMLtype
XMLType store as CLOB; 

Let’s focus on the structured and unstructured storage for XML data and look at these in more detail.

Structured storage. Structured XML storage is implemented as a set of objects. These objects can be implemented in a relational format through tables with referential constraints optionally implemented between the tables. They can also be implemented by using an XMLSchema to decompose an XMLType document into a set of objects.

In the case where relational tables are used, the tables may be designed in advance for this purpose or, alternatively, existing tables can be used. The document is in effect stored as a “virtual” document through the relational tables, with its logical structure maintained. This approach maintains Document Object Model (DOM) fidelity, although it is not a byte-for-byte physical representation of the document. This can be done by creating an XMLType view over existing relational data.

Structured storage has some performance advantages over unstructured storage and can be chosen to provide more query and update optimization through the table and index design. XML operations on structured storage can help to reduce memory and storage because XML tags are not stored and because there is potentially a more-granular level of data retrieval and usage. Index usage with b-tree and function-based indexes is enhanced, and in-place updates can be performed on pieces of the document using XPath rewrite. All of this helps performance and is discussed in more detail later in this article.

There are also some disadvantages to using structured storage. Inserting and retrieving an entire document can take more overhead. There is also limited flexibility, because only documents that match the XMLSchema can be stored—although there are cases where this is actually an advantage. The document is not stored as a byte-for-byte representation of the original, and the order of data in a document is not maintained. However, no data will be lost.

Think of highly structured data as being mostly XML, where each element of the data can be clearly defined.

Unstructured storage. XML can be stored in Oracle in an unstructured manner using CLOB storage such that SQL queries will not know the structure of the data. This data can be stored using Oracle’s XMLType datatype.

Unstructured data may be chosen in cases where you want the stored data to match the exact physical representation of the document. Also, if the data is not updated often or if there is a large percentage of inserts or reads of the entire document (rather than pieces of the document), it may be more efficient to keep the data together in one spot. There are also situations where you need to keep documents flexible, and in these cases an XMLType table or column can be useful.

A disadvantage of unstructured storage is that updates to pieces of a document are generally not as efficient as with structured data. Also, XPath operations that construct DOM from CLOBs using functions can be an expensive use of system resources. Other issues with unstructured data are that SQL constraints cannot be implemented and that memory management is not as efficient.

Think of unstructured data as mostly not XML data and where most or all of the data is simply seen as a single CLOB.

Now that you’ve been introduced to the storage models, let’s take a look at some naming features.

What’s in a Namespace?

This is a large topic and one of the more complex XML DB concepts to understand. In this article, we will cover some namespace concepts to give you a gist of what these are and how they are used.

A namespace is used to describe a set or collection of related attributes or elements in an XML document. Namespaces can be used to ensure that document constructs have a universally unique name. XMLSchema in particular takes advantage of this feature, as a target namespace is usually the same as the URI of the XMLSchema. Examples of namespaces are shown below. Note that the naming looks similar to an internet URL. This naming standard is recommended by the World Wide Web Consortium (W3C) but is not required. The URI is used to uniquely identify the name of a registered XMLSchema in the database and does not need to be the physical URI where a document is located.

XMLType methods and XML functions make use of namespace prefixes. When an XML document has no target namespace, then the namespace prefix is in the noNameSpace namespace. Attribute xsi:noNamespaceSchemaLocation can be used for the schema URI.

If an element does not have another namespace prefix, a namespace can be applied by default when it applies to an element where it is declared.

There are two special namespaces that you will see regularly. These are http://www.w3.org/2001/XMLSchema, which is the overall XMLSchema namespace, and http://xmlns.oracle.com/xdb, which is the Oracle-supplied XML DB namespace. The latter namespace has functions that get written to underlying SQL functions. Some of the XPath functions are also rewritten. Examples are ora:contains, ceiling, floor, not, string-length, substring, and translate. Attributes used by XML DB also belong to this namespace.

When defining an element, we can specify a namespace for that element. In XML, we can define this as

<elementName xmlns:ab="http.name.com" />

As you see, we gave an elementName, which is the element we are working on (for example, “orders”). Xmlns is a reserved word and tells us that this is a namespace definition. The “ab” part is called a prefix. This prefix is a short name that is bound to the URI. In this case, ab is bound to ‘http.name.com’, and ab can be prefixed to an element name.

Below we will create a very small schema and declare a bind prefix xs to http://www.w3.org/2001/XMLSchema and prefix xdb to http://xmlns.oracle.com/xdb . In order to declare the namespace, we use xmlns to bind them. Notice that the xs prefix is used in this case on the schema element names.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
           xmlns:xdb= "http://xmlns.oracle.com/xdb" version="1.0">
<xs:element name="INVOICESCHEMA" xdb:defaultTable="INVOICESCHEMA">
             <xs:element name="MailAddressTo">
                              <xs:element name="Person" type="xs:string"/>
                              <xs:element name="Street" type="xs:string"/>
                       <xs:attribute name="id" type="xs:string" use="required"/>  

We’ve only touched the surface of namespaces here. Refer to the Oracle XML DB Developer’s Guide 10g Release 2, on Oracle Technology Network (OTN), for more information on this topic.

Working with XML

Getting data into an XMLType table and column. As you may expect, there are many options available to us to get XML data into the database. Data can be inserted with SQL, PL/SQL, Java, and C programs. We can also load data into the database using the SQL*Loader and Import/Export functions of XMLType tables. XML data can also be moved with Oracle Streams and can also use DBMS_XDB functions. Let’s explore one useful and simple way to load XML data.

In this example, an XML document will be loaded from a file into a table using a SQL insert to load the XML document from a bfile. This file needs to be accessed through a database directory. To begin this, we’ll create a directory pointing to a file where the XML document file is stored. Note that to perform the examples in this article, you must ensure that role xdbadmin has been granted to the user you are working with.

create or replace directory xmldir as '/u01/app/oracle/admin/db01/sql';

Now we can create tables that can contain XMLType data in two ways. The first will be a table with a column of XMLType. The second will be a table defined as type XMLType.

1. Create a table with an XML column.

create table invoiceXML_col (
inv_id number primary key,
inv_doc XMLType); 

2. Create an XML table.

create table invoiceXML_tbl of XMLtype;

Each of the above statements implicitly creates two indexes in each table—one for the primary key and one for the LOB.

Now we will insert an invoice document into both tables. Note that we are using character set AL32UTF8 and will specify this to pass the character set encoding to be used for the file being read. Data will be inserted into both tables to show the similarity between the SQL statements.

Insert into invoicexml_col values (1, 
XMLType(bfilename('XMLDIR', 'invoicexml.txt'), 
nls_charset_id('AL32UTF8') )); 

Insert into invoicexml_tbl values 
(XMLType(bfilename('XMLDIR', 'invoicexml.txt'),

We now have two XML documents loaded into the database using an XMLType table and column. It’s as simple as that.

Registering and Implementing Schema-Typed Data

As if dealing with namespaces and storage techniques were not enough, XMLType data can also be implemented as schema-typed or untyped data. An XMLSchema associates a document with an XMLSchema that provides information about the document structure and contents. This provides better documentation, validation, and controls around a document. The information in the schema can be used to deliver more-efficient query and update processing. An XMLSchema also allows an XML document to be stored as structured storage data, because the document can be decomposed into a set of objects through the XMLSchema. The object model used to store the document is determined by the schema definition. XMLType methods schemaValidate() and isSchemaValid() allow editing of an XMLType document using the schema definition.

How do we create a schema and use this with an XML document? To do this, we need to create an XMLSchema definition

  1. Register the schema
  2. Create an XMLSchema-based table
  3. Insert data into it

The example below shows an implementation of these in four steps.

1. First, create a schema definition in file invoiceformtest.xsd in directory XML_DIR. The listing is below:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
           xmlns:xdb=http://xmlns.oracle.com/xdb version="1.0">
<xs:element name="INVOICEFORMTEST" xdb:defaultTable="INVOICEFORMTEST">
         <xs:element name="MailAddressTo">
                  <xs:element name="Person" type="xs:string"/>
                  <xs:element name="Street" type="xs:string"/>
                  <xs:element name="City" type="xs:string"/>
                  <xs:element name="State" type="xs:string"/>
                  <xs:element name="Zipcode" type="xs:string"/>
               <xs:attribute name="id" type="xs:string" use="required"/>  

2. Register the above schema in the database using procedure DBMS_XMLSCHEMA.registerSchema. The username you are using to connect to the database will require that the alter session privilege be granted to it so that the schema can be successfully registered.

    SCHEMAURL => 'http://xmlns.oracle.com/xdb/invoiceformtest.xsd',
    SCHEMADOC => bfilename('XMLDIR','invoiceformtest.xsd'),
    CSID => nls_charset_id('AL32UTF8'));

To delete the schema, simply run the DBMS_XMLSCHEMA.deleteSchema statement, as shown below:

  SCHEMAURL => 'http://xmlns.oracle.com/xdb/invoiceformtest.xsd',

3. The above statement also creates table invoiceformtest because of the xdb:defaultTable="INVOICEFORMTEST" statement in the schema definition above. Without this, a generated name (one that you would not want to work with) would have been created.

4. Now we are ready to enter documents into the XMLSchema-based table from an XML document that is stored in XML_DIR and is in the format specified by the implemented schema.

Insert into invoiceformtest values 
(XMLType(bfilename('XMLDIR', 'invoiceformtest.txt'),
 nls_charset_id(' AL32UTF8')));

Let’s look at what we’ve just created using the object_value pseudocolumn on the XML document.

SQL> select object_value from invoiceformtest;

     <MailAddressTo id="1">
            <Person>Joe Smith</Person>
            <Street>10 Apple Tree Lane</Street>
            <City>New York</City>

We’re done!

Understanding XQuery Functions and Expressions

XQuery is a standard developed by the W3C for a query language to extract information from an XML file It allows us to access physical XML documents or relational data that is virtualized as XML documents through XML views. The expressions used can be simple queries or part of a larger query, and standard functions can be included such as date/time, arithmetic, or string functions as well as user-defined functions. XQuery is new in Oracle Database 10g Release 2 and is supported in XMLQuery() and XMLTable() functions. Examples using these two functions are shown later, in the “XMLQuery() and XMLTable()” section.

XQuery makes use of XPath expressions (described below) to locate the detailed items in XML documents. This can be thought of as the foundation for the XMLQuery() and XMLTable() functions introduced in Oracle Database 10g Release 2 and covered later in this article.

Some of the more important XQuery expressions include XPath, XMLSequence, and FLWOR and are discussed below.

XPath, XMLSequence, and FLWOR Expressions

XPath. XPath provides a way to locate items in an XML document using addressing techniques, and it processes these items using a logical path through a document structure. It allows a programmer to deal with a document at a higher level of abstraction by specifying a route through a document rather than pointing at specific elements. XPath uses the concept of a node that defines where the path begins and then a “logical tree” that includes relationships such as attribute, self, parent, child, and ancestor. In other words, XPath models an XML document as a tree of nodes. There are different types of nodes, such as element nodes, attribute nodes, and text nodes. XPath can determine a way to calculate a string value for each node.

XPath expressions can be used to query and update XML documents in a standards-based way. We’ll use the extract, extractValue, existsNode, and XMLSequence functions to demonstrate XPath functionality using the invoicexml_tbl document we’ve already created and into which we’ve already inserted data.

Query the document to be used in XPath examples. Let’s take a look at searching XML (XMLType) data with SQL using XPath functions. To do this, we’ll look at the document we’ll be working with using the object_value pseudocolumn to retrieve the XML document from the XMLType table.

SQL> select object_value from invoicexml_tbl;

    <MailAddressTo id="PA">
        <Person>Joe Smith</Person>
        <Street>10 Apple Tree Lane</Street>
        <City>New York</City>
    <MailAddressFrom id="PA">
        <Person>Ed Jones</Person>
        <Street>11 Cherry Lane</Street>
    <Details id="2006Sept1to30PA">
        <FromTo>Sept 1, 2006 to Sept 30, 2006</FromTo>
        <InvDate>Oct 1, 2006</InvDate>
        <Contractor>Ed Jones</Contractor>

Using extract. Using extract, we can select an individual node and its leaf nodes from the document by combining extract with object_value. In other words, we can look inside an XML document that’s been stored as XMLType. This is true whether we’ve used structured or unstructured data and also whether or not the data is schema-based. Let’s extract the MailAddressTo node and its leaf nodes.

select extract(object_value, '/Invoice/MailAddressTo') from invoicexml_tbl;

<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10
Apple Tree Lane</Street><City>New York</City><State>NY</Stat

As you can see, the output includes the MailAddressTo portion of the document and is not pretty-printed (formatted). Also, the syntax used to accomplish this is very simple. What’s important is that we were able to look inside the document without dumping the entire thing.

Using extractValue. The data value that exists in a leaf node can be extracted using extractValue. A higher - level node such as MailAddressTo cannot be extracted using this function. Note that the output of this is not in XML-syntax format and contains simply the data value.

select extractValue(object_value, '/Invoice/MailAddressTo/Person')
 Person from invoicexml_tbl;

Joe Smith

Using existsNode. ExistsNode is used in a similar fashion, to search for specific values at the node level (and only the node level) of a document. It returns a True or False flag to specify whether a search was successful. The = 1 predicate is not a count but rather represents a True condition, and = 0 is False.

Select count(*) from invoicexml_tbl
where existsNode(
object_value, '/Invoice/MailAddressTo[Person="Joe Smith"]') = 1;


Using XMLSequence. Unlike extractValue, which can only extract a value from a single node, XMLSequence can be used to look at multiple nodes or a fragment of a document. It does this by creating a virtual table that consists of XMLType objects. Let’s compare extractValue with XMLSequence using the MailAddressTo branch node.

select extractValue(object_value, '/Invoice/MailAddressTo') from invoicexml_tbl;

from invoicexml_tbl
ERROR at line 2:
ORA-19025: EXTRACTVALUE returns value of only one node

This ORA-19025 message is self-explanatory. We can fortunately overcome this by restructuring the query and using XMLSequence, as shown below:

select value(addr) 
from invoicexml_tbl i,
    extract(i.object_value, '/Invoice/MailAddressTo'))) addr
where existsNode(i.object_value, '/Invoice/Details[@id="2006Sept1to30PA"]') = 1;

<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10
Apple Tree Lane</Street><City>New York</City><State>NY</Stat

Because XMLSequence creates a virtual table, we can also use this function on a leaf node.

select value(person)
from invoicexml_tbl i,
    extract(i.object_value, '/Invoice/MailAddressTo/Person'))) person
where existsnode(i.object_value, '/Invoice/Details[@id="2006Sept1to30PA"]') = 1;

<Person>Joe Smith</Person>

FLWOR. FOR, LET, WHERE, ORDER BY, and RETURN (FLWOR; pronounced “flower”) is one of the most important and powerful expressions in XQuery syntax. Either FOR or LET must exist, WHERE and ORDER BY are optional, and RETURN is mandatory. FLWOR on its own is a large topic to cover. This section gives you an introduction to the power of this statement.

FOR binds one or more variables in an iterative manner, in the order that the variables are listed. Values listed previously can then be used in a subsequent set of values. This also works similarly to a SQL From clause. Values listed previously can then be used in a subsequent set of values, as shown below:

For $var in (1,2,3) , $varPlus5 in (5+$var, 5+$var, 5+$var)

The three iterations set $var and $varPlus5 to 1,6; 2,7; and 3,8, respectively.

LET, like FOR, binds variables iteratively, and values can be computed using previously calculated values. As with FOR, LET can be thought of as a SQL FROM clause. LET can also be used to perform joins.

WHERE filters data in the same manner as a SQL WHERE clause.

ORDER BY optionally sorts the data.

RETURN returns the final result set from the filtered and ordered FLWOR expression.

FLWOR being used with XMLQuery(). Let’s look at an example where we query and join two documents: partys.xml to an orders.xml document on the Party key. This XML data is in the Oracle XML DB Repository. To do this, we will use XMLQuery(); FLWOR; and XQuery functions doc, count, avg, and integer. These are in the namespace for built-in XQuery functions, http://www.w3.org/2003/11/xpath_functions.

The query below reads as follows: Using function fn:doc, FOR all partyno attributes in partys.xml, join (LET) all order elements in orders.xml that match on partyno (variable $p was bound in the FOR statement). This produces a stream of items ($p and $o), with $p representing a party number and $o a set of orders for that party. Get the items WHERE there is more than 1 order. ORDER BY the average amt descending using XQuery function avg in namespace fn. Amt is attached to order element $o. Return the party number (bound to $p) and child element ordercount.

                               for $p in  
                               let $o :=  
                               fn:doc("/public/orders.xml")/orders/order[@partyno = $p]
                               where fn:count($o) > 1
                               order by fn:avg($o/@amt) descending
                               return <big-party>{$p,


Yes, this query is doing a lot and shows some of what’s possible when using FLWOR expressions on XML documents.

XQuery ora: Functions

Oracle XML DB provides five more XQuery functions that developers can use. These are implemented in the http://xmlns.oracle.com/xdb namespace, which uses the prefix ora:. Along with these are two other XPath functions. These are not yet part of the SQL/XML standard but are expected to be in the future. The ora:view function is a particularly valuable one that can be used to transform relational data into XML format. All of these are described below.

ora:view XQuery function. This is a valuable function to create a view over relational tables. This can make them appear as if they are XML documents.

Syntax:         ora:view ([db-schema STRING, ] db_table STRING)
                RETURNS document-node (element())

Examples that use ora:view are shown later in the article.

ora:contains XQuery function. The namespace prefix “ora:” and name “contains” are used together to create an XPath function that can be used as an XPath argument to the SQL/XML existsNode, extract, and extractValue functions.This allows you to perform a structural search with a text predicate.

This function returns a positive number if text_query matches the input_text. If they do not match, then 0 is returned. When they are used with existsNode, extract, or extractValue, you need to include the namespace mapping parameter xmlns:ora="http://xmlns.oracle.com/xdb". The syntax for ora:contains is shown below. The owner is the current user in cases where a policy_owner is not defined. The policy_name is the name of the matching rules that will be applied. The default matching rules are defined by ctxsys.default_policy_oracontains.

Syntax: ora:contains (
                               text_query [,  
                               policy_name] [,  

The example below shows how we can use ora:contains as an argument to existsNode.

SELECT count(*) FROM invoicexml_tbl
  WHERE existsNode(object_value, '/Invoice/MailAddressTo/Person
                     [ora:contains(text(), "Joe Smith") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"') = 1;


ora:matches XQuery function. This function uses regular expressions to match text. This is similar to the SQL REGEXP_LIKE condition except that it uses XQuery arguments instead of SQL datatypes. True is returned if the target_string matches the regular expression match_pattern ; if not, False is returned. As you can see in the syntax below, match_parameter can be added to specify additional criteria to use in a search. An example of this is to provide case sensitivity.

Syntax: ora:matches (
                               match_pattern [,  

ora:replace XQuery function. This extends the ora:matches function by replacing the target_string with the replace_string if the match_pattern is met ( True ). As with ora:matches, this uses regular expressions to match the text.

Syntax: ora:replace (
                               replace_string [,  

ora:sqrt XQuery function. As you may expect from the name, this returns the square root value of the provided number.

Syntax: ora:sqrt (number)

XPath extension functions: ora:instanceof and ora:instanceof-only. Oracle XML DB can support schema-based data when the datatypes of attributes and elements are known. Because XPath 1.0 is not aware of datatype information, there are XML DB extension functions in namespace http://xmlns.oracle.com/xdb that allow you to restrict an XML document node to a specific datatype. Function ora:instanceof can be used to restrict a node to a datatype or subtype, and ora:instanceof-only restricts a node to the datatype only. A subtype is a feature than can be used to extend or restrict a type.

Syntax: ora:instanceof(nodeset-expr, typename [, schema-url])

Syntax: ora:instanceof-only(nodeset-expr, typename [, schema-url])

In the above syntax, nodeset-expr is usually a relative XPath expression. This function returns True if the datatype of any node matches typename and False if it does not. Typename can be qualified with a namespace prefix.

T hese functions will return False for non-schema-based data because it does not have a datatype associated with elements and attributes .

Querying XML Data in the XML DB Repository Using fn:doc and fn:collection Functions

There are two important XQuery functions that can be used to query all of the resources in the XML DB Repository. Fn:doc is an XQuery function that can obtain a repository file containing XML data. This file resource is pointed at by its URI argument. XQuery variables can be bound to data using the FLWOR expressions FOR and LET. XQuery function fn:doc can be used to read a single XML document stored in the XML DB repository.

The second XQuery function that queries resources in the repository is fn:collection. This can return a number of similar documents stored in the same folder in the repository.

Let’s create repository resources to illustrate how fn:doc and fn:collection work with some simple examples. We will start by creating a resource using the DBMS_XDB PL/SQL package. This can be used to manage resources in XML DB. The createResource procedure is used in this case to create new file resources that contain the Orders and Partys XML documents.

Create the orders and partys resource names used in the examples below. There is one additional resource created named ordersnamespace.xml, which shows an example of a namespace being used.

  res BOOLEAN;
  ordersxmlstring VARCHAR2(500):= 
    '<?xml version="1.0"?>
       <order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
       <order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
       <order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
       <order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
  partysxmlstring VARCHAR2(500):=
    '<?xml version="1.0"?>
       <party partyno="1111" partyname="ABC Corp" partycity="Toronto"/>
       <party partyno="2222" partyname="Freds Inc" partycity="Chicago"/>
       <party partyno="3333" partyname="Gofaster Corp" partycity="Montreal"/>
  ordersxmlnsstring VARCHAR2(500):=
    '<?xml version="1.0"?>
     <orders xmlns="http://order.com">
       <order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
       <order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
       <order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
       <order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
  res := DBMS_XDB.createResource('/public/orders.xml',   ordersxmlstring);
  res := DBMS_XDB.createResource('/public/partys.xml',  partysxmlstring);
  res := DBMS_XDB.createResource('/public/ordersnamespace.xml', ordersxmlnsstring);

We can see the resources we just created by querying the resource_view, as shown below:

SQL>  select any_path, res from resource_view where any_path like '%partys%';
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd">

Next, display a single partys.xml document using fn:doc.

SELECT XMLQuery('for $p in fn:doc("/public/partys.xml") 
                 return $p'
                RETURNING CONTENT) partys FROM DUAL;

<partys><party partyno="1111" partyname="ABC Corp" 
partycity="Toronto"/><party partyno="2222" partyname="Freds Inc" 
partycity="Chicago"/> <party partyno="3333" partyname="Gofaster Corp"

Fn:collection can be used a little differently than fn:doc by returning all of the documents stored in the same folder in the repository. The output produced from this statement was not pretty-printed, and the formatting below was done manually to make the document more readable.

SELECT XMLQuery('for $p in fn:collection("/public") 
                 return $p'
                RETURNING CONTENT) collection_public FROM DUAL;

   <order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
   <order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
   <order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
   <order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>

<orders xmlns="http://order.com">
   <order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
   <order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
   <order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
   <order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>

   <party partyno="1111" partyname="ABC Corp" partycity="Toronto"/>
   <party partyno="2222" partyname="Freds Inc" partycity="Chicago"/>
   <party partyno="3333" partyname="Gofaster Corp" partycity="Montreal"/>

There are many other DBMS_XDB procedures and functions that can be used to manage all XML DB resources, such as providing the ability to delete resources and create folders. These can be found in the Oracle XML DB Developer’s Guide 10g Release 2, on OTN.

XMLQuery() and XMLTable()

The XMLQuery() and XMLTable() functions were introduced in Oracle Database 10g Release 2. They provide a powerful interface between SQL and XML, allowing us to query, construct, and transform relational data as if it’s XML and XML data as if it’s relational. In general, we use XMLQuery() to generate an XML document from relational data and XMLTable() to create relational views from XML data. These are not yet part of ANSI SQL but are expected to be included in a future standard.

XMLQuery() function. This is used to construct XML data and query it using XQuery. It enables the execution of XQuery expressions in a SQL context. We can also query relational data by creating a dynamic XML view over relational data using ora:view and are able to operate on parts of an XML document rather than on the entire document.

XMLQuery() takes an XQuery expression as a string literal and an optional XQuery “context item” as a SQL expression. This expression sets the XPath context in which the XQuery expression will be evaluated. The function returns the result of the XQuery expression as an XMLType instance. The XMLQuery() function can also take in SQL expressions as arguments, where the values are bound to XQuery variables when the expression is evaluated. The results are returned as an XMLType instance.

Let’s look at two examples where we use XMLQuery() on relational and XML data.

Example combining XMLQuery() with ora:view and FLWOR expressions. In the first example, we will use XMLQuery() on physical relational tables. XMLQuery() must act on XML data, and this is accomplished using the ora:view feature on relational tables Employee and Department in the HR schema. Once ora:view is used on both relational tables, they appear as XML and we are able to then use XQuery expressions, including a nested FLWOR expression.

The query below is doing the following: For each department, get the department id, and for all employees that match the department id with a commission greater than 30 percent, return the employee first and last name. Note that the FOR expression is used twice in this query.

         'FOR $dep in ora:view("DEPARTMENTS")/ROW
          RETURN <Department id="{$dep/DEPARTMENT_ID}">
                   {FOR $emp in ora:view("EMPLOYEES")/ROW 
                    WHERE $emp/DEPARTMENT_ID eq $dep/DEPARTMENT_ID
                      and $emp/COMMISSION_PCT > .3 
                    RETURN ($emp/FIRST_NAME, $emp/LAST_NAME)}

<Department id="10"><Employee></Employee></Department><Department 

The result, as you can see, is not pretty-printed. The query syntax is slightly complex but shows that we have the ability to transform relational data into XML data with ora:view and that we can perform a join operation on XML documents and apply predicates on the data inside the documents.

Example using XMLQuery() with an XMLType column and FLWOR expressions. Table invoicexml_col, which we created above and into which we inserted a document, contains an XMLType column inv_doc. The Invoice data that is stored in this column is in XML format. In this query, we will pass XMLType column inv_doc to XQuery using the XMLQuery() function with the PASSING clause. Notice how we can return some specific fields from the Invoice document based on WHERE predicates. In effect, we are able to get inside the XML document and retrieve specific fields based on predicates rather than only being able to see this document as a CLOB.

The SELECT statement below applies to all of the rows of invoicexml_col. We then iterate across all the invoice rows with the FOR statement. The WHERE predicate picks out the elements from Zipcode 12345, and we then RETURN the City, State, and Zipcode and whether the City and State are entered correctly for this Zipcode. An IF…THEN…ELSE construct shows some additional power to this syntax.

Select XMLQuery(
         'FOR $i in /Invoice 
          WHERE  $i/MailAddressTo/Zipcode = 12345 
          RETURN <Details>
                   <Zipcode num="{$i/MailAddressTo/Zipcode}"/>
                   <CityName char="{$i/MailAddressTo/City}"/>
                   <City>{IF ($i/MailAddressTo/City = "New York") 
                          THEN "Correct City" 
                          ELSE "Incorrect City"}
                   <State>{if ($i/MailAddressTo/State = "NY") 
                                   then "Correct State" else "Incorrect State"}
         PASSING inv_doc RETURNING CONTENT) ny_invoice
FROM invoicexml_col;

<Details><Zipcode num="12345"></Zipcode>
<CityName char="New York"></CityName>
<City>Correct City</City>
<State>Correct State</State>

Both queries illustrate the power we have to look inside and process XML documents in a detailed and piece-wise manner. The functionality provided is similar to what we’ve been able to do with relational data—impressive!

XMLTable() function. This function enables an XML value to be interpreted as a table or a set. It’s used to return a table and columns from the evaluation of XQuery expressions, rather than returning a sequence as XQuery would normally do. XMLType data can be queried and the XML results split or shredded into relational format—think of this as creating a virtual table. The virtual table can then be used to insert data into other tables or can be queried. Relational views can also be constructed over XML data. The XMLTable() function can also be used in a SQL From clause.

Example using XMLTable() with the COLUMNS clause. We will use our invoicexml_col table, introduced earlier, to illustrate how XMLTable() can be used to transform XML data into relational format. In the example below, XMLTable() accesses the Invoice document that is stored in column inv_doc. The paths of the data elements we want are mapped to new column names and formats using the COLUMNS clause. The XMLTable() function returns the data as a virtual table, and the result of this query is the same as if we had queried a relational table. Note the use of the WHERE clause at the bottom of the query that allows us to filter XML data in the exact same way we would with any SQL query written for relational data.

The query and output are shown below:

SELECT inv_id, a.PersonName, a.StreetName, a.CityName, a.State, a.Zipcode
FROM invoicexml_col,  
     PASSING invoicexml_col.inv_doc
        PersonName varchar2(10) PATH '/Invoice/MailAddressTo/Person',
        StreetName varchar2(20) PATH '/Invoice/MailAddressTo/Street',
        CityName varchar2(10) PATH '/Invoice/MailAddressTo/City',
        State varchar2(5) PATH '/Invoice/MailAddressTo/State',
        Zipcode varchar2(7) PATH '/Invoice/MailAddressTo/Zipcode'
      ) a
WHERE a.CityName like 'New%';

    1   Joe Smith       10 Apple Tree Lane      New York         NY      12345

Getting XML to Perform

We’ve already seen that we can perform queries that look inside XML documents. The next step is to think about ways to make these queries perform quickly. XML queries can be explained and indexes created to help improve the performance of XML data access. In particular, we can improve the performance of XPath functions in a manner similar to the way we’ve tuned SQL in the past. Also, as with SQL tuning, there are some situations where restructuring XML queries can also help to change and improve access paths.

Function-based indexes can be used on structured and unstructured XMLType tables whether they are schema-based or not. We may also want to take advantage of binary indexes for functions such as existsNode that only return flags of 0 or 1 depending on whether the predicate evaluates to True or False.

In this section, we’ll see examples of how structured and unstructured indexing can potentially impact performance through explain plans. We will first look at the optimization of an XQuery expression that uses ora:view with relational tables.

Optimizing relational data in XQuery expressions. Let’s look at a tuning example on a similar query to one we’ve already examined . This query uses ora:view to access r elational d ata and takes advantage of FLWOR expressions. The relational tables Employees and Departments from the HR schema will be joined and department info returned for all employees who had a commission > .3. Note that the attribute names (such as JOB in $emp/ROW/COMMISSION_PCT) are case-sensitive.

In the query below, FOR allows us to iterate over row elements Employees in Departments. Employees rows are bound to variable $emp, and Departments rows are bound to $dep. WHERE performs a join between the two tables and selects all employees who had a commission_pct > .3. RETURN returns the department information.

The query and explain plans are shown below:

explain plan for 
SELECT XMLQuery('for $emp in ora:view("EMPLOYEES"), $dep in ora:view("DEPARTMENTS")
                 where $emp/ROW/DEPARTMENT_ID = $dep/ROW/DEPARTMENT_ID
                   and $emp/ROW/COMMISSION_PCT > .3
                 return $dep'   
                RETURNING CONTENT) AS high_commission_employees

                                QUERY_PLAN              OBJECT_NAME           COST     BYTES LEVEL
SELECT STATEMENT                               2                  1
  SORT                                                 82         2
    HASH JOIN                                  5       328        3
      TABLE ACCESS      EMPLOYEES              2       104        4
      TABLE ACCESS      DEPARTMENTS            2       1512       4
  FAST DUAL                                    2                  2

As you can see, the access path that the optimizer chose does not use indexes. Because ora:view allows us to return results from a relational query as XML elements, we can simply create b-tree indexes on the relational tables and see if this can improve performance.

Create index emp_idx1 on employees (department_id, commission_pct);
Create index dept_idx1 on departments (department_id);

                                QUERY_PLAN          OBJECT_NAME              COST     BYTES  LEVEL
SELECT STATEMENT                               2                  1
  SORT                                                 82         2
    TABLE ACCESS    DEPARTMENTS                1       56         3
    NESTED LOOPS                               2       328        4
    INDEX           EMP_IDX1                   1       104        5
    INDEX           DEPT_IDX1                  0                  5
  FAST DUAL                                    2                  2

The indexes are now used, and cost decreased very slightly. Our tuning of this XQuery expression using ora:view is very similar to our tuning of standard, non-XML SQL. As with standard SQL, rewriting the query and also using features such as bind variables rather than hard-coded string values can help improve performance.

Optimizing structured XMLType data. XPath rewrite can optimize structured (object-relational) storage techniques. The optimizer can make one other internal performance improvement: It can change XPath-based functions into relational statements when the following are true:

  • The XMLType data uses a registered XMLSchema. This is another advantage of using schema-based data.
  • Attributes map to underlying SQL (relational) tables.
  • Structured storage is used.

This provides the possibility of query rewrite for performance reasons. It also allows relational performance tuning techniques to be used, as shown above.

Now, let’s optimize a query on structured, nonrelational XMLType data. We will start with a very simple example using the invoicexml_tbl table created earlier.

explain plan for 
select extract(object_value, '/Invoice/MailAddressTo')
from invoicexml_tbl
where extractValue(object_value, '/Invoice/MailAddressTo/Person')= 'Joe Smith';

The access path is shown below.

                                QUERY_PLAN        OBJECT_NAME          COST      BYTES       LEVEL
SELECT STATEMENT                        3        87       1
  TABLE ACCESS    INVOICEXML_TBL        3        87       2

A function-based index on the predicate used in this query can be created. The index must use the exact same syntax as the query that will use it. This index will be created and the SQL explained one more time to see if the new index is used.

Create index invoicexml_tbl_idx1 on invoicexml_tbl
(extractValue(object_value, '/Invoice/MailAddressTo/Person'));

                                QUERY_PLAN        OBJECT_NAME           COST    BYTES        LEVEL
SELECT STATEMENT                         1      87        1
  TABLE ACCESS    INVOICEXML_TBL         1      87        2
  INDEX           INVOICEXML_TBL_IDX1    1                3


The new function-based index is used, and the cost of the query has decreased as a result.

Optimizing unstructured XMLType data.
We’ll now perform the exact same test with unstructured XMLType data. Table invtest_unstruct will be created in exactly the same manner as we created invoicexml_tbl, except that we will add the “XMLType store as CLOB” syntax to ensure that this is defined as unstructured data. The data definition language (DDL), insert, and explain plan are below :

create table invtest_unstruct of XMLtype 
XMLType store as CLOB;

Insert into invtest_unstruct values ( 
XMLType(bfilename('XMLDIR', 'invoicexml.txt'), 
nls_charset_id('WE8MSWIN1252') )); 

explain plan for 
select extract(object_value, '/Invoice/MailAddressTo')
from invtest_unstruct
where extractValue(object_value, '/Invoice/MailAddressTo/Person')='Joe Smith'

                                QUERY_PLAN            OBJECT_NAME            COST     BYTES  LEVEL
SELECT STATEMENT                               2       2002       1
  TABLE ACCESS        INVTEST_UNSTRUCT         2       2002       2

We will now create a function-based index to see if it will be used and if the expected cost has decreased. Note that the “create index” syntax is the same as the syntax used in the query predicate.

Create index invtest_unstruct_idx1 on invtest_unstruct
(extractValue(object_value, '/Invoice/MailAddressTo/Person'));

                                QUERY_PLAN           OBJECT_NAME              COST     BYTES         LEVEL
SELECT STATEMENT                               1       2002       1
  TABLE ACCESS       INVTEST_UNSTRUCT          1       2002   2
  INDEX              INVTEST_UNSTRUCT_IDX1     1                  3

As you can see, the same index and query can be used effectively with structured and unstructured data. In general, however, note that structured data access is generally more efficient than unstructured access.


Oracle Database has evolved to incorporate XML standards and data at a rapid pace. Oracle9i Database introduced the XML DB repository and a new datatype, XMLType, which delivered both LOB and structured storage options. Oracle XML DB features provide the ability to perform native XML processing in the database through a hierarchical model that maps URIs to database objects. XPath expressions are used to operate on individual elements of a document through a “logical tree” using the concepts of a node and path. XMLSchema support was also included with Oracle9i Database.

Oracle Database 10g enhanced XML support with the W3C XML XQuery language, which includes the XMLQuery() and XMLTable() functions. The combination of these features facilitates the interchangeable use of relational data and XML data in Oracle Database 10g Release 2. As this article demonstrates, SQL queries can operate on XML data, and XML queries are now able to access relational data. A key to this is Oracle’s SQL/XML implementation of the XMLQuery() and XMLTable() functions.

Oracle’s standards-based approach also supports popular protocols such as FTP, HTTP, and WebDAV to allow common client tools and applications to access, edit, and publish XML data stored in the database.

In this article you've learned that the skills you’ve already acquired while working with relational data can easily be enhanced to allow you to work with XML using these SQL/XML features. An important example of this is our ability to enhance performance of XML data access through Oracle’s indexing, explain, and storage features. It’s critical that designers, developers, and DBAs keep up-to-date with this quickly changing technology.

Tim Quinlan [ tquinlan@tlqconsulting.com] is an Oracle certified Database Administrator with experience in Oracle 7 thru Oracle10g Release 2. He has worked with Databases since 1981, performing the roles of DBA, architect, designer and implementer of enterprise-wide Data Warehouse and transactional Databases. Tim has spoken at many conferences, taught database courses, and written feature articles for leading database publications. His main (professional) interest is designing and implementing very large, high-performance, high-availability database systems.