Purpose
UPDATEXML() updates an expression of XMLType data. You can specify one or more XPath-value pairs in the function. If any nodes in the expression match the XPaths, the function updates the nodes in sequence to the paired values specified by the value_expr expression.
Syntax
UPDATEXML
(XMLType_instance,
XPath_string, value_expr
[, XPath_string, value_expr ]...
[, namespace_string ]
)
Parameters
| Parameter | Description |
|---|---|
| XMLType_instance | The XMLType data, which can be an XMLType column or an expression that returns XMLType data. |
| XPath_string | The XPath used to identify the XML content to be updated. |
| value_expr | The update value, which can be XMLType, scalar (such as CHAR and VARCHAR2), CLOB, or NULL data. |
| namespace_string | The XML namespace. |
Return type
The return type is XMLType.
Considerations
- The input
value_exprcan be aNULL, scalar, orXMLTypevalue. namespace_stringis optional. You can specify a namespace for XPath-based node search.
Examples
obclient> SELECT UPDATEXML(XMLPARSE(CONTENT '<root><a>aaa</a></root>'), '/root/a/text()', 'bbb') RES FROM DUAL;
+------------------------------+
| RES |
+------------------------------+
| <root>
<a>bbb</a>
</root>
|
+------------------------------+
1 row in set
obclient> SELECT UPDATEXML(XMLPARSE(CONTENT '<root><a>aaa</a></root>'), '/root/a/text()', NULL) RES FROM DUAL;
+------------------------+
| RES |
+------------------------+
| <root>
<a/>
</root>
|
+------------------------+
1 row in set
obclient> SELECT UPDATEXML(XMLPARSE(CONTENT '<root><a>aaa</a></root>'), '/root/a/text()', XMLPARSE(content '<a>bbb</a>')) RES FROM DUAL;
+---------------------------------------------+
| RES |
+---------------------------------------------+
| <root>
<a>
<a>bbb</a>
</a>
</root>
|
+---------------------------------------------+
1 row in set
obclient> SELECT UPDATEXML(XMLPARSE(CONTENT '<root a1="aaa"><a>aaa</a></root>'), '/root/@a1', 'bbb') RES FROM DUAL;
+---------------------------------------+
| RES |
+---------------------------------------+
| <root a1="bbb">
<a>aaa</a>
</root>
|
+---------------------------------------+
1 row in set
obclient> SELECT UPDATEXML(XMLPARSE(CONTENT '<root a1="aaa"><a>aaa</a></root>'), '/root/@a1', NULL) RES FROM DUAL;
+------------------------------------+
| RES |
+------------------------------------+
| <root a1="">
<a>aaa</a>
</root>
|
+------------------------------------+
1 row in set
obclient> SELECT UPDATEXML(XMLPARSE(CONTENT '<root a1="aaa"><a>aaa</a></root>'), '/root/@a1', XMLPARSE(CONTENT '<a>a1</a>')) RES FROM DUAL;
+------------------------------------------+
| RES |
+------------------------------------------+
| <root>
<a>a1</a>
<a>aaa</a>
</root>
|
+------------------------------------------+
1 row in set
obclient> SELECT UPDATEXML(XMLPARSE(CONTENT '<root><a>aaa</a></root>'), '/root/a', 'bbb') RES FROM DUAL;
+-------------------+
| RES |
+-------------------+
| <root>bbb</root>
|
+-------------------+
1 row in set
obclient> SELECT UPDATEXML(XMLPARSE(CONTENT '<a a1="a1" xmlns:f="ns1" xmlns="ns2"><c/>cccc</a>'), '/a', NULL, 'xmlns="ns2"') RES FROM DUAL;
+-------------------+
| RES |
+-------------------+
| <a xmlns="ns2"/>
|
+-------------------+
1 row in set (0.01 sec)
obclient> SELECT UPDATEXML(XMLPARSE(CONTENT '<root><a>aaa</a></root>'), '/root/a', xmltype('<b>bbb</b>')) RES FROM DUAL;
+------------------------------+
| RES |
+------------------------------+
| <root>
<b>bbb</b>
</root>
|
+------------------------------+
1 row in set