XMLUpdate() function

The XMLUpdate() function updates the portion of an XML document (elements, attributes, or nodes) that is identified by XPath with a new value. The data types of the XPath target and the new value must match.

You cannot directly use the XMLUpdate() function to insert a new node or delete a node, element, or attribute. Instead, you must update the containing parent element with the new value.

Description

The XMLUpdate() function has two forms. One updates an XML document with a scalar (varchar) value, and the other updates an XML document with an XML document:

XML = XMLUpdate(XML input, varchar XPath, varchar value);
XML = XMLUpdate(XML input, varchar XPath, XML value);

The input value specifies an XML document that contains a fragment to be updated.

The Xpath value specifies an XPath expression that is used to locate the XML fragment to update. If Xpath is an XML element, the corresponding value must be type XML. If Xpath is an attribute or text node, the value can be any scalar data type.

The value value specifies the new value to assign to the XML fragment.

Returns

The function returns an XML document that contains an updated fragment.

Example

This example updates the company name in order XML documents to Netezza®, where the salesperson is John Smith:

update sales_tab
set order = XMLUpdate(order,
'/order/company/name',
XMLParse('<Name>Netezza</Name>'))
where sales_person = “John Smith“