How to update existing XML by changing or adding new elements in Oracle DB?

Assuming we have a task to update XML data stored in Oracle database table. Let it be XMLTYPE column. Actually it can either be CLOB or BLOB. Does not make big sense, because can go though conversions to XMLTYPE anyway. Just matter of performance.

Here I’ll provide sample, how to proceed in 2 cases:

1. Update XML by changing values in existing XML element
2. Update XML by appending new element to an existing element sequence

First create a demo table with sample XML in it:

create table xml_update_test (id number, sample_xml xmltype);
insert into xml_update_test values(1,xmltype('<employees><employee name="John Smith" location_id="15"/><employee name="Peter Graham" location_id="20"/></employees>'));
commit;

1. Lets change location_id to “30” for employee John Smith. To fullfill this task, SQL function UPDATEXML will be leveraged.

UPDATEXML(Xmltype source_xml, String xpath_to_target_node,Xmltype new_target_node)

The trick is that you can’t update a value directly inside of XML, you must update/switch whole xml first changing desired elements inside of it:

update xml_update_test set sample_xml=updatexml(sample_xml,'/employees/employee[@name="John Smith"]',xmltype('<employee name="John Smith" location_id="30"/>'));
commit;

2. Lets add another employee. To fullfill this task, SQL function APPENDCHILDXML will be leveraged.

APPENDCHILDXML(Xmltype source_xml, String xpath_to_parent_node,Xmltype new_node)

update xml_update_test set sample_xml=appendchildxml(sample_xml,'/employees',xmltype('<employee name="Steven Reilly" location_id="50"/>'));
commit;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0