PL/SQL UPDATEXML

One of the features of PL/SQL is the UPDATEXML function, which allows you to update XML data stored in an Oracle database. The UPDATEXML function allows you to modify the value of a specific node in an XML document. Here is the basic syntax of the UPDATEXML function:

Syntax

UPDATEXML(xml_document, xpath_string, new_value)

xml_document is an XMLType expression that represents the XML document to be modified.
xpath_string is a string that specifies the location of the node to be updated in the XML document. The XPath language is used to define this string.
new_value is the new value that you want to set for the node.

Example

Let’s say you have an XML document stored in a column of a table in your database, and you want to update the value of a specific element within that XML document. You can use the UPDATEXML function in PL/SQL to achieve this.

For example, let’s assume you have a table called “employees” with columns “id” and “xml_data”, where “xml_data” contains an XML document that looks like this:

<employee>
  <id>1234</id>
  <name>John Doe</name>
  <salary>50000</salary>
</employee>

To update the value of the “salary” element to 60000 for the employee with id 1234, you can use the following PL/SQL code:

DECLARE
  l_xml XMLType;
BEGIN
  SELECT xml_data INTO l_xml FROM employees WHERE id = 1234;
  UPDATE employees
  SET xml_data = UPDATEXML(l_xml, '/employee/salary/text()', '60000')
  WHERE id = 1234;
END;

In this example, we first select the XML data for the employee with id 1234 into a variable called “l_xml”. We then use the UPDATEXML function to update the value of the “salary” element to 60000 within the XML document, and assign the result back to the “xml_data” column using an UPDATE statement.

Note that the first parameter of the UPDATEXML function is the XML document being updated, the second parameter is an XPath expression that identifies the element being updated, and the third parameter is the new value for that element.