Skip Headers

Oracle9i XML Database Developer's Guide - Oracle XML DB
Release 2 (9.2)

Part Number A96620-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF

Using XMLType

This chapter describes how to use the XMLType datatype, create and manipulate XMLType tables and columns, and query on them. It contains the following sections:

What Is XMLType?

Oracle9i Release 1 (9.0.1) introduced a new datatype, XMLType, to facilitate native handling of XML data in the database. The following summarizes XMLType:

With XMLType and these capabilities, SQL developers can leverage the power of the relational database while working in the context of XML. Likewise, XML developers can leverage the power of XML standards while working in the context of a relational database.

XMLType datatype can be used as the datatype of columns in tables and views. Variables of XMLType can be used in PL/SQL stored procedures as parameters, return values, and so on. You can also use XMLType in SQL, PL/SQL, and Java (through JDBC).


In Oracle9i Release 1 (9.0.1), XMLType was only supported in the server in SQL, PL/SQL, and Java. In Oracle9i Release 2 (9.2), XMLType is also supported on the client side through SQL, Java, and protocols such as FTP and HTTP/WebDav.

A number of useful functions that operate on XML content are provided. Many of these are provided as both SQL and member functions of XMLType. For example, the extract() function extracts a specific node(s) from an XMLType instance.

You can use XMLType in SQL queries in the same way as any other user-defined datatypes in the system.

See Also:

Benefits of the XMLType Data Type and API

The XMLType datatype and API provides significant advantages. It enables SQL operations on XML content, as well as XML operations on SQL content:

When to Use XMLType

Use XMLType when you need to perform the following:

Storing XMLType Data in Oracle XML DB

XMLType data can be stored in two ways or a combination thereof:

Native XMLType instances contain hidden columns that store this extra information that does not quite fit in the SQL object model. This information can be accessed through APIs in SQL or Java, using member functions, such as extractNode().

Changing XMLType storage from structured storage to LOB, or vice versa, is possible using database IMPORT and EXPORT. Your application code does not have to change. You can then change XML storage options when tuning your application, since each storage option has its own benefits.

Pros and Cons of XML Storage Options in Oracle XML DB

Table 4-1 summarizes some advantages and disadvantages to consider when selecting your Oracle XML DB storage option.

Table 4-1 XML Storage Options in Oracle XML DB  
Feature LOB Storage (with Oracle Text index) Structured Storage (with B*Tree index)

Database schema flexibility

Very flexible when schemas change.

Limited flexibility for schema changes. Similar to the ALTER TABLE restrictions.

Data integrity and accuracy

Maintains the original XML byte for byte - important in some applications.

Trailing new lines, whites pace within tags, and data format for non-string datatypes is lost. But maintains DOM fidelity.


Mediocre performance for DML.

Excellent DML performance.

Access to SQL

Some accessibility to SQL features.

Good accessibility to existing SQL features, such as constraints, indexes, and so on

Space needed

Can consume considerable space.

Needs less space in particular when used with an Oracle XML DB registered XML schema.

When to Use CLOB Storage for XMLType

Use CLOB storage for XMLType in the following cases:

XMLType Member Functions

Oracle9i Release 1 (9.0.1) introduced several SQL functions and XMLType member functions that operate on XMLType values. Oracle9i Release 2 (9.2) has expanded functionality. It provides several new SQL functions and XMLType member functions.

See Also:

All XMLType functions use the built-in C parser and processor to parse XML data, validate it, and apply XPath expressions on it. They also use an optimized in-memory DOM tree for processing, such as extracting XML documents or fragments.

See Also:

Appendix C, "XPath and Namespace Primer"

How to Use the XMLType API

You can use the XMLType API to create tables and columns. The createXML() static function of the XMLType API can be used to create XMLType instances for insertion. By storing your XML documents as XMLType, XML content can be readily searched using standard SQL queries.

Figure 4-1 shows the syntax for creating an XMLType table:

  [XMLTYPE XMLType_storage] [XMLSchema_spec];

Figure 4-1 Creating an XMLType Table

Text description of XMLType_table.gif follows
Text description of the illustration XMLType_table.gif

This section shows some simple examples of how to create an XMLType column and use it in a SQL statement, and how to create XMLType tables.

Creating, Adding, and Dropping XMLType Columns

The following are examples of creating, adding, and dropping XMLType columns:

Example 4-1 Creating XMLType: Creating XMLType Columns

The XMLType column can be created like any other user-defined type column:

CREATE TABLE warehouses(
  warehouse_id NUMBER(4),
  warehouse_spec XMLTYPE,
  warehouse_name VARCHAR2(35),
  location_id NUMBER(4));

Example 4-2 Creating XMLType: Creating XMLType Columns

As explained, you can create XMLType columns by simply using the XMLType as the datatype. The following statement creates a purchase order document column, poDoc, of XMLType:

CREATE TABLE po_xml_tab(
  poid number,
  poDoc XMLTYPE);

CREATE TABLE po_xtab of XMLType; -- this creates a table of XMLType. The default
                                 -- is CLOB based storage.

Example 4-3 Adding XMLType Columns

You can alter tables to add XMLType columns as well. This is similar to any other datatype. The following statement adds a new customer document column to the table:

ALTER TABLE po_xml_tab add (custDoc XMLType);

Example 4-4 Dropping XMLType Columns

You can alter tables to drop XMLType columns, similar to any other datatype. The following statement drops column custDoc:

ALTER TABLE po_xml_tab drop (custDoc);

