|Oracle9i XML Database Developer's Guide - Oracle XML DB
Release 2 (9.2)
Part Number A96620-02
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:
Oracle9i Release 1 (9.0.1) introduced a new datatype,
XMLType, to facilitate native handling of XML data in the database. The following summarizes
XMLTypecan be used in PL/SQL stored procedures as parameters, return values, and variables.
XMLTypecan represent an XML document as an instance (of
XMLType) in SQL.
XMLTypehas built-in member functions that operate on XML content. For example, you can use
XMLTypefunctions to create, extract, and index XML data stored in Oracle9i database.
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),
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
You can use
XMLType in SQL queries in the same way as any other user-defined datatypes in the system.
XMLType datatype and API provides significant advantages. It enables SQL operations on XML content, as well as XML operations on SQL content:
XMLTypehas a versatile API for application development, as it includes built-in functions, indexing support, navigation, and so on.
XMLTypein SQL statements combined with other columns and datatypes. For example, you can query
XMLTypecolumns and join the result of the extraction with a relational column, and then Oracle can determine an optimal way to execute these queries.
XMLTypeis optimized to not materialize the XML data into a tree structure unless needed. Therefore when SQL selects
XMLTypeinstances inside queries, only a serialized form is exchanged across function boundaries. These are exploded into tree format only when operations such as
xistsNode()are performed. The internal structure of
XMLTypeis also an optimized DOM-like tree structure.
XMLTypecolumns. You can also create function-based indexes on
extract()functions to speed up query evaluation.
XMLType when you need to perform the following:
extract()provide the necessary SQL query functions over XML documents.
existsNode()functions: Note that
XMLTypeuses the built-in C XML parser and processor and hence provides better performance and scalability when used inside the server.
XMLTypehas member functions that you can use to create function-based indexes to optimize searches.
XMLTypeinstead of CLOBs or relational storage allows applications to gracefully move to various storage alternatives later without affecting any of the query or DML statements in the application.
XMLType. Since Oracle9i database is natively aware that
XMLTypecan store XML data, better optimizations and indexing techniques can be done. By writing applications to use
XMLType, these optimizations and enhancements can be easily achieved and preserved in future releases without your needing to rewrite applications.
XMLType data can be stored in two ways or a combination thereof:
XMLTypeoffers a CLOB storage option. In future releases, Oracle may provide other storage options, such as BLOBs, NCLOBS, and so on. You can also create a CLOB-based storage for XML schema-based storage.
When you create an
XMLType column without any XML schema specification, a hidden CLOB column is automatically created to store the XML data. The
XMLType column itself becomes a virtual column over this hidden CLOB column. It is not possible to directly access the CLOB column; however, you can set the storage characteristics for the column using the
XMLType storage clause.
XMLTypeachieves DOM fidelity by maintaining information that SQL or Java objects normally do not provide for, such as:
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
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.
Table 4-1 summarizes some advantages and disadvantages to consider when selecting your Oracle XML DB storage option.
|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
Can consume considerable space.
Needs less space in particular when used with an Oracle XML DB registered XML schema.
Use CLOB storage for
XMLType in the following cases:
XMLType and Varray:
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.
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.
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
This section shows some simple examples of how to create an
XMLType column and use it in a SQL statement, and how to create
The following are examples of creating, adding, and dropping
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));
As explained, you can create
XMLType columns by simply using the
XMLType as the datatype. The following statement creates a purchase order document column,
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.
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:
You can alter tables to drop
XMLType columns, similar to any other datatype. The following statement drops column
To insert values into the
XMLType column, you need to bind an
XMLType instance can be easily created from a VARCHAR or a Character Large Object (CLOB) by using the
INSERT INTO warehouses VALUES ( 100, XMLType( '<Warehouse whNo="100"> <Building>Owned</Building> </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.
The following simple SELECT statement shows how you can use
XMLType in an SQL statement:
SELECT w.warehouse_spec.extract('/Warehouse/Building/text()').getStringVal() "Building" 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):
An XML document in an
XMLType can be stored packed in a CLOB. Then updates have to replace the whole document in place.
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"> <Building>Leased</Building> </Warehouse>');
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 is no different from deleting a row containing any other datatype.
You can use
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
The following are guidelines for storing XML data in
XMLType tables and columns:
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 constructor to create the
XMLType instance before inserting into the column/table. You can also use a variety of other functions that return
"SYS_XMLGEN(): Converting an XMLType Instance" , for an example.
You can select out the
XMLType instance from the column.
XMLType also offers a choice of member functions, such as
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.
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
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.
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
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.
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 STORE AS CLOB ( TABLESPACE lob_seg_ts STORAGE (INITIAL 4096 NEXT 4096) CHUNK 4096 NOCACHE LOGGING );
In non- schema-based storage, you can use
XMLDATA to change storage characteristics on an
For example, consider table
To change the storage characteristics of LOB column
foo_tab, you can use the following statement:
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
You can use the
XMLDATA attribute in constraints and indexes, in addition to storage clauses.
Oracle9i Application Developer's Guide - Large Objects (LOBs) f and Oracle9i SQL Reference for more information about LOB storage options
You can specify NOT NULL constraint on an
prevents inserts such as:
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:
You can also define check constraints on
XMLType columns. Other default values are not supported on this datatype.
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:
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
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:
XMLTypeconstructors. This can be done in SQL, PL/SQL, and Java.
XMLAGG(). This can be done in SQL, PL/SQL, and Java.
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"> <PNAME>Po_1</PNAME> <CUSTNAME>John</CUSTNAME> <SHIPADDR> <STREET>1033, Main Street</STREET> <CITY>Sunnyvalue</CITY> <STATE>CA</STATE> </SHIPADDR> </PO>');
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
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.
This example inserts a purchase order into table
po_tab using the
INSERT INTO po_xml_tab VALUES(100, XMLType('<?xml version="1.0"?> <PO pono="1"> <PNAME>Po_1</PNAME> <CUSTNAME>John</CUSTNAME> <SHIPADDR> <STREET>1033, Main Street</STREET> <CITY>Sunnyvalue</CITY> <STATE>CA</STATE> </SHIPADDR> </PO>'));
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".
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.
You can query XML data from
XMLType columns in the following ways:
XMLTypecolumns through SQL, PL/SQL, or Java
XMLTypecolumns directly and using
SQL functions such as
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.
You can select
XMLType data using PL/SQL or Java. You can also use the
getNumberVal() functions to retrieve XML as a CLOB, VARCHAR, or NUMBER, respectively.
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; POXML --------------------- <?xml version="1.0"?> <PO pono="2"> <PNAME>Po_2</PNAME> <CUSTNAME>Nance</CUSTNAME> <SHIPADDR> <STREET>2 Avocet Drive</STREET> <CITY>Redwood Shores</CITY> <STATE>CA</STATE> </SHIPADDR> </PO>
You can query
XMLType data and extract portions of it using the
extract() functions. Both these functions use a subset of the W3C XPath recommendation to navigate the document.
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.
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 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.
You can select
XMLType data through PL/SQL, OCI, or Java. You can also use the
getNumberVal() functions to retrieve the XML as a CLOB, VARCHAR or a number, respectively.
This example shows how to select an
XMLType column using
set long 2000 SELECT e.poDoc.getClobval() AS poXML FROM po_xml_tab e WHERE e.poDoc.existsNode('/PO[PNAME = "po_2"]') = 1; POXML --------------------- <?xml version="1.0"?> <PO pono="2"> <PNAME>Po_2</PNAME> <CUSTNAME>Nance</CUSTNAME> <SHIPADDR> <STREET>2 Avocet Drive</STREET> <CITY>Redwood Shores</CITY> <STATE>CA</STATE> </SHIPADDR>
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) RETURN NUMBER
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).
For example, consider an XML document such as:
<PO> <PONO>100</PONO> <PNAME>Po_1</PNAME> <CUSTOMER CUSTNAME="John"/> <SHIPADDR> <STREET>1033, Main Street</STREET> <CITY>Sunnyvalue</CITY> <STATE>CA</STATE> </SHIPADDR> </PO>
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.
existsNode() member function can be used in queries and to create function-based indexes to speed up evaluation of queries.
The following example tests for the existence of the
/Warehouse/Dock node in the
warehouse_spec column XML path of the sample table
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
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.
unction is similar to the e
xistsNode() function. It applies a
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;
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.
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
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
The latter is still considered an
text() node test function to identify text nodes in elements before using the
getNumberVal() to convert them to SQL data. Not having the
text() node would produce an XML fragment.
For example, XPath expressions:
/PO/PNAMEidentifies the fragment
/PO/PNAME/text()identifies the text value "
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:
//PONOto identify the first "PONO" element (with value 100).
//PONOto identify the second "PONO" element (with value 200).
The result of
extract() is always an
XMLType. If applying the XPath produces an empty set, then
extract() returns a NULL value.
extract() member function can be used in a number of ways, including the following:
This example extracts the value of node,
/Warehouse/Docks, of column,
warehouse_spec in table
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 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
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.
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
extractValue() as follows:
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
extractValue() has the following characteristics:
extractValue()can automatically return the appropriate datatype based on the XML schema information, if it can detect so at compile time of the query. For instance, if the XML schema information for the path
/PO/POIDindicates that this is a numerical value, then
extractValue()returns a NUMBER.
extracts out the text child of
PNAME. This is equivalent to:
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') "Docks" FROM warehouses e WHERE warehouse_spec IS NOT NULL; WAREHOUSE_NAME Docks -------------------- ------------ 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:
The following SQL examples illustrate ways you can query XML.
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"?> <PO> <PONO>221</PONO> <PNAME>PO_2</PNAME> </PO>')); INSERT INTO po_xml_tab values (200, xmltype('<?xml version="1.0"?> <PO> <PONAME>PO_1</PONAME> </PO>'));
Now you can extract the numerical values for the purchase order numbers using
SELECT e.poDoc.extract('//PONO/text()').getNumberVal() as pono FROM po_xml_tab e WHERE e.podoc.existsnode('/PO/PONO') = 1 AND poid > 1;
extract() extracts the contents of tag, purchase order number, "PONO".
existsNode() finds nodes where "PONO" exists as a child of "PO".
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 declare poxml XMLType; cust XMLType; val VARCHAR2(200); begin -- 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); end; /
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:
CREATE TABLE cust_tab ( 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'?> <PO> <PONO>2001</PONO> <PNAME>Po_1</PNAME> <CUSTOMER CUSTNAME="John Nike"/> <SHIPADDR> <STREET>323 College Drive</STREET> <CITY>Edison</CITY> <STATE>NJ</STATE> <ZIP>08820</ZIP> </SHIPADDR> </PO>
into the relational tables, using extract().
Here is an SQL example assuming that the XML described in the previous example is present in the
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:
PONO PNAME CUSTID SHIPSTREET SHIPCITY SHIPZIP ---------------------------------------------------------------- 2001 Po_1 1001 323 College Drive Edison 08820
You can do the same in an equivalent fashion inside a PL/SQL block, as follows:
DECLARE poxml XMLType; cname varchar2(200); pono number; pname varchar2(100); shipstreet varchar2(100); shipcity varchar2(30); shipzip varchar2(20); BEGIN -- 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); END; /
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".
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%';
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:
You cannot insert fragments into
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.
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
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() 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.
updateXML() updates only the transient XML instance in memory. Use an SQL UPDATE statement to update data stored in tables. The
updateXML() syntax is:
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"> <PNAME>Po_2</PNAME> <CUSTNAME>Nance</CUSTNAME> <SHIPADDR> <STREET>2 Avocet Drive</STREET> <CITY>Redwood Shores</CITY> <STATE>CA</STATE> </SHIPADDR> </PO>') WHERE e.poDoc.EXTRACT('/PO/PONO/text()').getNumberVal() = 2001;
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.
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; NEWPO -------------------------------------------------------------------- <?xml version="1.0"?> <PO pono="2"> <PNAME>Po_2</PNAME> <CUSTNAME>John</CUSTNAME> <SHIPADDR> <STREET>2 Avocet Drive</STREET> <CITY>Redwood Shores</CITY> <STATE>CA</STATE> </SHIPADDR> </PO>
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,
UPDATE emp_tab e SET e.emp_col = UPDATEXML(e.emp_col, '/EMPLOYEES/EMP[EMPNAME="Joe"]/SALARY/text()',100000, '//EMP[EMPNAME="Jack"]/EMPNAME/text()','Jackson', '//EMP[EMPNO=217]',XMLTYPE.CREATEXML( '<EMP><EMPNO>217</EMPNO><EMPNAME>Jane</EMPNAME></EMP>')) WHERE EXISTSNODE(e.emp_col, '//EMP') = 1;
This updates all rows that have an employee element with the new values.
The following example updates the customer name in the purchase order XML document,
This example only selects the document and the update occurs on a transient
SELECT UPDATEXML(poDoc, '/PO/CUSTNAME/text()', 'John').getstringval() AS updatedPO FROM po_xml_tab; UPDATEDPO -------------------------------------------------------------------- <?xml version="1.0"?> <PO pono="2"> <PNAME>Po_2</PNAME> <CUSTNAME>John</CUSTNAME> <SHIPADDR> <STREET>2 Avocet Drive</STREET> <CITY>Redwood Shores</CITY> <STATE>CA</STATE> </SHIPADDR> </PO>
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:
<EMPLOYEES> <EMP> <EMPNO>112</EMPNO> <EMPNAME>Joe</EMPNAME> <SALARY>50000</SALARY> </EMP> <EMP> <EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> <SALARY>60000</SALARY> </EMP> <EMP> <EMPNO>412</EMPNO> <EMPNAME>Jack</EMPNAME> <SALARY>40000</SALARY> </EMP> </EMPLOYEES>
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:
SELECT UPDATEXML(emp_col, '/EMPLOYEES/EMP[EMPNAME="Joe"]/SALARY/text()', 100000, '//EMP[EMPNAME="Jack"]/EMPNAME/text()','Jackson', '//EMP[EMPNO=217]', XMLTYPE.CREATEXML('<EMP><EMPNO>217</EMPNO><EMPNAME>Jane</EMPNAME>')) FROM emp_tab e;
This generates the following updated XML:
<EMPLOYEES> <EMP> <EMPNO>112</EMPNO> <EMPNAME>Joe</EMPNAME> <SALARY>100000</SALARY> </EMP> <EMP> <EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> </EMP> <EMP> <EMPNO>412</EMPNO> <EMPNAME>Jackson</EMPNAME> <SALARY>40000</SALARY> </EMP> </EMPLOYEES>
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.
A view such as:
CREATE VIEW new_emp_view AS SELECT 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.
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:
could get rewritten (if the rewrite rules are satisfied) to an UPDATE of the
custname column directly:
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.
Consider the XML document:
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:
You can update the same XML node more than once in the
updateXML() statement. For example, you can update both
/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 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.
XMLTransform() is explained in detail in Chapter 6, "Transforming and Validating XMLType Data".
DELETEs on the row containing the
XMLType column are handled in the same way as any other datatype.
For example, to delete all purchase order rows with a purchase order name of "Po_2", execute a statement such as:
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.
For example, you can write a trigger to change the purchase order if it does not contain a shipping address:
CREATE OR REPLACE TRIGGER po_trigger BEFORE INSERT OR UPDATE ON po_xml_tab FOR EACH ROW declare pono Number; begin
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; end; /
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.
You can create the following indexes when using
XMLType. Indexing speeds up query evaluation.
You can speed up by queries by building function-based indexes on
existsNode() or those portions of the XML document that use
For example, to speed up the search on the query,
you can create a function-based index on the
extract() function as follows:
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.
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
SHIPADDR) at any level:
you can create a bitmapped function-based index on the
existsNode() function as follows:
This speeds up the query processing.
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.
You can also perform Oracle Text operations such as
XMLType columns. In Oracle9i Release (9.0.1), the
CONTAINS operator was enhanced to support XPath using two new operators,
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:
QUERY_REWRITEprivilege. You must have this privilege granted to create text indexes on
XMLTypecolumns in your own schema.
GLOBAL_QUERY_REWRITEprivilege. If you need to create Oracle Text indexes on
XMLTypecolumns in other schemas or on tables residing in other schemas, you must have this privilege granted.
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.
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 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.
For example, a query such as:
could potentially use
CTXXPATH indexing to satisfy the
The differences in XPath support when using
CONTAINS compared to XPath support with
extract() functions are:
the XPath expression - A/B/E falsely matches the preceding XML document.
extract()operations on the remainder of the XML documents.