Purpose
This function is used to update the content of a node at a specified path in an XML string.
Syntax
UPDATEXML(xml_target, xpath_expr, new_xml)
Parameters
Note
Replaces the part specified by in xml_target with new_xml. If does not match any result or matches multiple results, the original xml_target result is returned.
Field
Note
xml_target
The specified XML string, which can be an entire XML document or a fragment.
xpath_expr
Specify the path (XPath) expression of the node to be updated.
new_xml
Specify the new XML snippet for the node to be replaced.
Return type
The return type is LONGTEXT.
Examples
Use the UPDATEXML function to replace the </a> node with a <x>carrot</x> node in an XML string and return the updated XML string. <a/><b/> represents a root node that contains two child nodes, <a> and <b>.
obclient [test_db]> SELECT UPDATEXML('<a/><b/>', '/a', '<x>carrot</x>');
The return result is as follows:
+----------------------------------------------+
| UPDATEXML('<a/><b/>', '/a', '<x>carrot</x>') |
+----------------------------------------------+
| <x>carrot</x><b></b> |
+----------------------------------------------+
1 row in set
Use the UPDATEXML function to replace the a node under the root node in an XML string with the <b>bbb</b> node, and return the updated XML string.
obclient [test_db]> SELECT UPDATEXML('<root><a>aaa</a></root>', '/root/a', '<b>bbb</b>');
The return result is as follows:
+---------------------------------------------------------------+
| UPDATEXML('<root><a>aaa</a></root>', '/root/a', '<b>bbb</b>') |
+---------------------------------------------------------------+
| <root><b>bbb</b></root> |
+---------------------------------------------------------------+
1 row in set