Skip Headers
Oracle® XML DB Developer's Guide
11g Release 1 (11.1)

B28369-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 XMLType Operations

This chapter describes XMLType operations for XML applications (schema-based and non-schema-based). It includes guidelines for creating, manipulating, updating, and querying XMLType columns and tables.

This chapter contains these topics:

See Also:

Selecting and Querying XML Data

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

Searching XML Documents with XPath Expressions

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

Note:

Oracle SQL functions and XMLType methods respect the W3C XPath recommendation, which states that if an XPath expression targets no nodes when applied to XML data, then an empty sequence must be returned; an error must not be raised.

The specific semantics of an Oracle SQL function or XMLType method that applies an XPath-expression to XML data determines what is returned. For example, SQL function extract returns NULL if its XPath-expression argument targets no nodes, and the updating SQL functions, such as deleteXML, return the input XML data unchanged. An error is never raised if no nodes are targeted, but updating SQL functions may raise an error if an XPath-expression argument targets inappropriate nodes, such as attribute nodes or text nodes.

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

Table 4-1 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, /PurchaseOrder/Reference identifies the purchase-order name element Reference, a child of the root element.

//

Used to identify all descendants of the current node. For example, PurchaseOrder//ShippingInstructions matches any ShippingInstructions element under the PurchaseOrder 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 selects the shipping address element of all purchase orders whose purchase-order number is 20 and whose purchase-order name is PO_2.

Brackets are also used to denote a position (index). For example, /PO/PONO[2] identifies the second purchase-order number element under the PO root element.

Functions

XPath supports a set of built-in functions such as substring(), round(), and not(). In addition, XPath provides for extension functions through the use of namespaces. In the Oracle namespace, http://xmlns.oracle.com/xdb, Oracle XML DB additionally supports the function ora:contains(). This functions behaves like the equivalent SQL function.


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

Oracle Extension XPath Function Support

Oracle supports the XPath extension function ora:contains(). This function provides text searching functionality with XPath.

Selecting XML Data Using XMLType Methods

You can select XMLType data using PL/SQL, C, or Java. You can also use the XMLType methods getCLOBVal(), getStringVal(), getNumberVal(), and getBLOBVal(csid) to retrieve XML data as a CLOB, VARCHAR, NUMBER, and BLOB value, respectively.

Example 4-1 Selecting XMLType Columns Using Method getCLOBVal()

This example shows how to select an XMLType column using method getCLOBVal():

CREATE TABLE xml_table OF XMLType;
 
Table created.
 
CREATE TABLE table_with_xml_column (filename VARCHAR2(64), xml_document XMLType);
 
Table created.
 
INSERT INTO xml_table
  VALUES (XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'),
          nls_charset_id('AL32UTF8')));
 
1 row created.
 
INSERT INTO table_with_xml_column (filename, xml_document)
  VALUES ('purchaseOrder.xml',
          XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'),
          nls_charset_id('AL32UTF8')));
 
1 row created.
 
SELECT x.OBJECT_VALUE.getCLOBVal() FROM xml_table x;
 
X.OBJECT_VALUE.GETCLOBVAL()
--------------------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
paceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOr
der.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>400 Oracle Parkway
      Redwood Shores
... 
 
1 row selected.
 
--
SELECT x.xml_document.getCLOBVal() FROM table_with_xml_column x;
 
X.XML_DOCUMENT.GETCLOBVAL()
--------------------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
paceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOr
der.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>400 Oracle Parkway
      Redwood Shores
... 
 
1 row selected.

Note:

In some circumstances, XMLType method getCLOBVal() returns a temporary CLOB value. If you call getCLOBVal() programmatically, you must explicitly free such a temporary CLOB value when finished with it. You can do this by calling PL/SQL method DBMS_LOB.freeTemporary() or its equivalent in Java or C (OCI). You can use method DBMS_LOB.isTemporary() to test whether a CLOB value is temporary.

Querying XMLType Data with SQL Functions

You can query XMLType data and extract portions of it using SQL functions, including the following:

  • SQL/XML standard functions XMLQuery, XMLTable, XMLExists, and XMLCast

  • Oracle functions existsNode, extract, and extractValue

The Oracle functions use a subset of the W3C XPath recommendation to navigate the document. See Chapter 18, "Using XQuery with Oracle XML DB" for information about functions XMLQuery and XMLTable. The other functions are described in the following sections.

XMLEXISTS SQL Function

Figure 4-1 describes the syntax for SQL/XML standard function XMLExists. This function checks whether a given XQuery expression returns a non-empty XQuery sequence. If so, the function returns TRUE; otherwise, it returns FALSE.

Figure 4-1 XMLExists Syntax

Description of Figure 4-1 follows
Description of "Figure 4-1 XMLExists Syntax"

XML_passing_clause ::=

Description of xml_passing_clause.gif follows
Description of the illustration xml_passing_clause.gif

  • XQuery_string is a complete XQuery expression, possibly including a prolog, as a literal string. It can contain XQuery variables that you bind using the XQuery PASSING clause (XML_passing_clause in the syntax diagram). The predefined namespace prefixes recognized for SQL function XMLQuery are also recognized in XQuery_string — see "Predefined Namespaces and Prefixes".

  • The XML_passing_clause is the keyword PASSING followed by one or more SQL expressions (expr) that each return an XMLType instance or an instance of a SQL scalar data type. All but possibly one of the expressions must each be followed by the keyword AS and an XQuery identifier. The result of evaluating each expr is bound to the corresponding identifier for the evaluation of XQuery_string. If there is an expr that is not followed by an AS clause, then the result of evaluating that expr is used as the context item for evaluating XQuery_string. Oracle XML DB supports only passing BY VALUE, not passing BY REFERENCE, so the clause BY VALUE is implicit and can be omitted.

Standard function XMLExists is similar to Oracle function existsNode, but it differs in these ways:

  • XMLExists accepts an arbitrary XQuery expression (possibly including a prolog); existsNode accepts only an XPath expression (XPath is a proper subset of XQuery).

  • XMLExists tests whether its XQuery-expression argument returns a non-empty sequence; existsNode tests whether its XPath-expression argument targets at least one element node or text node. The set of XPath expressions is a proper subset of the XQuery expressions.

  • XMLExists returns a Boolean value, TRUE or FALSE; existsNode returns 1 or 0.

Oracle recommends that you use XMLExists instead of existsNode.

If an XQuery expression such as /PurchaseOrder/Reference or /PurchaseOrder/Reference/text() targets a single node, then XMLExists returns true for that expression. If XMLExists is called with an XQuery expression that locates no nodes, then XMLExists returns false.

Function XMLExists can be used in queries, and it can be used to create function-based indexes to speed up evaluation of queries.

Note:

Oracle XML DB limits the use of XMLExists to a SQL WHERE clause or CASE expression. If you need to use XMLExists in a SELECT list, then wrap it in a CASE expression:
CASE WHEN XMLExists(...) THEN 'TRUE' ELSE 'FALSE' END

Example 4-2 Using XMLExists to Find a node

This example uses SQL/XML standard function XMLExists to select rows with SpecialInstructions set to Expedite. Compare Example 4-3, which uses Oracle SQL function existsNode to do the same thing.

SELECT OBJECT_VALUE
  FROM purchaseorder
  WHERE XMLExists('/PurchaseOrder[SpecialInstructions="Expedite"]'
                  PASSING OBJECT_VALUE);
 
OBJECT_VALUE
--------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 
13 rows selected.

You can create function-based indexes using SQL function XMLExists to speed up the execution. You can also create an XMLIndex index to help speed up arbitrary XQuery searching.

EXISTSNODE SQL Function

Figure 4-2 describes the syntax for SQL function existsNode.

Figure 4-2 EXISTSNODE Syntax

Description of Figure 4-2 follows
Description of "Figure 4-2 EXISTSNODE Syntax"

Oracle SQL function existsNode checks whether the given XPath path targets at least one XML element node or text node. If so, the function returns 1; otherwise, it returns 0. Optional parameter namespace_string is used to map the namespace prefixes specified in parameter XPath_string to the corresponding namespaces.

Oracle function existsNode predates the SQL/XML standard function XMLExists. Oracle recommends that you use XMLExists instead of existsNode in new code. Function existsNode differs from XMLExists in these ways:

  • XMLExists accepts an arbitrary XQuery expression (possibly including a prolog); existsNode accepts only an XPath expression (XPath is a proper subset of XQuery).

  • XMLExists tests whether its XQuery-expression argument returns a non-empty sequence; existsNode tests whether its XPath-expression targets at least one element node or text node.

  • XMLExists returns an XQuery Boolean value; existsNode returns 1 or 0.

If an XPath expression such as /PurchaseOrder/Reference or /PurchaseOrder/Reference/text() targets a single node, then existsNode returns 1 for that expression. If existsNode is called with an XPath expression that locates no nodes, then existsNode returns 0.

Function existsNode can be used in queries, and it can be used to create function-based indexes to speed up evaluation of queries.

Note:

When using SQL function existsNode in a query, always use it in the WHERE clause, never in the SELECT list.

Example 4-3 Using EXISTSNODE to Find a node

This example uses SQL function existsNode to select rows with SpecialInstructions set to Expedite. Compare Example 4-2, which uses SQL/XML standard function XMLExists to do the same thing.

SELECT OBJECT_VALUE
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[SpecialInstructions="Expedite"]') 
        = 1;
 
OBJECT_VALUE
----------------------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 
13 rows selected.

You can create function-based indexes using SQL function existsNode to speed up the execution. You can also create an XMLIndex index to help speed up arbitrary XPath searching.

EXTRACT SQL Function

SQL function extract is similar to existsNode. It accepts a VARCHAR2 XPath string that targets a node set and an optional namespace parameter. It returns an XMLType instance containing an XML fragment. The syntax is described in Figure 4-3:

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

Figure 4-3 EXTRACT Syntax

Description of Figure 4-3 follows
Description of "Figure 4-3 EXTRACT Syntax"

Note:

You can use SQL/XML function XMLQuery as an alternative to extract. Using XMLQuery is generally recommended, because it is a standard function, not Oracle-specific.

Applying extract to an XMLType value extracts the node or a set of nodes from the document identified by the XPath expression. The XPath argument must target a node set. So, for example, XPath expression /a/b/c[count('//d')=4] can be used, but count('//d') cannot, because it returns a scalar value (number).

The extracted nodes can be element, attribute, or text nodes. If multiple text nodes are referenced in the XPath expression, the text nodes are collapsed into a single text node value. Namespace can be used to supply namespace information for prefixes in the XPath expression.

The XMLType instance returned from extract need not be a well-formed XML document. It can contain a set of nodes or simple scalar data. You can use XMLType methods getStringVal() and getNumberVal() to extract the scalar data.

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

Note:

A text node is considered an instance of XMLType. In other words, the following expression returns an XMLtype instance even though the instance may contain only text:
extract(OBJECT_VALUE, '/PurchaseOrder/Reference/text()')

You can use method getStringVal() to retrieve the text from the XMLType instance as a VARCHAR2 value.

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

For example:

  • XPath /PurchaseOrder/Reference identifies the fragment <Reference> ... </Reference>

  • XPath /PurchaseOrder/Reference/text() identifies the value of the text node of the Reference element.

You can use XPath position predicates (sometimes called indexes) to identify individual elements in case of repeated elements in an XML document. If you have an XML document such as that in Example 4-4, then you can use:

  • XPath expression //LineItem[1] to identify the first LineItem element.

  • XPath expression //LineItem[2] to identify the second LineItem element.

Example 4-4 Purchase-Order XML Document

<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:noNamespaceSchemaLocation=
    "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>400 Oracle Parkway
      Redwood Shores
      CA
      94065
      USA</address>
    <telephone>650 506 7400</telephone>
  </ShippingInstructions>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
      <Description>The Unbearable Lightness Of Being</Description>
      <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="3">
      <Description>Sisters</Description>
      <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>

The result of SQL function extract is always an XMLType instance. If applying the XPath path produces an empty set, then extract returns a NULL value.

SQL function extract can be used in a number of ways. You can extract:

  • Numerical values on which function-based indexes can be created to speed up processing

  • Collection expressions for use in the FROM clause of SQL statements

  • Fragments for later aggregation to produce different documents

Example 4-5 Using EXTRACT to Extract the Value of a Node

This example uses SQL function extract to retrieve the Reference children of PurchaseOrder nodes whose SpecialInstructions attribute has value Expedite.

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Reference') "REFERENCE"
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[SpecialInstructions="Expedite"]')
        = 1;
 
REFERENCE
------------------------------------------------------------
<Reference>AMCEWEN-20021009123336271PDT</Reference>
<Reference>SKING-20021009123336321PDT</Reference>
<Reference>AWALSH-20021009123337303PDT</Reference>
<Reference>JCHEN-20021009123337123PDT</Reference>
<Reference>AWALSH-20021009123336642PDT</Reference>
<Reference>SKING-20021009123336622PDT</Reference>
<Reference>SKING-20021009123336822PDT</Reference>
<Reference>AWALSH-20021009123336101PDT</Reference>
<Reference>WSMITH-20021009123336412PDT</Reference>
<Reference>AWALSH-20021009123337954PDT</Reference>
<Reference>SKING-20021009123338294PDT</Reference>
<Reference>WSMITH-20021009123338154PDT</Reference>
<Reference>TFOX-20021009123337463PDT</Reference>
 
13 rows selected.

Note:

SQL function extractValue and XMLType method getStringVal() differ in their treatment of entity encoding. Function extractValue unescapes any encoded entities; method getStringVal() returns the data with entity encoding intact.

XMLCAST SQL Function

Figure 4-4 describes the syntax for SQL/XML standard function XMLCast.

Figure 4-4 XMLCast Syntax

Description of Figure 4-4 follows
Description of "Figure 4-4 XMLCast Syntax"

SQL/XML standard function XMLCast casts its first argument to the scalar SQL data type specified by its second argument. The first argument is a SQL expression that is evaluated. Data types NUMBER, VARCHAR2, and any of the date and time data types can be used as the second argument.

Note:

Unlike the SQL/XML standard, Oracle XML DB limits the use of XMLCast to cast XML to a SQL scalar data type; it does not support casting XML to XML or from a scalar SQL type to XML.

The result of evaluating the first XMLCast argument is an XML value. It is converted to the target SQL data type by using the XQuery atomization process and then casting the XQuery atomic values to the target data type. If this conversion fails, then an error is raised. If conversion succeeds, the result returned is an instance of the target data type.

Standard function XMLCast is similar to Oracle function extractValue, but it differs in that extractValue does not allow or require you to specify a target data type. In this, extractValue can sometimes be more convenient. XMLCast gives you the advantage of control over the data type, in addition to portability. If the SQL scalar data type cannot be determined at compile time, extractValue returns a value of time VARCHAR2(4000), which might not always be what you expect or want. You can work around this obstacle by using the SQL function cast, but XMLCast is a better choice in this case.

Example 4-6 Extracting the Scalar Value of an XML Fragment Using XMLCAST

This query extracts the scalar value of the Reference node. Compare Example 4-7, which uses Oracle SQL function extractValue to do the same thing. This extraction of the scalar value of the node is in contrast to Example 4-5, where function extract retrieves the <Reference> node itself.

SELECT XMLCast(XMLQuery('/PurchaseOrder/Reference' PASSING OBJECT_VALUE
                                                   RETURNING CONTENT)
               AS VARCHAR2(100)) "REFERENCE"
  FROM purchaseorder
  WHERE XMLExists('/PurchaseOrder[SpecialInstructions="Expedite"]'
                  PASSING OBJECT_VALUE);
 
REFERENCE
----------------------------
AMCEWEN-20021009123336271PDT
SKING-20021009123336321PDT
AWALSH-20021009123337303PDT
JCHEN-20021009123337123PDT
AWALSH-20021009123336642PDT
SKING-20021009123336622PDT
SKING-20021009123336822PDT
AWALSH-20021009123336101PDT
WSMITH-20021009123336412PDT
AWALSH-20021009123337954PDT
SKING-20021009123338294PDT
WSMITH-20021009123338154PDT
TFOX-20021009123337463PDT
 
13 rows selected.

You can create function-based indexes using SQL function existsNode to speed up the execution. You can also create an XMLIndex index to help speed up arbitrary XPath searching.

EXTRACTVALUE SQL Function

SQL function extractValue takes as parameters an XMLType instance and an XPath expression that targets a node set. It returns a scalar SQL value corresponding to the result of the XPath evaluation for the XMLType instance.

  • XML schema-based documents – For documents based on XML schema, if Oracle Database can infer the type of the return value, then a scalar value of the appropriate type is returned. Otherwise, the result is of type VARCHAR2.

  • Non-schema-based documents – If the query containing extractValue can be rewritten, such as when the query is over a SQL/XML view, then a scalar value of the appropriate type is returned. Otherwise, the result is of type VARCHAR2.

Figure 4-5 describes the extractValue syntax.

Figure 4-5 EXTRACTVALUE Syntax

Description of Figure 4-5 follows
Description of "Figure 4-5 EXTRACTVALUE Syntax"

SQL function extractValue attempts to determine the proper return type from the XML schema associated with the document, or from other information such as the SQL/XML view. If the proper return type cannot be determined, then Oracle XML DB returns a VARCHAR2. With XML schema-based content, extractValue returns the underlying data type in most cases. CLOB values are returned directly.

If a specific data type is desired, then you can apply a conversion function such as to_char or to_date to the result of extractValue or extract.getStringVal(). This can help maintain consistency between different queries, regardless of whether the queries can be rewritten.

Use EXTRACTVALUE for Convenience

SQL function extractValue lets you extract the desired value more easily than extract; it is a convenience function. You can use it in place of extract().getStringVal() or extract().getnumberval().

For example, you can replace extract(x, 'path/text()').getStringVal() with extractValue(x, 'path/text()'). If the node at path has only one child and that child is a text node, then you can leave the text() test off of the XPath argument: extractValue(x, 'path'). If not, an error is raised if you leave off text().

SQL function extractValue has the same syntax as function extract.

EXTRACTVALUE Characteristics

