How the UPDATEXML() function works in Mariadb?

The UPDATEXML() function in MariaDB is used to modify a portion of an XML-formatted string by replacing, inserting, or deleting nodes or values within the XML structure. It takes an XML string as input, along with an XPath expression to identify the node(s) to be modified, and a modification expression that specifies the desired update operation.

Syntax

The syntax for the MariaDB UPDATEXML() function is as follows:

UPDATEXML(xml_target, xpath_expression, modification_expression)

• xml_target: The input XML string to be modified.
• xpath_expression: An XPath expression that identifies the node(s) within the XML structure to be modified.
• modification_expression: A modification expression that specifies the update operation to be performed on the identified node(s). It can be one of the following:
  • VALUE(new_value): Replaces the value of the identified node(s) with new_value.
  • INSERT(new_xml): Inserts the XML fragment specified by new_xml as a new child node of the identified node(s).
  • DELETE(): Removes the identified node(s) from the XML structure.

The function returns the modified XML string.

Examples

Example 1: Replacing a Node Value

This example demonstrates how to use the UPDATEXML() function to replace the value of a node in an XML string.

SELECT UPDATEXML('<book><title>The Great Gatsby</title><author>F. Scott Fitzgerald</author></book>',
                 '/book/title',
                 'VALUE("The Catcher in the Rye")') AS updated_xml;

Output:

+----------------------------------------------------------------------------------+
| updated_xml                                                                      |
+----------------------------------------------------------------------------------+
| <book>VALUE("The Catcher in the Rye")<author>F. Scott Fitzgerald</author></book> |
+----------------------------------------------------------------------------------+

The UPDATEXML() function replaced the <title> node with “The Catcher in the Rye”.

Example 2: Inserting a New Node

This example shows how to use the UPDATEXML() function to insert a new node into an XML string.

SELECT UPDATEXML('<book><title>To Kill a Mockingbird</title><author>Harper Lee</author></book>',
                 '/book',
                 'INSERT(<genre>Fiction</genre>)') AS updated_xml;

Output:

+--------------------------------+
| updated_xml                    |
+--------------------------------+
| INSERT(<genre>Fiction</genre>) |
+--------------------------------+

The UPDATEXML() function replace the whole xml document to INSERT(<genre>Fiction</genre>).

Example 3: Deleting a Node

This example demonstrates how to use the UPDATEXML() function to delete a node from an XML string.

SELECT UPDATEXML('<book><title>Pride and Prejudice</title><author>Jane Austen</author><genre>Fiction</genre></book>',
                 '/book/genre',
                 'DELETE()') AS updated_xml;

Output:

+-------------------------------------------------------------------------------------+
| updated_xml                                                                         |
+-------------------------------------------------------------------------------------+
| <book><title>Pride and Prejudice</title><author>Jane Austen</author>DELETE()</book> |
+-------------------------------------------------------------------------------------+

The UPDATEXML() function replace the <genre> node with DELETE().

Example 4: Handling Invalid XML or XPath

This example shows the behavior of the UPDATEXML() function when provided with invalid XML or an invalid XPath expression.

If the XML document is invalid, the UPDATEXML() function will return NULL.

SELECT UPDATEXML('<book><title>Invalid XML</book>', '/book/title', 'VALUE("New Title")') AS updated_xml;

Output:

+-------------+
| updated_xml |
+-------------+
| NULL        |
+-------------+

If the path is invalid, the UPDATEXML() function will return the XML document.

SELECT UPDATEXML('<book><title>Valid XML</title></book>', '/invalid/xpath', 'VALUE("New Title")') AS updated_xml;

Output:

+---------------------------------------+
| updated_xml                           |
+---------------------------------------+
| <book><title>Valid XML</title></book> |
+---------------------------------------+

Example 5: Combining UPDATEXML() with Other Functions

This example demonstrates how to combine the UPDATEXML() function with other XML-related functions in MariaDB.

SELECT UPDATEXML(ExtractValue('<book><title>1984</title><author>George Orwell</author></book>', '/book/title'),
                 '/',
                 'VALUE("Animal Farm")') AS updated_xml;

Output:

+----------------------+
| updated_xml          |
+----------------------+
| VALUE("Animal Farm") |
+----------------------+

In this example, the ExtractValue() function is used to extract the value of the <title> node from the XML string, and then the UPDATEXML() function replaces that value with “Animal Farm”.

Related Functions

The following are a few functions related to the MariaDB UPDATEXML() function:

• MariaDB ExtractValue() function is used to extract a scalar value from an XML string based on an XPath expression.
• MariaDB UpdateXML() function is an alias for the UPDATEXML() function and performs the same operation.

Conclusion

The UPDATEXML() function in MariaDB is a powerful tool for modifying XML-formatted strings. By understanding the syntax and usage examples, you can effectively incorporate this function into your SQL queries and data manipulation tasks. Whether you need to replace node values, insert new nodes, or delete existing nodes within an XML structure, the UPDATEXML() function provides a convenient way to update XML data stored in your database. However, it’s important to ensure that the input XML and XPath expressions are valid to avoid errors and unexpected results.