If we want to designate a new project for employee ID #2, we can do the following: The newest employee now needs salary data.
Employee #4 is at tier 4, like employee #1, and should be at the same salary range as employee #1.
You may notice that we designated a singleton (single node value) representation of ' for Expression 2 (the Salaries destination node).
This is because the insert keyword requires that Expression 2 be a single node.
Oddly enough, we tell SQL which records to update at the very end with our at the end unless you want to update every record.
For example, an operation that tries to update the salary amount of two different employees in the following manner will fail: UPDATE HR_XML SET Salaries.modify('replace value of (/Salaries/Marketing/Employee[@ID=("2")]/Salary/text()) with ("60000")'), Salaries.modify('replace value of (/Salaries/Marketing/Employee[@ID=("1")]/Salary/text()) with ("60000")') GO Instead, multiple updates to the same XML value must be accomplished using entirely separate UPDATE statements. We need to update the salary amount to match that of employee #2 (60600): Notice that we had to perform two separate modify() operations in order to update the salary and tier data.Let's insert a Salary node for employee #4, and populate it with the salary amount from employee #1's Salary node: We've deleted the empty node by indicating its singleton value (2).Node values can be deleted by employing the text() function to specify that a value, not a node, is to be removed.The following will fail: This is because modify() operates directly on the XML value, whether it is a column or variable, and cannot be used in an ad-hoc operation that does not update the underlying XML value.An alternative to the above operation would be to select the XML value into a new XML variable, make the change, and then select the variable value: , on the same XML value.UPDATE searches for whatever record or records that satisfy its WHERE condition.