SQL function extractValue has the following characteristics:

  • It returns only a scalar value (NUMBER, VARCHAR2, and so on). It cannot return XML nodes or mixed content. An error is raised if extractValue cannot return a scalar value.

  • By default, it returns a VARCHAR2 value. If the length is greater than 4K, a run-time error is raised.

  • If XML schema information is available at query compile time, then the data type of the returned value is based on the XML schema information. For instance, if the XML schema information for the XPath /PurchaseOrder/LineItems/LineItem[1]/Part/@Quantity indicates a number, then extractValue returns a NUMBER.

  • If extractValue is applied to a SQL/XML view and the data type of the column can be determined from the view definition at compile time, the appropriate type is returned.

  • If the XPath argument identifies a node, then the node must have exactly one text child (or an error is raised). The text child is returned. For example, this expression extracts the text child of the Reference node:

    extractValue(xmlinstance, '/PurchaseOrder/Reference')
     
    
  • The XPath argument must target a node set. So, for example, XPath expression /a/b/c[count('//d')=4] can be used, but count('//d') cannot, because it returns a scalar value (number).

Example 4-7 Extracting the Scalar Value of an XML Fragment Using EXTRACTVALUE

This query extracts the scalar value of the Reference node. Compare Example 4-6, which uses SQL/XML standard function XMLCast to do the same thing.

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') "REFERENCE"
  FROM purchaseorder
  WHERE XMLExists('/PurchaseOrder[SpecialInstructions="Expedite"]'
                  PASSING OBJECT_VALUE);
 
REFERENCE
----------------------------
AMCEWEN-20021009123336271PDT
SKING-20021009123336321PDT
AWALSH-20021009123337303PDT
JCHEN-20021009123337123PDT
AWALSH-20021009123336642PDT
SKING-20021009123336622PDT
SKING-20021009123336822PDT
AWALSH-20021009123336101PDT
WSMITH-20021009123336412PDT
AWALSH-20021009123337954PDT
SKING-20021009123338294PDT
WSMITH-20021009123338154PDT
TFOX-20021009123337463PDT
 
13 rows selected.

Note:

Function extractValue and XMLType method getStringVal() differ in their treatment of entity encoding. Function extractValue unescapes any encoded entities; method getStringVal() returns the data with entity encoding intact.
EXTRACTVALUE XPath Expression Must Match A Single Leaf Node

Example 4-8 shows some incorrect uses of SQL function extractValue. In the first query, the XPath expression identifies a parent node, not a leaf node (text node or attribute value). In the second query, the XPath expression matches three nodes in the document — it must match only one. Which error is raised for the second query depends on whether or not XPath rewrite takes place.

Example 4-8 Invalid Uses of EXTRACTVALUE

SELECT extractValue(OBJECT_VALUE,'/PurchaseOrder/LineItems/LineItem[1]')
  FROM purchaseorder;
  FROM purchaseorder
     *
ERROR at line 3:
ORA-19026: EXTRACTVALUE can only retrieve value of leaf node


SELECT extractValue(OBJECT_VALUE,
                    '/PurchaseOrder/LineItems/LineItem/Description')
  FROM purchaseorder;
SELECT extractValue(OBJECT_VALUE,
                    '/PurchaseOrder/LineItems/LineItem/Description')
 *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

If XPath rewrite does not occur, then this query can instead raise the following error:

ORA-19025: EXTRACTVALUE returns value of only one node

Querying XML Data With SQL

The following examples illustrate ways you can query XML data with SQL.

Example 4-9 Querying XMLType Using EXTRACTVALUE and EXISTSNODE

This example inserts two rows into the purchaseorder table, then queries data in those rows using extractValue.

INSERT INTO purchaseorder 
  VALUES (XMLType(bfilename('XMLDIR', 'SMCCAIN-2002091213000000PDT.xml'),
                  nls_charset_id('AL32UTF8')));
 
1 row created.
 
INSERT INTO purchaseorder
  VALUES (XMLType(bfilename('XMLDIR', 'VJONES-20020916140000000PDT.xml'),
                  nls_charset_id('AL32UTF8')));
 
1 row created.
 
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') REFERENCE,
       extractValue(OBJECT_VALUE, '/PurchaseOrder/*//User') USERID,
       CASE
         WHEN existsNode(OBJECT_VALUE, '/PurchaseOrder/Reject/Date') = 1
           THEN 'Rejected'
           ELSE 'Accepted'
       END "STATUS",
       extractValue(OBJECT_VALUE, '//Date') STATUS_DATE
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,'//Date') = 1
  ORDER BY extractValue(OBJECT_VALUE,'//Date');
 
REFERENCE                        USERID   STATUS   STATUS_DATE
-------------------------------- -------- -------- ------------
VJONES-20020916140000000PDT      SVOLLMAN Accepted 2002-10-11
SMCCAIN-2002091213000000PDT      SKING    Rejected 2002-10-12
 
2 rows selected.

Example 4-10 Querying Transient XMLType Data

This example uses a PL/SQL cursor to query XML data. A local XMLType instance is used to store transient data.

DECLARE
  xNode          XMLType;
  vText          VARCHAR2(256);
  vReference     VARCHAR2(32);
  CURSOR getPurchaseOrder(reference IN VARCHAR2) IS
           SELECT OBJECT_VALUE XML
             FROM purchaseorder
             WHERE existsNode(OBJECT_VALUE,
                              '/PurchaseOrder[Reference="'|| reference || '"]') 
                   = 1;
BEGIN
  vReference := 'EABEL-20021009123335791PDT';
  FOR c IN getPurchaseOrder(vReference) LOOP
    xNode := c.XML.extract('//Requestor');
    vText := xNode.extract('//text()').getStringVal();
    DBMS_OUTPUT.put_line('The Requestor for Reference '
                         || vReference || ' is '|| vText);
  END LOOP;
  vReference := 'PTUCKER-20021009123335430PDT';
  FOR c IN getPurchaseOrder(vReference) LOOP
    xNode := c.XML.extract('//LineItem[@ItemNumber="1"]/Description');
    vText := xNode.extract('//text()').getStringVal();
    DBMS_OUTPUT.put_line('The Description of LineItem[1] for Reference ' 
                         || vReference || ' is '|| vText);
  END LOOP;
END;/
The Requestor for Reference EABEL-20021009123335791PDT is Ellen S. Abel
The Description of LineItem[1] for Reference PTUCKER-20021009123335430PDT is Picnic at Hanging Rock

PL/SQL procedure successfully completed.

Example 4-11 Extracting XML Data with XMLTable, and Inserting It into a Database Table

This example uses SQL function XMLTable to extract data from an XML purchase-order document, and then inserts that data into a database table.

CREATE TABLE purchaseorder_table (reference           VARCHAR2(28) PRIMARY KEY,
                                  requestor           VARCHAR2(48),
                                  actions             XMLType,
                                  userid              VARCHAR2(32),
                                  costcenter          VARCHAR2(3),
                                  shiptoname          VARCHAR2(48),
                                  address             VARCHAR2(512),
                                  phone               VARCHAR2(32),
                                  rejectedby          VARCHAR2(32),
                                  daterejected        DATE,
                                  comments            VARCHAR2(2048),
                                  specialinstructions VARCHAR2(2048));
 
CREATE TABLE purchaseorder_lineitem (reference,
                                     FOREIGN KEY ("REFERENCE")
                                       REFERENCES "PURCHASEORDER_TABLE" ("REFERENCE") ON DELETE CASCADE,
                                     lineno      NUMBER(10), PRIMARY KEY ("REFERENCE", "LINENO"),
                                     upc         VARCHAR2(14),
                                     description VARCHAR2(128),
                                     quantity    NUMBER(10),
                                     unitprice   NUMBER(12,2));
 
INSERT INTO purchaseorder_table (reference, requestor, actions, userid, costcenter, shiptoname, address,
                                 phone, rejectedby, daterejected, comments, specialinstructions)
  SELECT t.reference, t.requestor, t.actions, t.userid, t.costcenter, t.shiptoname, t.address, 
          t.phone, t.rejectedby, t.daterejected, t.comments, t.specialinstructions
    FROM purchaseorder p,
         XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
                  COLUMNS reference           VARCHAR2(28)   PATH 'Reference',
                          requestor           VARCHAR2(48)   PATH 'Requestor',
                          actions             XMLType        PATH 'Actions',
                          userid              VARCHAR2(32)   PATH 'User',
                          costcenter          VARCHAR2(3)    PATH 'CostCenter',
                          shiptoname          VARCHAR2(48)   PATH 'ShippingInstructions/name',
                          address             VARCHAR2(512)  PATH 'ShippingInstructions/address',
                          phone               VARCHAR2(32)   PATH 'ShippingInstructions/telephone',
                          rejectedby          VARCHAR2(32)   PATH 'Rejection/User',
                          daterejected        DATE           PATH 'Rejection/Date',
                          comments            VARCHAR2(2048) PATH 'Rejection/Comments',
                          specialinstructions VARCHAR2(2048) PATH 'SpecialInstructions') t
    WHERE t.reference = 'EABEL-20021009123336251PDT';
 
1 row created.
 
INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice)
  SELECT t.reference, li.lineno, li.upc, li.description, li.quantity, li.unitprice
    FROM purchaseorder p,
         XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
                  COLUMNS reference VARCHAR2(28) PATH 'Reference',
                          lineitem XMLType PATH 'LineItems/LineItem') t,
         XMLTable('LineItem' PASSING t.lineitem
                  COLUMNS lineno      NUMBER(10)    PATH '@ItemNumber',
                          upc         VARCHAR2(14)  PATH 'Part/@Id',
                          description VARCHAR2(128) PATH 'Description',
                          quantity    NUMBER(10)    PATH 'Part/@Quantity',
                          unitprice   NUMBER(12,2)  PATH 'Part/@UnitPrice') li
    WHERE t.reference = 'EABEL-20021009123336251PDT';
 
3 rows created.
 
SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table;
 
REFERENCE                        USERID   SHIPTONAME                                       SPECIALINSTRUCTIONS
-------------------------------- -------- ------------------------------------------------ -------------------
EABEL-20021009123336251PDT       EABEL    Ellen S. Abel                                    Counter to Counter
 
1 row selected.
 
SELECT reference, lineno, upc, description, quantity FROM purchaseorder_lineitem;
 
REFERENCE                            LINENO UPC            DESCRIPTION                          QUANTITY
-------------------------------- ---------- -------------- ---------------------------------- ----------
EABEL-20021009123336251PDT                1 37429125526    Samurai 2: Duel at Ichijoji Temple          3
EABEL-20021009123336251PDT                2 37429128220    The Red Shoes                               4
EABEL-20021009123336251PDT                3 715515009058   A Night to Remember                         1
 
3 rows selected.

Example 4-12 Extracting XML Data with EXTRACTVALUE, and Inserting It into a Table

This example extracts data from an XML purchase-order document, and inserts it into a relational table using SQL function extractValue.

CREATE OR REPLACE PROCEDURE insertPurchaseOrder(purchaseorder XMLType) AS reference VARCHAR2(28);
BEGIN
  INSERT INTO purchaseorder_table (reference, requestor, actions, userid, costcenter, shiptoname, address,
                                   phone, rejectedby, daterejected, comments, specialinstructions)
    VALUES (extractValue(purchaseorder, '/PurchaseOrder/Reference'),
            extractValue(purchaseorder, '/PurchaseOrder/Requestor'),
            extract(purchaseorder,      '/PurchaseOrder/Actions'),
            extractValue(purchaseorder, '/PurchaseOrder/User'),
            extractValue(purchaseorder, '/PurchaseOrder/CostCenter'),
            extractValue(purchaseorder, '/PurchaseOrder/ShippingInstructions/name'),
            extractValue(purchaseorder, '/PurchaseOrder/ShippingInstructions/address'),
            extractValue(purchaseorder, '/PurchaseOrder/ShippingInstructions/telephone'),
            extractValue(purchaseorder, '/PurchaseOrder/Rejection/User'),
            extractValue(purchaseorder, '/PurchaseOrder/Rejection/Date'),
            extractValue(purchaseorder, '/PurchaseOrder/Rejection/Comments'),
            extractValue(purchaseorder, '/PurchaseOrder/SpecialInstructions'))
    RETURNING reference INTO reference;

  INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice)
    SELECT reference, li.lineno, li.upc, li.description, li.quantity, li.unitprice
      FROM XMLTable('/PurchaseOrder/LineItems/LineItem' PASSING purchaseorder
                    COLUMNS lineno      NUMBER(10)    PATH '@ItemNumber',
                            upc         VARCHAR2(14)  PATH 'Part/@Id',
                            description VARCHAR2(128) PATH 'Description',
                            quantity    NUMBER(10)    PATH 'Part/@Quantity',
                            unitprice   NUMBER(12,2)  PATH 'Part/@UnitPrice') li;
END;/
Procedure created.
CALL insertPurchaseOrder(XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'), nls_charset_id('AL32UTF8')));
 
Call completed.
SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table;
 
REFERENCE                        USERID   SHIPTONAME                                       SPECIALINSTRUCTIONS
-------------------------------- -------- ------------------------------------------------ -------------------
SBELL-2002100912333601PDT        SBELL    Sarah J. Bell                                    Air Mail
 
1 row selected.
 
SELECT reference, lineno, upc, description, quantity FROM purchaseorder_lineitem;
 
REFERENCE                 LINENO UPC          DESCRIPTION                        QUANTITY
------------------------- ------ ------------ ---------------------------------- --------
SBELL-2002100912333601PDT      1 715515009058 A Night to Remember                       2
SBELL-2002100912333601PDT      2 37429140222  The Unbearable Lightness Of Being         2
SBELL-2002100912333601PDT      3 715515011020 Sisters                                   4
 
3 rows selected.

Example 4-13 Searching XML Data with XMLType Methods extract() and existsNode()

This example extracts the purchase-order name from the PurchaseOrder element, for customers with "ll" (double L) in their names and the word "Shores" in the shipping instructions. It uses XMLType methods extract() and existsNode() instead of SQL functions extract and existsNode.

SELECT p.OBJECT_VALUE.extract('/PurchaseOrder/Requestor/text()').getStringVal() NAME,    
       count(*)
  FROM purchaseorder p
  WHERE p.OBJECT_VALUE.existsNode
          ('/PurchaseOrder/ShippingInstructions[ora:contains(address/text(),"Shores")>0]',
           'xmlns:ora="http://xmlns.oracle.com/xdb"') = 1
    AND p.OBJECT_VALUE.extract('/PurchaseOrder/Requestor/text()').getStringVal() LIKE '%ll%'
  GROUP BY p.OBJECT_VALUE.extract('/PurchaseOrder/Requestor/text()').getStringVal();
 
NAME                   COUNT(*)
-------------------- ----------
Allan D. McEwen               9
Ellen S. Abel                 4
Sarah J. Bell                13
William M. Smith              7
 
4 rows selected.

Example 4-14 Searching XML Data with EXTRACTVALUE

This example shows the query of Example 4-13 rewritten to use SQL function extractValue.

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME, count(*)
  FROM purchaseorder
  WHERE existsNode
          (OBJECT_VALUE,
           '/PurchaseOrder/ShippingInstructions[ora:contains(address/text(), "Shores")>0]',
           'xmlns:ora="http://xmlns.oracle.com/xdb') = 1
    AND extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor/text()') LIKE '%ll%'
  GROUP BY extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor');
 
NAME                   COUNT(*)
-------------------- ----------
Allan D. McEwen               9
Ellen S. Abel                 4
Sarah J. Bell                13
William M. Smith              7
 
4 rows selected.

Example 4-15 uses SQL function extract to extract nodes identified by an XPath expression. An XMLType instance containing the XML fragment is returned by extract. The result may be a set of nodes, a singleton node, or a text value. You can determine whether the result is a fragment using the isFragment() method on the XMLType instance.

Note:

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

Example 4-15 Extracting Fragments From an XMLType Instance Using EXTRACT

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') REFERENCE, count(*)
  FROM purchaseorder, XMLTable('//LineItem[Part/@Id="37429148327"]' PASSING OBJECT_VALUE)
  WHERE extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[Part/@Id="37429148327"]').isFragment() = 1
  GROUP BY extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference')
  ORDER BY extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference');
 
REFERENCE                          COUNT(*)
-------------------------------- ----------
AWALSH-20021009123337303PDT               1
AWALSH-20021009123337954PDT               1
DAUSTIN-20021009123337553PDT              1
DAUSTIN-20021009123337613PDT              1
LSMITH-2002100912333722PDT                1
LSMITH-20021009123337323PDT               1
PTUCKER-20021009123336291PDT              1
SBELL-20021009123335771PDT                1
SKING-20021009123335560PDT                1
SMCCAIN-20021009123336151PDT              1
SMCCAIN-20021009123336842PDT              1
SMCCAIN-2002100912333894PDT               1
TFOX-2002100912333681PDT                  1
TFOX-20021009123337784PDT                 3
WSMITH-20021009123335650PDT               1
WSMITH-20021009123336412PDT               1
 
16 rows selected.

Updating XML Instances and XML Data in Tables

This section covers updating transient XML instances and XML data stored in tables. It details the use of the following SQL functions:

Updating an Entire XML Document

For unstructured storage (CLOB), an update effectively replaces the entire document. To update an entire XML document, use a SQL UPDATE statement. The right side of the UPDATE statement SET clause must be an XMLType instance. This can be created in any of the following ways:

  • Use SQL functions or XML constructors that return an XML instance.

  • Use the PL/SQL DOM APIs for XMLType that change and bind an existing XML instance.

  • Use the Java DOM API that changes and binds an existing XML instance.

Updates for non-schema-based XML documents stored as CLOB values (unstructured storage) always update the entire XML document. Updates for non-schema-based documents stored as binary XML can be made in a piecewise manner. See "Updating XML Schema-Based and Non-Schema-Based XML Documents".

Example 4-16 Updating XMLType Using SQL UPDATE Statement

This example updates an XMLType instance using a SQL UPDATE statement.

SELECT t.reference, li.lineno, li.description
  FROM purchaseorder p,
       XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
                COLUMNS reference VARCHAR2(28) PATH 'Reference',
                        lineitem  XMLType      PATH 'LineItems/LineItem') t
       XMLTable('/LineItem' PASSING t.lineitem
                COLUMNS lineno      NUMBER(10)    PATH '@ItemNumber',
                        description VARCHAR2(128) PATH 'Description') li
  WHERE t.reference = 'DAUSTIN-20021009123335811PDT' AND ROWNUM < 6;
 