Inserting Values into an XMLType Column

To insert values into the XMLType column, you need to bind an XMLType instance.

Example 4-5 Inserting into XMLTYpe Using the XMLType() Constructor

An XMLType instance can be easily created from a VARCHAR or a Character Large Object (CLOB) by using the XMLType() constructor:

   (       100, XMLType(
              '<Warehouse whNo="100"> 
               </Warehouse>'), 'Tower Records', 1003);

This example creates an XMLType instance from a string literal. The input to createXML() can be any expression that returns a VARCHAR2 or CLOB. createXML() also checks that the input XML is well-formed.

Using XMLType in an SQL Statement

The following simple SELECT statement shows how you can use XMLType in an SQL statement:

Example 4-6 Using XMLType and in a SELECT Statement

  FROM warehouses w;

where warehouse_spec is an XMLType column operated on by member function extract(). The result of this simple query is a string (varchar2):


See Also:

"How to Use the XMLType API" .

Updating an XMLType Column

An XML document in an XMLType can be stored packed in a CLOB. Then updates have to replace the whole document in place.

Example 4-7 Updating XMLType

To update an XML document, you can execute a standard SQL UPDATE statement. You need to bind an XMLType instance, as follows:

UPDATE warehouses SET warehouse_spec = XMLType
                  ('<Warehouse whono="200">

This example created an XMLType instance from a string literal and updates column warehouse_spec with the new value.


Any triggers would get fired on the UPDATE statement You can see and modify the XML value inside the triggers.

Deleting a Row Containing an XMLType Column

Deleting a row containing an XMLType column is no different from deleting a row containing any other datatype.

Example 4-8 Deleting an XMLType Column Row

You can use extract() and existsNode() functions to identify rows to delete as well. For example to delete all warehouse rows for which the warehouse building is leased, you can write a statement such as:

DELETE FROM warehouses e
   WHERE e.warehouse_spec.extract('//Building/text()').getStringVal()
           = 'Leased';


In this release, Oracle supports XMLType as a public synonym for sys.XMLType. XMLType now also supports a set of user-defined constructors (mirroring the createXML static functions). For example:

  • In Oracle9i Release 1 (9.0.1), you could use the following syntax: sys.XMLType.createXML('<Warehouse whNo="100">...)
  • In Oracle9i Release 2 (9.2), you can use the following abbreviated version: XMLType('<Warehouse whNo="100">...).

Guidelines for Using XMLType Tables and Columns

The following are guidelines for storing XML data in XMLType tables and columns:

Define table/column of XMLType

First, define a table/column of XMLType. You can include optional storage characteristics with the table/column definition.


This release of Oracle supports creating tables of XMLType. You can create object references (REFs) to these tables and use them in the object cache.

Create an XMLType Instance

Use the XMLType constructor to create the XMLType instance before inserting into the column/table. You can also use a variety of other functions that return XMLType.

See Also:

"SYS_XMLGEN(): Converting an XMLType Instance" , for an example.

Select or Extract a Particular XMLType Instance

You can select out the XMLType instance from the column. XMLType also offers a choice of member functions, such as extract() and existsNode(), to extract a particular node and to check to see if a node exists respectively. See the table of XMLType member functions in Oracle9i XML API Reference - XDK and Oracle XML DB.

See Also:

You can Define an Oracle Text Index

You can define an Oracle Text index on XMLType columns. This enables you to use CONTAINS, HASPATH, INPATH, and other text operators on the column. All the Oracle Text operators and index functions that operate on LOB columns also work on XMLType columns.

You Can Define XPath Index, CTXXPATH

In this release, a new Oracle Text index type, CTXXPATH is introduced. This helps existsNode() implement indexing and optimizes the evaluation of existsNode() in a predicate.

See Also:

Specifying Storage Characteristics on XMLType Columns

XML data in an XMLType column can be stored as a CLOB column. Hence you can also specify LOB storage characteristics for that column. In example, "Creating XMLType: Creating XMLType Columns", the warehouse_spec column is an XMLType column.

Example 4-9 Specifying Storage When Creating an XMLType Table

You can specify storage characteristics on this column when creating the table as follows:

CREATE TABLE po_xml_tab(
     poid NUMBER(10),
     poDoc XMLTYPE
     XMLType COLUMN poDoc
        STORE AS CLOB (
            TABLESPACE lob_seg_ts
            STORAGE (INITIAL 4096 NEXT 4096)
            CHUNK 4096 NOCACHE LOGGING

The STORE AS clause is also supported when adding columns to a table.

Example 4-10 Adding an XMLType Columns and Specifying Storage

To add a new XMLType column to this table and specify the storage clause for that column, you can use the following SQL statement:

ALTER TABLE po_xml_tab  add(
     custDoc XMLTYPE
   XMLType COLUMN custDoc
           TABLESPACE lob_seg_ts
           STORAGE (INITIAL 4096 NEXT 4096)

Changing Storage Options on an XMLType Column Using XMLData

In non- schema-based storage, you can use XMLDATA to change storage characteristics on an XMLType column.

Example 4-11 Changing Storage Characteristics on an XMLType Column Using XMLDATA

For example, consider table foo_tab:

CREATE TABLE foo_tab (a xmltype);

To change the storage characteristics of LOB column a in foo_tab, you can use the following statement:

ALTER TABLE foo_tab MODIFY LOB (a.xmldata) (storage (next 5K) cache);

XMLDATA identifies the internal storage column. In the case of CLOB-based storage this corresponds to the CLOB column. The same holds for XML schema-based storage. You can use XMLDATA to explore structured storage and modify the values.


In this release, the XMLDATA attribute helps access the XMLType's internal storage columns so that you can specify storage characteristics, constraints, and so on directly on that column.

You can use the XMLDATA attribute in constraints and indexes, in addition to storage clauses.

See also:

Oracle9i Application Developer's Guide - Large Objects (LOBs) f and Oracle9i SQL Reference for more information about LOB storage options

Specifying Constraints on XMLType Columns

You can specify NOT NULL constraint on an XMLType column.

Example 4-12 Specifying Constraints on XMLType Columns

CREATE TABLE po_xml_tab (
  poid number(10),
  poDoc XMLType NOT NULL

prevents inserts such as:

INSERT INTO po_xml_tab (poDoc) VALUES (null);

Example 4-13 Using ALTER TABLE to Change NOT NULL of XMLType Columns

You can also use the ALTER TABLE statement to change NOT NULL information of an XMLType column, in the same way you would for other column types:

ALTER TABLE po_xml_tab MODIFY (poDoc NULL);

You can also define check constraints on XMLType columns. Other default values are not supported on this datatype.

Manipulating XML Data in XMLType Columns/Tables

Since XMLType is a user-defined data type with functions defined on it, you can invoke functions on XMLType and obtain results. You can use XMLType wherever you use a user-defined type, including for table columns, views, trigger bodies, and type definitions.

You can perform the following manipulations or Data Manipulation Language (DML) on XML data in XMLType columns and tables:

Inserting XML Data into XMLType Columns/Tables

You can insert data into XMLType columns in the following ways:

XMLType columns can only store well-formed XML documents. Fragments and other non-well-formed XML cannot be stored in XMLType columns.

Using INSERT Statements

To use the INSERT statement to insert XML data into XMLType, you need to first create XML documents to perform the insert with. You can create the insertable XML documents as follows:

Example 4-14 Inserting XML Data Using createXML() with CLOB

The following examples use INSERT...SELECT and the XMLType constructor to first create an XML document and then insert the document into the XMLType columns. Consider table po_clob_tab that contains a CLOB, poClob, for storing an XML document:

CREATE TABLE po_clob_tab 
  poid number,
  poClob CLOB

-- some value is present in the po_clob_tab
INSERT INTO po_clob_tab 
     VALUES(100, '<?xml version="1.0"?>
                    <PO pono="1">
                          <STREET>1033, Main Street</STREET>

Example 4-15 Inserting XML Data Using an XMLType Instance

You can insert a purchase order XML document into table, po_xml_tab, by simply creating an XML instance from the CLOB data stored in the other po_clob_tab:

INSERT INTO po_xml_tab 
       SELECT poid, XMLType(poClob)
       FROM po_clob_tab;


You can also get the CLOB value from any expression, including functions that can create temporary CLOBs or select out CLOBs from other table or views.

Example 4-16 Inserting XML Data Using XMLType() with String

This example inserts a purchase order into table po_tab using the XMLType constructor:

INSERT INTO po_xml_tab
   VALUES(100, XMLType('<?xml version="1.0"?>
                    <PO pono="1">
                          <STREET>1033, Main Street</STREET>

Example 4-17 Inserting XML Data Using XMLElement()

This example inserts a purchase order into table po_xml_tab by generating it using the XMLElement() SQL function. Assume that the purchase order is an object view that contains a purchase order object. The whole definition of the purchase order view is given in "DBMS_XMLGEN: Generating a Purchase Order from the Database in XML Format".

INSERT INTO po_xml_tab
  SELECT XMLelement("po", value(p))
       FROM po p
       WHERE p.pono=2001;

XMLElement() creates an XMLType from the purchase order object, which is then inserted into table po_xml_tab. You can also use SYS_XMLGEN() in the INSERT statement.

Selecting and Querying XML Data

You can query XML data from XMLType columns in the following ways:

SQL Functions for Manipulating XML data

SQL functions such as existsNode(), extract(), XMLTransform(), and updateXML() operate on XML data inside SQL. XMLType datatype supports most of these as member functions. You can use either the selfish style of invocation or the SQL functions.

Selecting XML Data

You can select XMLType data using PL/SQL or Java. You can also use the getClobVal(), getStringVal(), or getNumberVal() functions to retrieve XML as a CLOB, VARCHAR, or NUMBER, respectively.

Example 4-18 Selecting XMLType Columns using getClobVal()

This example shows how to select an XMLType column using SQL*Plus:

SET long 2000

SELECT e.poDoc.getClobval() AS poXML
   FROM po_xml_tab e;

<?xml version="1.0"?>
<PO pono="2">
      <STREET>2 Avocet Drive</STREET>
      <CITY>Redwood Shores</CITY>

Querying XML Data

You can query XMLType data and extract portions of it using the existsNode() and extract() functions. Both these functions use a subset of the W3C XPath recommendation to navigate the document.

Using XPath Expressions for Searching XML Documents

XPath is a W3C recommendation for navigating XML documents. XPath models the XML document as a tree of nodes. It provides a rich set of operations to "walk" the tree and to apply predicates and node test functions. Applying an XPath expression to an XML document can result in a set of nodes. For instance, /PO/PONO selects out all "PONO" child elements under the "PO" root element of the document.

Table 4-2 lists some common constructs used in XPath.

Table 4-2 Some Common XPath Constructs  
XPath Construct Description


Denotes the root of the tree in an XPath expression. For example, /PO refers to the child of the root node whose name is "PO".


Also used as a path separator to identify the children node of any given node. For example, /PO/PNAME identifies the purchase order name element, a child of the root element.


Used to identify all descendants of the current node. For example, PO//ZIP matches any zip code element under the "PO" element.


Used as a wildcard to match any child node. For example, /PO/*/STREET matches any street element that is a grandchild of the "PO" element.

[ ]

Used to denote predicate expressions. XPath supports a rich list of binary operators such as OR, AND, and NOT. For example, /PO[PONO=20 and PNAME="PO_2"]/SHIPADDR select out the shipping address element of all purchase orders whose purchase order number is 20 and whose purchase order name is "PO_2". [ ] is also used for denoting an index into a list. For example, /PO/PONO[2] identifies the second purchase order number element under the "PO" root element.

The XPath must identify a single or a set of element, text, or attribute nodes. The result of the XPath cannot be a boolean expression.

See Also:

Appendix C, "XPath and Namespace Primer"

Querying XML Data Using XMLType Member Functions

You can select XMLType data through PL/SQL, OCI, or Java. You can also use the getClobVal(), getStringVal(), or getNumberVal() functions to retrieve the XML as a CLOB, VARCHAR or a number, respectively.

Example 4-19 Retrieving an XML Document as a CLOB Using getClobVal() and existsNode()

This example shows how to select an XMLType column using getClobVal() and existsNode():

set long 2000

SELECT e.poDoc.getClobval() AS poXML
   FROM po_xml_tab e
   WHERE e.poDoc.existsNode('/PO[PNAME = "po_2"]') = 1;

<?xml version="1.0"?>
<PO pono="2">
      <STREET>2 Avocet Drive</STREET>
      <CITY>Redwood Shores</CITY>


existsNode Function

The syntax for the existsNode() function is described in Figure 4-2 and also as follows:

existsNode(XMLType_instance IN XMLType, 
           XPath_string IN VARCHAR2, namespace_string IN varchar2 := null) 

Figure 4-2 existsNode() Syntax

Text description of existsnode.gif follows
Text description of the illustration existsnode.gif

existsNode() function on XMLType checks if the given XPath evaluation results in at least a single XML element or text node. If so, it returns the numeric value 1, otherwise, it returns a 0. Namespace can be used to identify the mapping of prefix(es) specified in the XPath_string to the corresponding namespace(s).

Example 4-20 Using existsNode() on XMLType

For example, consider an XML document such as:

     <STREET>1033, Main Street</STREET>

An XPath expression such as /PO/PNAME results in a single node. Therefore, existsNode() will return 1 for that XPath. This is the same with /PO/PNAME/text(), which results in a single text node.

An XPath expression such as /PO/POTYPE does not return any nodes. Therefore, an existsNode() on this would return the value 0.

To summarize, existsNode() member function can be used in queries and to create function-based indexes to speed up evaluation of queries.

Example 4-21 Using existsNode() to Find a node

The following example tests for the existence of the /Warehouse/Dock node in the warehouse_spec column XML path of the sample table oe.warehouses:

SELECT warehouse_id, EXISTSNODE(warehouse_spec, '/Warehouse/Docks')
   "Loading Docks"
   FROM warehouses
   WHERE warehouse_spec IS NOT NULL;

WAREHOUSE_ID Loading Docks
------------ -------------
           1             1
           2             1
           3             0
           4             1

Using Indexes to Evaluate existsNode()

You can create function-based indexes using existsNode() to speed up the execution. You can also create a CTXXPATH index to help speed up arbitrary XPath searching.

See Also:

"Creating XPath Indexes on XMLType Columns: CTXXPATH Index"

extract () Function

The extract() function is similar to the existsNode() function. It applies a VARCHAR2 XPath string with an optional namespace parameter and returns an XMLType instance containing an XML fragment. The syntax is described in Figure 4-3 and as follows:

extract(XMLType_instance IN XMLType, XPath_string IN VARCHAR2, 
                namespace_string In varchar2 := null) RETURN XMLType;

Figure 4-3 extract() Syntax

Text description of extract_xml.gif follows
Text description of the illustration extract_xml.gif

extract() on XMLType extracts the node or a set of nodes from the document identified by the XPath expression. The extracted nodes can be elements, attributes, or text nodes. When extracted out, all text nodes are collapsed into a single text node value. Namespace can be used to supply namespace information for prefixes in the XPath string.

The XMLType resulting from applying an XPath through extract() need not be a well-formed XML document but can contain a set of nodes or simple scalar data in some cases. You can use the getStringVal() or getNumberVal() methods on XMLType to extract this scalar data.

For example, the XPath expression /PO/PNAME identifies the PNAME element inside the XML document shown previously. The expression /PO/PNAME/text(), on the other hand, refers to the text node of the PNAME element.


The latter is still considered an XMLType. In other words, extract(poDoc,'/PO/PNAME/text()') still returns an XMLtype instance although the instance may actually contain only text. You can use getStringVal() to get the text value out as a VARCHAR2 result.

Use text() node test function to identify text nodes in elements before using the getStringVal() or getNumberVal() to convert them to SQL data. Not having the text() node would produce an XML fragment.

For example, XPath expressions:

You can use the index mechanism to identify individual elements in case of repeated elements in an XML document. For example, if you have an XML document such as:


you can use:

The result of extract() is always an XMLType. If applying the XPath produces an empty set, then extract() returns a NULL value.

Hence, extract() member function can be used in a number of ways, including the following:

Example 4-22 Using extract() to Extract the Value of a Node

This example extracts the value of node, /Warehouse/Docks, of column, warehouse_spec in table oe.warehouses:

SELECT warehouse_name, 
   extract(warehouse_spec, '/Warehouse/Docks').getStringVal()
   "Number of Docks"
   FROM warehouses
   WHERE warehouse_spec IS NOT NULL;

WAREHOUSE_NAME       Number of Docks
-------------------- --------------------
Southlake, Texas          <Docks>2</Docks>
San Francisco             <Docks>1</Docks>
New Jersey                <Docks/>
Seattle, Washington       <Docks>3</Docks>

extractValue() Function

The extractValue() function takes as arguments an XMLType instance and an XPath expression. It returns a scalar value corresponding to the result of the XPath evaluation on the XMLType instance. extractValue() syntax is also described in Figure 4-4.

extractValue() tries to infer the proper return type from the XML schema of the document. If the XMLType is non- schema-based or the proper return type cannot be determined, Oracle XML DB returns a VARCHAR2.

Figure 4-4 extractValue() Syntax

Text description of extractvalue.gif follows
Text description of the illustration extractvalue.gif

A Shortcut Function

extractValue() permits you to extract the desired value more easily than when using the equivalent extract function. It is an ease-of-use and shortcut function. So instead of using:


you can replace extract().getStringVal() or extract().getnumberval() with extractValue() as follows:

extractValue(x, 'path/text()')

With extractValue() you can leave off the text(), but ONLY if the node pointed to by the 'path' part has only one child and that child is a text node. Otherwise, an error is thrown.

extractValue() syntax is the same as extract().

extractValue() Characteristics

extractValue() has the following characteristics:

Example 4-23 Extracting the Scalar Value of an XML Fragment Using extractValue()

The following example takes as input the same arguments as the example for extract () Function. Instead of returning an XML fragment, as extract() does, it returns the scalar value of the XML fragment:

SELECT warehouse_name, 
   extractValue(e.warehouse_spec, '/Warehouse/Docks')
   FROM warehouses e 
   WHERE warehouse_spec IS NOT NULL;

-------------------- ------------
Southlake, Texas     2
San Francisco        1
New Jersey
Seattle, Washington  3

ExtractValue() automatically extracted out the text child of Docks element and returned that value. You can also write this using extract() as follows:

extract(e.warehouse_spec, '/Warehouse/Docks/text()').getstringval()

More SQL Examples That Query XML

The following SQL examples illustrate ways you can query XML.

Example 4-24 Querying XMLType Using extract() and existsNode()

Assume the po_xml_tab table, which contains the purchase order identification and the purchase order XML columns, and assume that the following values are inserted into the table:

INSERT INTO po_xml_tab values (100,
   xmltype('<?xml version="1.0"?>

INSERT INTO po_xml_tab values (200,
   xmltype('<?xml version="1.0"?>

Now you can extract the numerical values for the purchase order numbers using extract():

SELECT e.poDoc.extract('//PONO/text()').getNumberVal() as pono
   FROM po_xml_tab e
   WHERE e.podoc.existsnode('/PO/PONO')  = 1 AND poid > 1;

Here extract() extracts the contents of tag, purchase order number, "PONO". existsNode() finds nodes where "PONO" exists as a child of "PO".


Here text() function is only used to return the text nodes. getNumberVal() function can convert only text values into numerical quantity

See Also:

"XMLType Member Functions"

Example 4-25 Querying Transient XMLType Data

The following example shows how you can select out the XML data and query it inside PL/SQL: create a transient instance from the purchase order table and then perform some extraction on it. Assume po_xml_tab contains the data shown in Example 4-16, "Inserting XML Data Using XMLType() with String", modified:

set serverout on
   poxml XMLType;
   cust XMLType;
   val VARCHAR2(200);

 -- select the adt instance
  select poDoc into poxml
     from po_xml_tab p where p.poid = 100;

  -- do some traversals and print the output
  cust := poxml.extract('//SHIPADDR');

   -- do something with the customer XML fragment
  val := cust.getStringVal();
  dbms_output.put_line(' The customer XML value is '|| val);


Example 4-26 Extracting Data from an XML Document and Inserting It Into a Table Using extract()

The following example shows how you can extract out data from an XML purchase order and insert it into an SQL relational table. Consider the following relational tables:

  custid number primary key,
  custname varchar2(20)

INSERT INTO cust_tab values (1001, 'John Nike');

CREATE TABLE po_rel_tab
  pono number,
  pname varchar2(100),
  custid number references cust_tab,
  shipstreet varchar2(100),
  shipcity varchar2(30),
  shipzip varchar2(20)

You can write a simple PL/SQL block to transform XML of the form:

<?xml version = '1.0'?>
    <STREET>323 College Drive</STREET>

into the relational tables, using extract().

Here is an SQL example assuming that the XML described in the previous example is present in the po_xml_tab:

INSERT INTO po_rel_tab
SELECT p.poDoc.extract('/PO/PONO/text()').getnumberval() as pono,
   p.poDoc.extract('/PO/PNAME/text()').getstringval() as pname,
   -- get the customer id corresponding to the customer name
   ( SELECT c.custid 
     FROM  cust_tab c
     WHERE c.custname = p.poDoc.extract('/PO/CUSTOMER/@CUSTNAME').getstringval()
    ) as custid,
    p.poDoc.extract('/PO/SHIPADDR/STREET/text()').getstringval() as shipstreetr,
    p.poDoc.extract('//CITY/text()').getstringval() as shipcity,
    p.poDoc.extract('//ZIP/text()').getstringval() as shipzip
FROM po_xml_tab p;

Table po_tab should now have the following values:

2001    Po_1    1001     323 College Drive  Edison     08820


PNAME is null, since the input XML document did not have the element called PNAME under PO. Also, the preceding example used //CITY to search for the city element at any depth.

Example 4-27 Extracting Data from an XML Document and Inserting It Into a Table Using extract() In a PL/SQL Block

You can do the same in an equivalent fashion inside a PL/SQL block, as follows:

  poxml XMLType;
  cname varchar2(200);
  pono number;
  pname varchar2(100);
  shipstreet varchar2(100);
  shipcity varchar2(30);
  shipzip varchar2(20);


 -- select the adt instance
  SELECT poDoc INTO poxml FROM po_xml_tab p;

  cname := poxml.extract('//CUSTOMER/@CUSTNAME').getstringval();

  pono := poxml.extract('/PO/PONO/text()').getnumberval();
  pname := poxml.extract('/PO/PNAME/text()').getstringval();
  shipstreet := poxml.extract('/PO/SHIPADDR/STREET/text()').getstringval();
  shipcity := poxml.extract('//CITY/text()').getstringval();
  shipzip := poxml.extract('//ZIP/text()').getstringval();     

  INSERT INTO po_rel_tab 
    VALUES (pono, pname, 
           (SELECT custid FROM cust_tab c WHERE custname = cname),
            shipstreet, shipcity, shipzip);

Example 4-28 Searching XML Data with extract() and existsNode()

Using extract() and existsNode() functions, you can perform a variety of search operations on the column, as follows:

SELECT e.poDoc.extract('/PO/PNAME/text()').getStringVal() PNAME
   FROM po_xml_tab e
   WHERE e.poDoc.existsNode('/PO/SHIPADDR') = 1 AND
      e.poDoc.extract('//PONO/text()').getNumberVal() = 300 AND
      e.poDoc.extract('//@CUSTNAME').getStringVal() like '%John%';

This SQL statement extracts the purchase order name "PNAME" from purchase order element PO, from all XML documents containing a shipping address with a purchase order number of 300, and a customer name "CUSTNAME" containing the string "John".

Example 4-29 Searching XML Data with extractValue()

Using extractValue(), you can rewrite the preceding query as:

SELECT extractvalue(e.poDoc, '/PO/PNAME') PNAME
   FROM po_xml_tab e
   WHERE e.poDoc.existsNode('/PO/SHIPADDR') = 1 AND
         extractvalue(e.poDoc,'//PONO') = 300 AND
         extractvalue(e.poDoc,'//@CUSTNAME') like '%John%';

Example 4-30 Extracting Fragments from XMLType Using extract()

extract() member function extracts nodes identified by the XPath expression and returns an XMLType containing the fragment. Here, the result of the traversal may be a set of nodes, a singleton node, or a text value. You can check if the result is a fragment by using the isFragment() function on the XMLType. For example:

SELECT e.poDoc.extract('/PO/SHIPADDR/STATE').isFragment()
    FROM po_xml_tab e;


You cannot insert fragments into XMLType columns. You can use SYS_XMLGEN() to convert a fragment into a well-formed document by adding an enclosing tag. See "SYS_XMLGEN() Function". You can, however, query further on the fragment using the various XMLType functions.

The previous SQL statement returns 0, since the extraction /PO/SHIPADDR/STATE returns a singleton well-formed node which is not a fragment.

On the other hand, an XPath such as /PO/SHIPADDR/STATE/text() is considered a fragment, since it is not a well-formed XML document.

Updating XML Instances and Data in Tables and Columns

This section talks about updating transient XML instances and XML data stored in tables.

With CLOB-based storage, in this release, an update effectively replaces the whole document. Use the SQL UPDATE statement to update the whole XML document. The right hand side of the UPDATE's SET clause must be an XMLType instance. This can be created using the SQL functions and XML constructors that return an XML instance, or using the PL/SQL DOM APIs for XMLType or Java DOM API, that change and bind existing XML instances.

updateXML() SQL Function

updateXML() function takes in a source XMLType instance, and a set of XPath value pairs. It returns a new XML instance consisting of the original XMLType instance with appropriate XML nodes updated with the given values. The optional namespace parameter specifies the namespace mapping of prefix(es) in the XPath parameters.

updateXML() can be used to update, replace elements, attributes and other nodes with new values. They cannot be directly used to insert new nodes or delete existing ones. The containing parent element should be updated with the new values instead.

Text description of updatexmla.gif follows
Text description of the illustration updatexmla.gif

updateXML() updates only the transient XML instance in memory. Use an SQL UPDATE statement to update data stored in tables. The updateXML() syntax is:

UPDATEXML(xmlinstance, xpath1, value_expr1 
                      [, xpath2, value_expr2]...[,namespace_string]);

Example 4-31 Updating XMLType Using the UPDATE Statement

This example updates the XMLType using the UPDATE statement. It updates only those documents whose purchase order number is 2001.

UPDATE po_xml_tab e
   SET  e.poDoc = XMLType(
   '<?xml version="1.0"?>
   <PO pono="2">
         <STREET>2 Avocet Drive</STREET>
         <CITY>Redwood Shores</CITY>
WHERE e.poDoc.EXTRACT('/PO/PONO/text()').getNumberVal() = 2001;


Updates for non- schema based XML documents always update the whole XML document.

Example 4-32 Updating XMLType Using UPDATE and updateXML()

To update the XML document in the table instead of creating a new one, you can use the updateXML() in the right hand side of an UPDATE statement to update the document.


This will also update the whole document, not just the part updated.

UPDATE po_xml_tab
SET poDoc =  UPDATEXML(poDoc,
   '/PO/CUSTNAME/text()', 'John');

1 row updated

SELECT e.poDoc.getstringval() AS newpo
   FROM po_xml_tab e;

<?xml version="1.0"?>
<PO pono="2">
      <STREET>2 Avocet Drive</STREET>
      <CITY>Redwood Shores</CITY>

Example 4-33 Updating Multiple Elements in the Column Using updateXML()

You can update multiple elements within a single updateXML() expression. For instance, you can use the same UPDATE statement as shown in the preceding example and update purchase order, po:

UPDATE emp_tab e
   SET e.emp_col = UPDATEXML(e.emp_col,
   WHERE EXISTSNODE(e.emp_col, '//EMP') = 1;

This updates all rows that have an employee element with the new values.

Example 4-34 Updating Customer Name in Purchase Order XML Document Using updateXML()

The following example updates the customer name in the purchase order XML document, po:


This example only selects the document and the update occurs on a transient XMLType instance. The original document is not affected.

   '/PO/CUSTNAME/text()', 'John').getstringval() AS updatedPO
   FROM po_xml_tab;

<?xml version="1.0"?>
<PO pono="2">
      <STREET>2 Avocet Drive</STREET>
      <CITY>Redwood Shores</CITY>

Example 4-35 Updating Multiple Transient XML Instances Using updateXML()

You can also use updateXML() to update multiple pieces of a transient instance. For example, consider the following XML document stored in column emp_col of table, emp_tab:


To generate a new document with Joe's salary updated to 100,000, update the Name of Jack to Jackson, and modify the Employee element for 217, to remove the salary element. You can write a query such as:

   FROM emp_tab e;

This generates the following updated XML:


Creating Views of XML Data with updateXML()

You can use updateXML() to create new views of XML data. This can be useful when you do not want a particular set of users to see sensitive data such as SALARY.

Example 4-36 Creating Views Using updateXML()

A view such as:

CREATE VIEW new_emp_view
      UPDATEXML(emp_col, '/EMPLOYEES/EMP/SALARY/text()', 0) emp_view_col
   FROM emp_tab e;

ensures that users selecting from view, new_emp_view, do not see the SALARY field for any employee.

Optimization of updateXML()

In most cases, updateXML() materializes the whole input XML document in memory and updates the values. However, it is optimized for UPDATE statements on XML schema-based object-relationally stored XMLType tables and columns so that the function updates the value directly in the column.

The conditions for rewrite are explained in Chapter 5, "Structured Mapping of XMLType", "Query Rewrite with XML Schema-Based Structured Storage", in detail. If all of the rewrite conditions are met, then the updateXML() is rewritten to update the object-relational columns directly with the values. For example, the following UPDATE statement:

UPDATE po_xml_tab
SET poDoc =  UPDATEXML(poDoc,
   '/PO/CUSTNAME/text()', 'John');

could get rewritten (if the rewrite rules are satisfied) to an UPDATE of the custname column directly:

UPDATE po_xml_tab p
SET p.xmldata.CUSTNAME  =  'John';

updateXML() and NULL Values

If you update an XML element to null, Oracle removes the attributes and children of the element, and the element becomes empty. The type and namespace properties of the element are retained. A NULL value for an element update is equivalent to setting the element to empty.

If you update the text node of an element to null, Oracle removes the text value of the element, and the element itself remains but is empty. For example, if you update node, '/empno/text()' with a NULL value, the text values for the empno element are removed and the empno element becomes empty.

Setting an attribute to NULL, similarly sets the value of the attribute to the empty string.

You cannot use updateXML() to remove, add, or delete a particular element or an attribute. You have to update the containing element with a new value.


Setting 'empno' to NULL has the same effect as setting 'empno/text()' to NULL, if empno is a simple scalar element with no attributes.

Example 4-37 NULL Updates with updateXML()

Consider the XML document:

    <shipAddr gate="xxx">

The clause:


is equivalent to making it:


If you update the text node to NULL, then this is equivalent to removing the text value alone. For example:

UPDATEXML(xmlcol,'/PO/shipAddr/street/text()', null)

results in:


Updating the Same XML Node More Than Once

You can update the same XML node more than once in the updateXML() statement. For example, you can update both /EMP[EMPNO=217] and /EMP[EMPNAME="Jane"]/EMPNO, where the first XPath identifies the EMPNO node containing it as well. The order of updates is determined by the order of the XPath expressions in left-to-right order. Each successive XPath works on the result of the previous XPath update.

XMLTransform() Function

The XMLTransform() function takes in an XMLType instance and an XSLT stylesheet. It applies the stylesheet to the XML document and returns a transformed XML instance. See Figure 4-5.

Figure 4-5 XMLTransform() Syntax

Text description of XMLTransform.gif follows
Text description of the illustration XMLTransform.gif

XMLTransform() is explained in detail in Chapter 6, "Transforming and Validating XMLType Data".

Deleting XML Data

DELETEs on the row containing the XMLType column are handled in the same way as any other datatype.

Example 4-38 Deleting Rows Using extract()

For example, to delete all purchase order rows with a purchase order name of "Po_2", execute a statement such as:

DELETE FROM po_xml_tab e
    WHERE e.poDoc.extract('/PO/PNAME/text()').getStringVal()='Po_2';

Using XMLType In Triggers

You can use the new and old binds inside triggers to read and modify the XMLType column values. For INSERT and UPDATE statements, you can modify the new value to change the value being inserted.

Example 4-39 Creating XMLType Triggers

For example, you can write a trigger to change the purchase order if it does not contain a shipping address:

  pono Number;

if inserting then:

     if :NEW.poDoc.existsnode('//SHIPADDR') = 0 then 
      :NEW.poDoc := xmltype('<PO>INVALID_PO</PO>'); end if;
     end if; 

when updating, if the old poDoc has purchase order number different from the new one then make it an invalid PO.

if updating then:

        if :OLD.poDoc.extract('//PONO/text()').getNumberVal() != 
            :NEW.poDoc.extract('//PONO/text()').getNumberVal() then

          :NEW.poDoc := xmltype('<PO>INVALID_PO</PO>'); 
        end if; 
    end if; 

This example is only an illustration. You can use the XMLType value to perform useful operations inside the trigger, such as validation of business logic or rules that the XML document should conform to, auditing, and so on.

Indexing XMLType Columns

You can create the following indexes when using XMLType. Indexing speeds up query evaluation.

Creating Function-Based Indexes on XMLType Columns

You can speed up by queries by building function-based indexes on existsNode() or those portions of the XML document that use extract().

Example 4-40 Creating a Function-Based Index on an extract() Function

For example, to speed up the search on the query,

SELECT * FROM po_xml_tab e
      WHERE e.poDoc.extract('//PONO/text()').getNumberVal()= 100;

you can create a function-based index on the extract() function as follows:

CREATE INDEX city_index ON po_xml_tab

The SQL query uses this function-based index, to evaluate the predicate instead of parsing the XML document row by row, and evaluating the XPath expression.

Example 4-41 Creating a Function-Based index on an existsNode() Function

You can also create bitmapped function-based indexes to speed up the evaluation of the operators. existsNode() is suitable, since it returns a value of 1 or 0 depending on whether the XPath is satisfied in the XML document or not.

For example, to speed up a query that searches whether the XML document contains an element called Shipping address (SHIPADDR) at any level:

SELECT * FROM po_xml_tab e
      WHERE e.poDoc.existsNode('//SHIPADDR') = 1;

you can create a bitmapped function-based index on the existsNode() function as follows:

CREATE BITMAP INDEX po_index ON po_xml_tab

This speeds up the query processing.

Creating Oracle Text Indexes on XMLType Columns

Oracle Text index works on CLOB and VARCHAR columns. It has been extended in Oracle9i to also work on XMLType columns. The default behavior of Oracle Text index is to automatically create XML sections, when defined over XMLType columns. Oracle Text also provides the CONTAINS operator which has been extended to support XPath.

In general, Oracle Text indexes can be created using the CREATE INDEX SQL statement with the INDEXTYPE specified as for other CLOB or VARCHAR columns. Oracle Text indexes on XMLType columns, however, are created as function-based indexes.

Example 4-42 Creating an Oracle Text Index

CREATE INDEX po_text_index ON
      po_xml_tab(poDoc) indextype is ctxsys.context;

You can also perform Oracle Text operations such as CONTAINS and SCORE. on XMLType columns. In Oracle9i Release (9.0.1), the CONTAINS operator was enhanced to support XPath using two new operators, INPATH and HASPATH:

Example 4-43 Searching XML Data Using HASPATH

For example:

SELECT * FROM po_xml_tab  w
      WHERE CONTAINS(w.poDoc,
           'haspath(/PO[./@CUSTNAME="John Nike"])') > 0;


In Oracle9i Release (9.0.1), to create and use Oracle Text index in queries, in addition to having the privileges for creating indexes and for creating Oracle Text indexes, you also needed privileges and settings for creating function-based indexes:

Oracle Text index uses the PATH_SECTION_GROUP as the default section group when indexing XMLType columns. This default can be overridden during Oracle Text index creation.

With this release, you no longer need the additional QUERY_REWRITE privileges when creating Oracle Text indexes.

See Also:


The QUERY_REWRITE_INTEGRITY and QUERY_REWRITE_ENABLED session settings are no longer needed to create Oracle Text or other function-based indexes on XMLType columns.

Creating XPath Indexes on XMLType Columns: CTXXPATH Index

existsNode() SQL function, unlike the CONTAINS operator, cannot use Oracle Text indexes to speed up its evaluation. To improve the performance of XPath searches in existsNode(), this release introduces a new index type, CTXXPATH.

CTXXPATH index is a new indextype provided by Oracle Text. It is designed to serve as a primary filter for existsNode() processing, that is, it produces a superset of the results that would be produced by the existNode() function. The existsNode() functional implementation is then applied on the results to return the correct set of rows.

CTXXPATH index can handle XPath path searching, wildcards, and string equality predicates.

Example 4-44 Using CTXXPATH Index or existsNode() for XPath Searching

CREATE INDEX po_text_index ON
      po_xml_tab(poDoc) indextype is ctxsys.ctxxpath;

For example, a query such as:

   FROM po_xml_doc  w
   WHERE existsNode(w.poDoc,'/PO[@CUSTNAME="John Nike"]') = 1;

could potentially use CTXXPATH indexing to satisfy the existsNode() predicate.

See Also:

Differences Between CONTAINS and existsNode()/extract()

The differences in XPath support when using CONTAINS compared to XPath support with existsNode() and extract() functions are: