| Oracle® XML DB Developer's Guide 11g Release 1 (11.1) Part Number B28369-01 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 11g Release 1 (11.1) Part Number B28369-01 |
|
|
View PDF |
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:
Chapter 3, "Using Oracle XML DB" for XMLType storage recommendations
Chapter 6, "XML Schema Storage and Query: Basic" for how to work with XML schema-based XMLType tables and columns
You can query XML data from XMLType columns in the following ways:
Select XMLType columns in SQL, PL/SQL, or Java.
Query XMLType columns directly or using XMLType methods extract() and existsNode().
Use Oracle Text operators to query the XML content. See Chapter 5, "Indexing XMLType Data" and Chapter 11, "Full-Text Search Over XML Data".
Use the XQuery language. See "Using XQuery with XMLType Data"
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, |
|
|
Also used as a path separator to identify the children node of any given node. For example, |
|
|
Used to identify all descendants of the current node. For example, |
|
|
Used as a wildcard to match any child node. For example, |
|
|
Used to denote predicate expressions. XPath supports a rich list of binary operators such as Brackets are also used to denote a position (index). For example, |
|
Functions |
XPath supports a set of built-in functions such as |
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.
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.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.
Figure 4-2 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.
XML_passing_clause::=
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.
Figure 4-2 describes the syntax for SQL function existsNode.
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.
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;
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 ofXMLType. 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 functionextractValue 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.Figure 4-2 describes the syntax for SQL/XML standard function XMLCast.
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.
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.
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.
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.
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:
FunctionextractValue 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.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
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 intoXMLType 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.
This section covers updating transient XML instances and XML data stored in tables. It details the use of SQL functions updateXML, insertChildXML, insertXMLbefore, appendChildXML, and deleteXML.
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.
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:
updateXML – Replace XML nodes of any kind. See "UPDATEXML SQL Function".
insertChildXML – Insert XML element or attribute nodes as children of a given element node. See "INSERTCHILDXML SQL Function".
insertXMLbefore – Insert XML nodes of any kind immediately before a given node (other than an attribute node). See "INSERTXMLBEFORE SQL Function".
appendChildXML – Insert XML nodes of any kind as the last child nodes of a given element node. See "APPENDCHILDXML SQL Function".
deleteXML – Delete XML nodes of any kind. See "DELETEXML SQL Function".
Use functions insertChildXML, insertXMLbefore, 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 andXMLType 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 schemaSQL 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.
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.
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, Example 3-35, and Example 3-35 for examples of rewritingupdateXML expressionsYou 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.
Here are some guidelines for preserving DOM fidelity when using SQL function updateXML:
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 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.
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:
"Querying a Registered XML Schema to Obtain Annotations" for an example of querying a schema to retrieve DOM fidelity values
In most cases, the SQL functions that modify XML data (updateXML, insertChildXML, insertXMLbefore, appendChildXML, and deleteXML) materialize a copy of the entire input XML document in memory, then update the copy. However, functions updateXML, insertChildXML, and deleteXML 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:
"Updating XML Schema-Based and Non-Schema-Based XML Documents" for more about piecewise updating
Chapter 3, "Using Oracle XML DB" and Chapter 7, "XPath Rewrite" for information about the conditions for XPath rewrite
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.
You can use the SQL functions that modify XML data (updateXML, insertChildXML, insertXMLbefore, 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.
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.
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.
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-8 shows the 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.
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-8 shows the 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.
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-8 shows the 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.