REFERENCE                         LINENO DESCRIPTION
-------------------------------- ------- -----------------
DAUSTIN-20021009123335811PDT           1 Nights of Cabiria
DAUSTIN-20021009123335811PDT           2 For All Mankind
DAUSTIN-20021009123335811PDT           3 Dead Ringers
DAUSTIN-20021009123335811PDT           4 Hearts and Minds
DAUSTIN-20021009123335811PDT           5 Rushmore
 
5 rows selected.
 
UPDATE purchaseorder
  SET OBJECT_VALUE = XMLType(bfilename('XMLDIR', 'NEW-DAUSTIN-20021009123335811PDT.xml'),
                             nls_charset_id('AL32UTF8'))
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]') = 1;
 
1 row updated.
 
SELECT t.reference, li.lineno, li.description
  FROM purchaseorder p,
       XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
                COLUMNS reference VARCHAR2(28) PATH 'Reference',
                        lineitem  XMLType      PATH 'LineItems/LineItem') t
       XMLTable('/LineItem' PASSING t.lineitem
                COLUMNS lineno      NUMBER(10)    PATH '@ItemNumber',
                        description VARCHAR2(128) PATH 'Description') li
  WHERE t.reference = 'DAUSTIN-20021009123335811PDT';
 
REFERENCE                         LINENO DESCRIPTION
-------------------------------- ------- --------------------------------
DAUSTIN-20021009123335811PDT           1 Dead Ringers
DAUSTIN-20021009123335811PDT           2 Getrud
DAUSTIN-20021009123335811PDT           3 Branded to Kill
 
3 rows selected.

SQL Functions to Update XML Data

There are several SQL functions that you can use to update XML data incrementally — that is, to replace, insert, or delete XML data without replacing the entire surrounding XML document. This is also called partial updating. These SQL functions are described in the following sections:

Use functions insertChildXML, insertChildXMLbefore, insertChildXMLafter, insertXMLbefore, insertXMLafter, and appendChildXML to insert XML data; use deleteXML to delete XML data; use updateXML to replace XML data.

In particular, do not use function updateXML to insert or delete XML data by replacing a parent node in its entirety. That will work, but it is less efficient than using one of the other functions, which perform more localized updates.

These functions do not, by themselves, change database data – they are all pure functions, without side effect. Each applies an XPath-expression argument to input XML data and returns a modified copy of the input XML data. You can then use that result with SQL DML operator UPDATE to modify database data. This is no different from the way you use SQL function upper to convert database data to uppercase: you must use a SQL DML operator such as UPDATE to change the stored data.

Each of these functions can be used on XML documents that are either schema-based or non-schema-based. For XML schema-based data, these SQL functions perform partial validation on the result, and, where appropriate, argument values are also checked for compatibility with the XML schema.

Note:

Oracle SQL functions and XMLType methods respect the W3C XPath recommendation, which states that if an XPath expression targets no nodes when applied to XML data, then an empty sequence must be returned; an error must not be raised.

The specific semantics of an Oracle SQL function or XMLType method that applies an XPath-expression to XML data determines what is returned. For example, SQL function extract returns NULL if its XPath-expression argument targets no nodes, and the updating SQL functions, such as deleteXML, return the input XML data unchanged. An error is never raised if no nodes are targeted, but updating SQL functions may raise an error if an XPath-expression argument targets inappropriate nodes, such as attribute nodes or text nodes.

See Also:

"Partial Validation" for more information about partial validation against an XML schema

Inserting XML Elements using SQL Functions

There are several SQL functions for inserting XML nodes into (a copy of) existing XML data. Each can insert nodes at multiple locations that are referenced by an XPath expression. They differ in the placement of the new nodes and how the target XML data is referenced.

  • Function appendChildXML appends nodes to the target elements. That is, for each target element, it inserts one or more nodes of any kind as the element's last children.

  • Function insertChildXML inserts new children (one or more elements of the same type or a single attribute) under target elements. The position of a new child element under its parent is not specified. If the target data is XML schema-based, then the schema can sometimes be used to determine the position; otherwise, it is arbitrary.

  • Function insertXMLbefore inserts one or more nodes of any kind immediately before a target node (which is not an attribute node).

    Function insertXMLafter inserts a node similarly, but after the target, not before.

  • Function insertChildXMLbefore is similar to insertChildXML, except that the inserted node must be an element (not an attribute), and you specify the position of the new element among its siblings. It is similar to insertXMLbefore, except that it inserts only collection elements, not arbitrary elements. The insertion position specifies a successor collection member. The actual element to be inserted must correspond to the element type for the collection.

    Function insertChildXMLafter inserts a node similarly, but after the target, not before.

Though the effect of insertChildXMLbefore (-after) is similar to that of insertXMLbefore (-after), the target location is expressed differently. For the former, the target is the parent of the new child. For the latter, the target is the succeeding (or preceding) sibling. This difference is reflected in the function names (Child).

For example, to insert a new LineItem element before the third LineItem element under element /PurchaseOrder/LineItems, you can use insertChildXMLbefore, specifying the target parent as /PurchaseOrder/LineItems and the succeeding sibling as LineItem[3]. Or you can use insertXMLbefore, specifying the target succeeding sibling as /PurchaseOrder/LineItems/LineItem[3]. If you use insertChildXML for the insertion, then you cannot specify the position of the new element in the collection — the resulting position is indeterminate.

Another difference among these functions is that all of them except insertXMLbefore, insertXMLafter, and appendChildXML —are optimized for SQL UPDATE operations on XMLType tables and columns that are stored object-relationally or as binary XML.

UPDATEXML SQL Function

SQL function updateXML replaces XML nodes of any kind. The XML document that is the target of the update can be schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned; the original data is unaffected. You can use that returned data with SQL operation UPDATE to modify database data.

Function updateXML has the following parameters (in order):

  • target-data (XMLType) – The XML data containing the target node to replace.

  • One or more pairs of xpath and replacement parameters:

    • xpath (VARCHAR2) – An XPath 1.0 expression that locates the nodes within target-data to replace; each targeted node is replaced by replacement. These can be nodes of any kind. If xpath matches an empty sequence of nodes, then no replacement is done; target-data is returned unchanged (and no error is raised).

    • replacement (XMLType or VARCHAR2) – The XML data that replaces the data targeted by xpath. The data type of replacement must correspond to the data to be replaced. If xpath targets an element node for replacement, then the data type must be XMLType; if xpath targets an attribute node or a text node, then it must be VARCHAR2. For an attribute node, replacement is only the replacement value of the attribute (for example, 23), not the complete attribute node including the name (for example, my_attribute="23").

  • namespace (VARCHAR2, optional) – The XML namespace for parameter xpath.

SQL function updateXML can be used to replace existing elements, attributes, and other nodes with new values. It is not an efficient way to insert new nodes or delete existing ones; you can only perform insertions and deletions with updateXML by using it to replace the entire node that is parent of the node to be inserted or deleted.

Function updateXML updates only the transient XML instance in memory. Use a SQL UPDATE statement to update data stored in tables.

Figure 4-6 illustrates the syntax.

Figure 4-6 UPDATEXML Syntax

Description of Figure 4-6 follows
Description of "Figure 4-6 UPDATEXML Syntax"

Example 4-17 Updating XMLType Using UPDATE and UPDATEXML

This example uses updateXML on the right side of an UPDATE statement to update the XML document in a table instead of creating a new document. The entire document is updated, not just the part that is selected.

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]') ACTION FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
ACTION
--------------------------------
<Action>
  <User>SVOLLMAN</User>
</Action>
 
1 row selected.
 
UPDATE purchaseorder
  SET OBJECT_VALUE = updateXML(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]/User/text()', 'SKING')
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
1 row updated.
 
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]') ACTION
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
ACTION
---------------------------------
<Action>
  <User>SKING</User>
</Action>
 
1 row selected.

Example 4-18 Updating Multiple Text Nodes and Attribute Values Using UPDATEXML

This example updates multiple nodes using SQL function updateXML.

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
       extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
NAME             LINEITEMS
---------------- ------------------------------------------------------------------------
Sarah J. Bell    <LineItems>
                   <LineItem ItemNumber="1">
                     <Description>A Night to Remember</Description>
                     <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="2">
                     <Description>The Unbearable Lightness Of Being</Description>
                     <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="3">
                     <Description>Sisters</Description>
                     <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
                   </LineItem>
                 </LineItems>
 
1 row selected.
 
