如何在SQL Server中使用新值更新XML节点值 [英] How to update XML node value with new value in SQL server

问题描述

例如

DECLARE @Employee TABLE（empXML XML）;

DECLARE @OldEmployeeId INT = 103，@ NewEmployeeId INT = 999;



INSERT INTO @Employee VALUES（'< root>< Employee Id =100/>< Employee Id =102/>< Employee Id =103/ ><员工ID =104/>'）



SELECT * FROM @Employee



我想将员工ID的值更新为999，其中员工ID = 103.



我尝试过：



我试过这个，但没有运气。



UPDATE @Employee SET

empXML.modify （用'sql：variable替换（ROOT / Employee / @ Id）[1]的值（@ NewEmployeeId）'）
WHERE empXML.value（'（ROOT / Employee / @ Id） [1]'，'INT'）= @OldEmployeeId

e.g.
DECLARE @Employee TABLE(empXML XML);
DECLARE @OldEmployeeId INT=103, @NewEmployeeId INT=999;

INSERT INTO @Employee VALUES('<root><Employee Id="100"/><Employee Id="102"/><Employee Id="103"/><Employee Id="104"/>')

SELECT * FROM @Employee

I want to update value of Employee Id to 999 where Employee Id =103.

What I have tried:

I tried this but no luck.

UPDATE @Employee SET
empXML.modify('replace value of (ROOT/Employee/@Id)[1] with sql:variable("@NewEmployeeId")')
WHERE empXML.value('(ROOT/Employee/@Id)[1]', 'INT') = @OldEmployeeId

推荐答案

您的XML无效 - < root> 节点未关闭。



XML区分大小写。在插入的XML中，根元素称为< root> ，因此 ROOT 的XPath查询不是要匹配它。



您的 WHERE 子句提取第一个的ID < Employee> 节点， 100 。这不等于您的 @OldEmployeeId 变量，因此没有要更新的行。



您的替换语句的值正在替换第一个 < Employee> 节点的ID，这是不是你要做的。



将更新查询更改为：

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;

替换（XML DML）的值 [ ^ ]

exists（）方法（xml数据类型） [ ^ ]

XPath参考 [ ^ ]

replace value of (XML DML)[^]
exist() Method (xml Data Type)[^]
XPath Reference[^]

这篇关于如何在SQL Server中使用新值更新XML节点值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！