How to update xml node value based on another node value in SQL Server

Question

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?

Paweł Dyl · Accepted Answer · 2018-09-18 10:50:37Z

2

To select node with id, use following:

DECLARE @xml xml = '<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>'

SELECT @xml.value('(//employee[id=3]/name)[1]', 'nvarchar(max)')

To modify, use:

SET @xml.modify('replace value of (//employee[id=3]/name/text())[1] with "xyz"')

answered Sep 18, 2018 at 10:50

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update xml node value based on another node value in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related