UPDATE purchaseorder
  SET OBJECT_VALUE = updateXML(OBJECT_VALUE,
                               '/PurchaseOrder/Requestor/text()', 'Stephen G. King',
                               '/PurchaseOrder/LineItems/LineItem[1]/Part/@Id', '786936150421',
                               '/PurchaseOrder/LineItems/LineItem[1]/Description/text()', 'The Rock',
                               '/PurchaseOrder/LineItems/LineItem[3]',
                               XMLType('<LineItem ItemNumber="99">
                                          <Description>Dead Ringers</Description>
                                          <Part Id="715515009249" UnitPrice="39.95" Quantity="2"/>
                                        </LineItem>'))
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
1 row updated.
 
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
       extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
NAME             LINEITEMS
---------------- ------------------------------------------------------------------
Stephen G. King  <LineItems>
                   <LineItem ItemNumber="1">
                     <Description>The Rock</Description>
                     <Part Id="786936150421" UnitPrice="39.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="2">
                     <Description>The Unbearable Lightness Of Being</Description>
                     <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="99">
                     <Description>Dead Ringers</Description>
                     <Part Id="715515009249" UnitPrice="39.95" Quantity="2"/>
                   </LineItem>
                 </LineItems>

1 row selected.

Example 4-19 Updating Selected Nodes Within a Collection Using UPDATEXML

This example uses SQL function updateXML to update selected nodes within a collection.

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
       extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
NAME             LINEITEMS
---------------- ------------------------------------------------------------------------
Sarah J. Bell    <LineItems>
                   <LineItem ItemNumber="1">
                     <Description>A Night to Remember</Description>
                     <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="2">
                     <Description>The Unbearable Lightness Of Being</Description>
                     <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="3">
                     <Description>Sisters</Description>
                     <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
                   </LineItem>
                 </LineItems>
 
1 row selected.
 
UPDATE purchaseorder
  SET OBJECT_VALUE = 
      updateXML(OBJECT_VALUE,
                '/PurchaseOrder/Requestor/text()', 'Stephen G. King',
                '/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity', 25,
                '/PurchaseOrder/LineItems/LineItem[Description/text()="The Unbearable Lightness Of Being"]',
                XMLType('<LineItem ItemNumber="99">
                           <Part Id="786936150421" Quantity="5" UnitPrice="29.95"/>
                           <Description>The Rock</Description>
                         </LineItem>'))
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
1 row updated.
 
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
       extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
NAME             LINEITEMS
---------------- ------------------------------------------------------------------------
Stephen G. King  <LineItems>
                   <LineItem ItemNumber="1">
                     <Description>A Night to Remember</Description>
                     <Part Id="715515009058" UnitPrice="39.95" Quantity="25"/>
                   </LineItem>
                   <LineItem ItemNumber="99">
                     <Part Id="786936150421" Quantity="5" UnitPrice="29.95"/>
                     <Description>The Rock</Description>
                   </LineItem>
                   <LineItem ItemNumber="3">
                     <Description>Sisters</Description>
                     <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
                   </LineItem>
                 </LineItems>
 
1 row selected.

UPDATEXML and NULL Values

If you update an XML element to NULL, the attributes and children of the element are removed, and the element becomes empty. The type and namespace properties of the element are retained. See Example 4-20.

If you update an attribute value to NULL, the value appears as the empty string. See Example 4-20.

If you update the text node of an element to NULL, the content (text) of the element is removed; the element itself remains, but is empty. See Example 4-21.

Example 4-20 NULL Updates With UPDATEXML – Element and Attribute

This example updates all of the following to NULL:

  • The Description element and the Quantity attribute of the LineItem element whose Part element has attribute Id value 715515009058.

  • The LineItem element whose Description element has the content (text) "The Unbearable Lightness Of Being".

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
       extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
NAME             LINEITEMS
---------------- -------------------------------------------------------------------
Sarah J. Bell    <LineItems>
                   <LineItem ItemNumber="1">
                     <Description>A Night to Remember</Description>
                     <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="2">
                     <Description>The Unbearable Lightness Of Being</Description>
                     <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
                   </LineItem>
                   <LineItem ItemNumber="3">
                     <Description>Sisters</Description>
                     <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
                   </LineItem>
                 </LineItems>
 
1 row selected.
 
UPDATE purchaseorder
  SET OBJECT_VALUE = 
      updateXML(
        OBJECT_VALUE,
        '/PurchaseOrder/LineItems/LineItem[Part/@Id="715515009058"]/Description', NULL,
        '/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity', NULL,
        '/PurchaseOrder/LineItems/LineItem[Description/text()="The Unbearable Lightness Of Being"]', NULL)
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
1 row updated.
 
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor') NAME,
       extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') LINEITEMS
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
NAME             LINEITEMS
---------------- ----------------------------------------------------------------
Sarah J. Bell    <LineItems>
                   <LineItem ItemNumber="1">
                     <Description/>
                     <Part Id="715515009058" UnitPrice="39.95" Quantity=""/>
                   </LineItem>
                   <LineItem/>
                   <LineItem ItemNumber="3">
                     <Description>Sisters</Description>
                     <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
                   </LineItem>
                 </LineItems>

1 row selected.

Example 4-21 updates the text node of a Part element whose Description attribute has value "A Night to Remember" to NULL.

Example 4-21 NULL Updates With UPDATEXML – Text Node

The XML data for this example corresponds to a different, revised purchase-order XML schema – see "Scenario for Copy-Based Evolution". In that XML schema, Description is an attribute of the Part element, not a sibling element.

SELECT extractValue(OBJECT_VALUE,
                    '/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]') PART
  FROM purchaseorder
  WHERE existsNode(object_value,'/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]') = 1;

PART
----
<Part Description="A Night to Remember" UnitCost="39.95">715515009058</Part>

UPDATE purchaseorder
  SET OBJECT_VALUE = 
        updateXML(OBJECT_VALUE, 
                  '/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]/text()', NULL)
  WHERE existsNode(object_value,'/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]') = 1;


SELECT extractValue(OBJECT_VALUE,
                    '/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]') PART
  FROM purchaseorder
  WHERE existsNode(object_value,'/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]') = 1;


PART
----
<Part Description="A Night to Remember" UnitCost="39.95"/>

See Also:

Example 7-2, Example 7-3, and Example 3-35 for examples of rewriting updateXML expressions

Updating the Same XML Node More Than Once

You can update the same XML node more than once in an updateXML expression. 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.

Preserving DOM Fidelity When Using UPDATEXML

Here are some guidelines for preserving DOM fidelity when using SQL function updateXML:

When DOM Fidelity is Preserved

When you update an element to NULL, you make that element appear empty in its parent, such as in <myElem/>.When you update a text node inside an element to NULL, you remove that text node from the element.When you update an attribute node to NULL, you make the value of the attribute become the empty string, for example, myAttr="".

When DOM Fidelity is Not Preserved

When you update a complexType element to NULL, you make the element appear empty in its parent, for example, <myElem/>.When you update a SQL-inlined simpleType element to NULL, you make the element disappear from its parent.When you update a text node to NULL, you are doing the same thing as setting the parent simpleType element to NULL. Furthermore, text nodes can appear only inside simpleType elements when DOM fidelity is not preserved, since there is no positional descriptor with which to store mixed content.When you update an attribute node to NULL, you remove the attribute from the element.

Determining Whether DOM Fidelity is Preserved

You can determine whether or not DOM fidelity is preserved for particular parts of a given XMLType in a given XML schema by querying the schema metadata for attribute maintainDOM.

See Also:

Optimization of SQL Functions that Modify XML Data

In most cases, the SQL functions that modify XML data materialize a copy of the entire input XML document in memory, then update the copy. However, functions updateXML, insertChildXML, insertChildXMLbefore, insertChildXMLafter, and deleteXML —that is, all except insertXMLbefore, insertXMLafter, and appendChildXML —are optimized for SQL UPDATE operations on XMLType tables and columns that are stored object-relationally or as binary XML. For structured storage, if particular conditions are met, then the function call can be rewritten to update the object-relational columns directly with the values. For binary XML storage, data preceding the targeted update is not modified, and, if SecureFile LOBs are used, then sliding inserts are used to update only the portions of the data that need changing.

See Also:

As an example with object-relational storage, the XPath argument to updateXML in Example 4-22 is processed by Oracle XML DB and rewritten into the equivalent object relational SQL statement shown in Example 4-23.

Example 4-22 XPath Expressions in UPDATEXML Expression

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
EXTRACTVAL
----------
SBELL
 
1 row selected.
 
UPDATE purchaseorder
  SET OBJECT_VALUE = updateXML(OBJECT_VALUE, '/PurchaseOrder/User/text()', 'SVOLLMAN')
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
1 row updated.
 
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
EXTRACTVAL
----------
SVOLLMAN
 
1 row selected.

Example 4-23 Object Relational Equivalent of UPDATEXML Expression

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
EXTRACTVAL
----------
SBELL
 
1 row selected.
 
UPDATE purchaseorder p
   SET p."XMLDATA"."USERID" = 'SVOLLMAN'
   WHERE p."XMLDATA"."REFERENCE" = 'SBELL-2002100912333601PDT';
 
1 row updated.
 
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
EXTRACTVAL
----------
SVOLLMAN
 
1 row selected.

Creating Views of XML With SQL Functions that Modify XML Data

You can use the SQL functions that modify XML data (updateXML, insertChildXML, insertChildXMLbefore, insertChildXMLafter, insertXMLbefore, insertXMLafter, appendChildXML, and deleteXML) to create new views of XML data.

Example 4-24 Creating Views Using UPDATEXML

This example creates a view of the purchaseorder table using SQL function updateXML.

CREATE OR REPLACE VIEW purchaseorder_summary OF XMLType AS
  SELECT updateXML(OBJECT_VALUE,
                   '/PurchaseOrder/Actions', NULL,
                   '/PurchaseOrder/ShippingInstructions', NULL,
                   '/PurchaseOrder/LineItems', NULL) AS XML
  FROM purchaseorder p;
 
View created.
 
SELECT OBJECT_VALUE FROM purchaseorder_summary
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]') = 1;
 
OBJECT_VALUE
---------------------------------------------------------------------------
<PurchaseOrder 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:noNamespaceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  <Reference>DAUSTIN-20021009123335811PDT</Reference>
  <Actions/>
  <Reject/>
  <Requestor>David L. Austin</Requestor>
  <User>DAUSTIN</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions/>
  <SpecialInstructions>Courier</SpecialInstructions>
  <LineItems/>
</PurchaseOrder>

1 row selected.

INSERTCHILDXML SQL Function

SQL function insertChildXML inserts new children (one or more elements of the same type or a single attribute) under parent XML elements. The XML document that is the target of the insertion can be schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned; the original data is unaffected. You can use that returned data with SQL operation UPDATE to modify database data.

Function insertChildXML has the following parameters (in order):

  • target-data (XMLType) – The XML data containing the target parent element.

  • parent-xpath (VARCHAR2) – An XPath 1.0 expression that locates the parent elements within target-data; child-data is inserted under each parent element.

    If parent-xpath matches an empty sequence of element nodes, then no insertion is done; target-data is returned unchanged (and no error is raised). If parent-xpath does not match a sequence of element nodes (in particular, if parent-xpath matches one or more attribute or text nodes), then an error is raised.

  • child-name (VARCHAR2) – The name of the child elements or attribute to insert. An attribute name is distinguished from an element name by having an at-sign (@) prefix as part of child-name, for example, @my_attribute versus my_element. (The at-sign is not part of the attribute name, but serves in the argument to indicate that child-name refers to an attribute.)

  • child-data (XMLType or VARCHAR2) – The child XML data to insert:

    • If one or more elements are being inserted, then this is of data type XMLType, and it contains element nodes. Each of the top-level element nodes in child-data must have the same name (tag) as child-name (or else an error is raised).

    • If an attribute is being inserted, then this is of data type VARCHAR2, and it represents the (scalar) attribute value. If an attribute of the same name already exists at the insertion location, then an error is raised.

  • namespace (VARCHAR2, optional) – The XML namespace for parameters parent-xpath and child-data.

XML data child-data is inserted as one or more child elements, or a single child attribute, under each of the parent elements located at parent-xpath; the result is returned.

In order of decreasing precedence, function insertChildXML has the following behavior for NULL arguments:

  • If child-name is NULL, then an error is raised.

  • If target-data or parent-xpath is NULL, then NULL is returned.

  • If child-data is NULL, then:

    • If child-name names an element, then no insertion is done; target-data is returned unchanged.

    • If child-name names an attribute, then an empty attribute value is inserted, for example, my_attribute = "".

Figure 4-7 shows the syntax.

Figure 4-7 INSERTCHILDXML Syntax

Description of Figure 4-7 follows
Description of "Figure 4-7 INSERTCHILDXML Syntax"

If target-data is XML schema-based, then the schema is consulted to determine the insertion positions. For example, if the schema constrains child elements named child-name to be the first child elements of a parent-xpath, then the insertion takes this into account. Similarly, if the child-name or child-data argument is inappropriate for an associated schema, then an error is raised.

If the parent element does not yet have a child corresponding in name and kind to child-name (and if such a child is permitted by the associated XML schema, if any), then child-data is inserted as new child elements, or a new attribute value, named child-name.

If the parent element already has a child attribute named child-name (without the at-sign), then an error is raised. If the parent element already has a child element named child-name (and if more than one child element is permitted by the associated XML schema, if any), then child-data is inserted so that its elements become the last child elements named child-name.

Example 4-25 Inserting a LineItem Element into a LineItems Element

SELECT extract(OBJECT_VALUE, 
               '/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER="222"]')
----------------------------------------------------------------------------

1 row selected.

UPDATE purchaseorder
  SET OBJECT_VALUE = 
        insertChildXML(OBJECT_VALUE, 
                       '/PurchaseOrder/LineItems', 
                       'LineItem', 
                       XMLType('<LineItem ItemNumber="222">
                                  <Description>The Harder They Come</Description>
                                  <Part Id="953562951413" 
                                        UnitPrice="22.95" 
                                        Quantity="1"/>
                                </LineItem>'))
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

1 row updated.

SELECT extract(OBJECT_VALUE, 
               '/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER="222"]')
----------------------------------------------------------------------------
<LineItem ItemNumber="222">
  <Description>The Harder They Come</Description>
  <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>

1 row selected.

If XML data to be updated is XML schema-based and it refers to a namespace, then the data to be inserted must also refer to the same namespace; otherwise, an error will be raised because the inserted data does not conform to the XML schema. For example, if the data in Example 4-25 used the namespace films.xsd, then the UPDATE statement would need to be as shown in Example 4-26.

Example 4-26 Inserting an Element that Uses a Namespace

This example is the same as Example 4-25, except that the LineItem element to be inserted refers to a namespace. This assumes that the XML schema requires a namespace for this element.

Note that this use of namespaces is different from the use of a namespace argument to function insertChildXML – namespaces supplied in that optional argument apply only to the XPath argument, not to the content to be inserted.

UPDATE purchaseorder
  SET OBJECT_VALUE = 
        insertChildXML(OBJECT_VALUE, 
                       '/PurchaseOrder/LineItems', 
                       'LineItem', 
                       XMLType('<LineItem xmlns="films.xsd" ItemNumber="222">
                                  <Description>The Harder They Come</Description>
                                  <Part Id="953562951413" 
                                        UnitPrice="22.95" 
                                        Quantity="1"/>
                                </LineItem>'))
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

1 row updated.

INSERTCHILDXMLBEFORE SQL Function

SQL function insertChildXMLbefore inserts one or more collection elements as children of target parent elements. The insertion for each target occurs immediately before a specified existing collection element. The existing XML document that is the target of the insertion can be schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned; the original data is unaffected. You can use that returned data with SQL operation UPDATE to modify database data.

Function insertChildXMLbefore has the following parameters (in order):

  • target-data (XMLType) – The XML data that is the target of the insertion.

  • parent-xpath (VARCHAR2) – An XPath 1.0 expression that locates the parent elements within target-data; child-data is inserted under each parent element.

    If parent-xpath matches an empty sequence of element nodes, then no insertion is done; target-data is returned unchanged (and no error is raised). If parent-xpath does not match a sequence of element nodes (in particular, if parent-xpath matches one or more attribute or text nodes), then an error is raised.

  • child-xpath (VARCHAR2) – A relative XPath 1.0 expression that locates the existing child that will be the successor of the inserted child-data. It must name a child element of the element indicated by parent-xpath, and it can include a predicate.

  • child-data (XMLType) – The child element XML data to insert. This is of data type XMLType, and it contains element nodes. Each of the top-level element nodes in child-data must have the same data type as the element indicated by child-xpath (or else an error is raised).

  • namespace (optional, VARCHAR2) – The namespace for parameters parent-xpath, child-xpath, and child-data.

XML data child-data is inserted as one or more child elements under each of the parent elements located at parent-xpath; the result is returned.

In order of decreasing precedence, function insertChildXMLbefore has the following behavior for NULL arguments:

  • If child-name is NULL, then an error is raised.

  • If target-data or parent-xpath is NULL, then NULL is returned.

  • If child-data is NULL, then no insertion is done; target-data is returned unchanged.

Figure 4-10 shows the syntax.

Figure 4-8 INSERTCHILDXMLBEFORE Syntax

Description of Figure 4-8 follows
Description of "Figure 4-8 INSERTCHILDXMLBEFORE Syntax"

INSERTCHILDXMLAFTER SQL Function

SQL function insertChildXMLafter inserts one or more collection elements as children of target parent elements. The insertion for each target occurs immediately after a specified existing collection element. The existing XML document that is the target of the insertion can be schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned; the original data is unaffected. You can use that returned data with SQL operation UPDATE to modify database data.

Function insertChildXMLafter has the following parameters (in order):

  • target-data (XMLType) – The XML data that is the target of the insertion.

  • parent-xpath (VARCHAR2) – An XPath 1.0 expression that locates the parent elements within target-data; child-data is inserted under each parent element.

    If parent-xpath matches an empty sequence of element nodes, then no insertion is done; target-data is returned unchanged (and no error is raised). If parent-xpath does not match a sequence of element nodes (in particular, if parent-xpath matches one or more attribute or text nodes), then an error is raised.

  • child-xpath (VARCHAR2) – A relative XPath 1.0 expression that locates the existing child that will be the predecessor of the inserted child-data. It must name a child element of the element indicated by parent-xpath, and it can include a predicate.

  • child-data (XMLType) – The child element XML data to insert. This is of data type XMLType, and it contains element nodes. Each of the top-level element nodes in child-data must have the same data type as the element indicated by child-xpath (or else an error is raised).

  • namespace (optional, VARCHAR2) – The namespace for parameters parent-xpath, child-xpath, and child-data.

XML data child-data is inserted as one or more child elements under each of the parent elements located at parent-xpath; the result is returned.

In order of decreasing precedence, function insertChildXMLafter has the following behavior for NULL arguments:

  • If child-name is NULL, then an error is raised.

  • If target-data or parent-xpath is NULL, then NULL is returned.

  • If child-data is NULL, then no insertion is done; target-data is returned unchanged.

Figure 4-10 shows the syntax.

Figure 4-9 INSERTCHILDXMLAFTER Syntax

Description of Figure 4-9 follows
Description of "Figure 4-9 INSERTCHILDXMLAFTER Syntax"

INSERTXMLBEFORE SQL Function

SQL function insertXMLbefore inserts one or more nodes of any kind immediately before a target node that is not an attribute node. The XML document that is the target of the insertion can be schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned; the original data is unaffected. You can use that returned data with SQL operation UPDATE to modify database data.

Function insertXMLbefore has the following parameters (in order):

  • target-data (XMLType) – The XML data that is the target of the insertion.

  • successor-xpath (VARCHAR2) – An XPath 1.0 expression that locates zero or more nodes in target-data of any kind except attribute nodes. XML-data is inserted immediately before each of these nodes; that is, the nodes in XML-data become preceding siblings of each of the successor-xpath nodes.

    If successor-xpath matches an empty sequence of nodes, then no insertion is done; target-data is returned unchanged (and no error is raised). If successor-xpath does not match a sequence of nodes that are not attribute nodes, then an error is raised.

  • XML-data (XMLType) – The XML data to be inserted: one or more nodes of any kind. The order of the nodes is preserved after the insertion.

  • namespace (optional, VARCHAR2) – The namespace for parameter successor-xpath.

The XML-data nodes are inserted immediately before each of the non-attribute nodes located at successor-xpath; the result is returned.

Function insertXMLbefore has the following behavior for NULL arguments:

  • If target-data or parent-xpath is NULL, then NULL is returned.

  • Otherwise, if child-data is NULL, then no insertion is done; target-data is returned unchanged.

Figure 4-10 shows the syntax.

Figure 4-10 INSERTXMLBEFORE Syntax

Description of Figure 4-10 follows
Description of "Figure 4-10 INSERTXMLBEFORE Syntax"

Example 4-27 Inserting a LineItem Element Before the First LineItem ELement

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[1]')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[1]')
------------------------------------------------------------
<LineItem ItemNumber="1">
  <Description>Salesman</Description>
  <Part Id="37429158920" UnitPrice="39.95" Quantity="2"/>
</LineItem>

1 row selected.

UPDATE purchaseorder
  SET OBJECT_VALUE = 
      insertXMLbefore(OBJECT_VALUE, 
                      '/PurchaseOrder/LineItems/LineItem[1]', 
                      XMLType('<LineItem ItemNumber="314">
                                 <Description>Brazil</Description>
                                 <Part Id="314159265359" 
                                       UnitPrice="69.95" 
                                       Quantity="2"/>
                               </LineItem>'))
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[position() <= 2]')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[POSITION()<=2]')
------------------------------------------------------------------------
<LineItem ItemNumber="314">
  <Description>Brazil</Description>
  <Part Id="314159265359" UnitPrice="69.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="1">
  <Description>Salesman</Description>
  <Part Id="37429158920" UnitPrice="39.95" Quantity="2"/>
</LineItem>

1 row selected.

INSERTXMLAFTER SQL Function

SQL function insertXMLafter inserts one or more nodes of any kind immediately after a target node that is not an attribute node. The XML document that is the target of the insertion can be schema-based or non-schema-based. It is thus similar to insertXMLbefore, but it inserts after, not before, the target node.

A copy of the input XMLType instance is modified and returned; the original data is unaffected. You can use that returned data with SQL operation UPDATE to modify database data.

Function insertXMLafter has the following parameters (in order):

  • target-data (XMLType) – The XML data that is the target of the insertion.

  • successor-xpath (VARCHAR2) – An XPath 1.0 expression that locates zero or more nodes in target-data of any kind except attribute nodes. XML-data is inserted immediately after each of these nodes; that is, the nodes in XML-data become succeeding siblings of each of the successor-xpath nodes.

    If successor-xpath matches an empty sequence of nodes, then no insertion is done; target-data is returned unchanged (and no error is raised). If successor-xpath does not match a sequence of nodes that are not attribute nodes, then an error is raised.

  • XML-data (XMLType) – The XML data to be inserted: one or more nodes of any kind. The order of the nodes is preserved after the insertion.

  • namespace (optional, VARCHAR2) – The namespace for parameter successor-xpath.

The XML-data nodes are inserted immediately after each of the non-attribute nodes located at successor-xpath; the result is returned.

Function insertXMLafter has the following behavior for NULL arguments:

  • If target-data or parent-xpath is NULL, then NULL is returned.

  • Otherwise, if child-data is NULL, then no insertion is done; target-data is returned unchanged.

Figure 4-10 shows the syntax.

Figure 4-11 INSERTXMLAFTER Syntax

Description of Figure 4-11 follows
Description of "Figure 4-11 INSERTXMLAFTER Syntax"

APPENDCHILDXML SQL Function

SQL function appendChildXML inserts one or more nodes of any kind as the last children of a given element node. The XML document that is the target of the insertion can be schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned; the original data is unaffected. You can use that returned data with SQL operation UPDATE to modify database data.

Function appendChildXML has the following parameters (in order):

  • target-data (XMLType)– The XML data containing the target parent element.

  • parent-xpath (VARCHAR2) – An XPath 1.0 expression that locates zero or more element nodes in target-data that are the targets of the insertion operation; child-data is inserted as the last child or children of each of these parent elements.

    If parent-xpath matches an empty sequence of element nodes, then no insertion is done; target-data is returned unchanged (and no error is raised). If parent-xpath does not match a sequence of element nodes (in particular, if parent-xpath matches one or more attribute or text nodes), then an error is raised.

  • child-data (XMLType) – Child data to be inserted: one or more nodes of any kind. The order of the nodes is preserved after the insertion.

  • namespace (optional, VARCHAR2) – The namespace for parameter parent-xpath.

XML data child-data is inserted as the last child or children of each of the element nodes indicated by parent-xpath; the result is returned.

Function appendChildXML has the following behavior for NULL arguments:

  • If target-data or parent-xpath is NULL, then NULL is returned.

  • Otherwise, if child-data is NULL, then no insertion is done; target-data is returned unchanged.

Figure 4-10 shows the syntax.

Figure 4-12 APPENDCHILDXML Syntax

Description of Figure 4-12 follows
Description of "Figure 4-12 APPENDCHILDXML Syntax"

Example 4-28 Inserting a Date Element as the Last Child of an Action Element

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/ACTIONS/ACTION[1]')
--------------------------------------------------------
<Action>
  <User>KPARTNER</User>
</Action>
 
1 row selected.

UPDATE purchaseorder
  SET OBJECT_VALUE = 
      appendChildXML(OBJECT_VALUE, 
                     '/PurchaseOrder/Actions/Action[1]', 
                     XMLType('<Date>2002-11-04</Date>'))
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Actions/Action[1]') 
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/ACTIONS/ACTION[1]')
--------------------------------------------------------
<Action>
  <User>KPARTNER</User>
  <Date>2002-11-04</Date>
</Action>

1 row selected.

DELETEXML SQL Function

SQL function deleteXML deletes XML nodes of any kind. The XML document that is the target of the deletion can be schema-based or non-schema-based.

A copy of the input XMLType instance is modified and returned; the original data is unaffected. You can use that returned data with SQL operation UPDATE to modify database data.

Function deleteXML has the following parameters (in order):

  • target-data (XMLType) – The XML data containing the target nodes (to be deleted).

  • xpath (VARCHAR2) – An XPath 1.0 expression that locates zero or more nodes in target-data that are the targets of the deletion operation; each of these nodes is deleted.

    If xpath matches an empty sequence of nodes, then no deletion is done; target-data is returned unchanged (and no error is raised). If xpath matches the top-level element node, then an error is raised.

  • namespace (optional, VARCHAR2) – The namespace for parameter xpath.

The XML nodes located at xpath are deleted from target-data; the result is returned. Function deleteXML returns NULL if target-data or xpath is NULL.

Figure 4-10 shows the syntax.

Figure 4-13 DELETEXML Syntax

Description of Figure 4-13 follows
Description of "Figure 4-13 DELETEXML Syntax"

Example 4-29 Deleting LineItem Element Number 222

SELECT extract(OBJECT_VALUE, 
               '/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;
EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER="222"]')
----------------------------------------------------------------------------
<LineItem ItemNumber="222">
  <Description>The Harder They Come</Description>
  <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>

1 row selected.

UPDATE purchaseorder
  SET OBJECT_VALUE = 
      deleteXML(OBJECT_VALUE, 
                '/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;

SELECT extract(OBJECT_VALUE, 
               '/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]')
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE,
                   '/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]')
        = 1;
EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER="222"]')
----------------------------------------------------------------------------
 
1 row selected.