I have the following employees XML in "Test" table
<employees>
<employee>
<id>1</id>
<name>John doe 1</name>
</employee>
<employee>
<id>2</id>
<name>John doe 2</name>
</employee>
<employee>
<id>3</id>
<name>John doe 3</name>
</employee>
</employees>
I have to update the employee name whose id is 3. I am updating the following way.
update Test
set employeesXML.modify('replace value of (/employees/employee/name/text())[1]
with "xyz"')
where employeesXML.exist(N'/employees/employee/id[text()="3"]')
It updates the employee name with id 1 but if I don't specify the node index it throws an error.
How do I replace the value of a node based on the value of another node?
To select node with id, use following:
To modify, use: