Your XML is invalid - the
<root>
node is not closed.
XML is case-sensitive. In the inserted XML, the root element is called
<root>
, so an XPath query for
ROOT
isn't going to match it.
Your
WHERE
clause extracts the ID of the
first <Employee>
node, which is
100
. That is not equal to your
@OldEmployeeId
variable, so there are no rows to update.
Your
replace value of
statement is replacing the ID of the
first <Employee>
node, which is not what you're trying to do.
Change your
UPDATE
query to:
UPDATE @Employee
SET empXML.modify('replace value of (root/Employee[@Id = sql:variable("@OldEmployeeId")]/@Id)[1] with sql:variable("@NewEmployeeId")')
WHERE empXML.exist('root/Employee[@Id = sql:variable("@OldEmployeeId")]') = 1;
replace value of (XML DML)[
^]
exist() Method (xml Data Type)[
^]
XPath Reference[
^]