如何替换SQL Server中的XML节点属性值 [英] How to replace XML node attribute value in SQL server

问题描述

我想用新值更新xml节点属性值。基于以下条件

条件1：如果xml行中已存在新值，则从xml中删除旧值节点

条件2：如果xml行中不存在新值然后用新值替换旧值。



我尝试过：

 DECLARE @Employee TABLE（empXML XML）; 
 
 INSERT INTO @Employee VALUES（'< ROOT>< Employee Id =100/>< Employee Id =101/>< Employee Id =102/ >< / ROOT>'）
 INSERT INTO @Employee VALUES（'< ROOT>< Employee Id =100/>< Employee Id =106/>< Employee Id =107/>< / ROOT>'）
 INSERT INTO @Employee VALUES（'< ROOT>< Employee Id =100/>< Employee Id =102/> ;<员工ID =109/>< / ROOT>'）
 INSERT INTO @Employee VALUES（'< ROOT>< Employee Id =111/>< Employee Id = 107/><员工ID =101/>< / ROOT>'）
 
 SELECT * FROM @Employee 
 
 DECLARE @OldEmployeeId INT = 101，@ NewEmployeeId INT = 100; 
 
如果EXISTS（SELECT 1 FROM @Employee WHERE empXML.exist（'ROOT / Employee [@ Id = sql：variable（@ NewEmployeeId）]'）= 1）
 BEGIN 
 UPDATE @Employee 
 SET empXML.modify（'删除ROOT / Employee [@Id eq sql：variable（@ OldEmployeeId）]'）
 END 
 ELSE 
 BEGIN 
 UPDATE @Employee 
 SET empXML.modify（'替换值（ROOT / Employee [@Id eq sql：variable（@ OldEmployeeId）] / @ Id）[1]与sql：variable （@NewEmployeeId）'）
 WHERE empXML.exist（'ROOT / Employee [@Id eq sql：variable（@ OldEmployeeId）]'）= 1; 
 END 
 
 SELECT * FROM @Employee 
 
但它没有按预期工作。请帮助我。

解决方案

如果我已经理解你要做什么，这样的事情应该有效：

 DECLARE @Employee TABLE（ empXML XML）; 
 
 INSERT INTO @Employee VALUES（'< ROOT>< Employee Id =100>< Employee Id =101/>< Employee Id =102/> ;< / ROOT>'）; 
 INSERT INTO @Employee VALUES（'< ROOT>> Employee Id =100/>< Employee Id =106/>< Employee Id =107/>< / ROOT>'）; 
 INSERT INTO @Employee VALUES（'< ROOT>< Employee Id =100/>< Employee Id =102/>< Employee Id =109/>< / ROOT>'）; 
 INSERT INTO @Employee VALUES（'< ROOT>< Employee Id =111/>< Employee Id =107/>< Employee Id =101/>< / ROOT>'）; 
 
 SELECT * FROM @Employee; 
 
 DECLARE @OldEmployeeId INT = 101，@ NewEmployeeId INT = 100; 
 
 UPDATE @Employee 
 SET empXML.modify（'删除ROOT / Employee [@Id eq sql：variable（@ OldEmployeeId）]'）
 WHERE empXML.exist（ 'ROOT / Employee [@Id eq sql：variable（@ NewEmployeeId）]'）= 1; 
 
 UPDATE @Employee 
 SET empXML.modify（'替换值（ROOT / Employee [@Id eq sql：variable（@ OldEmployeeId）] / @ Id）[1] with sql：variable（@ NewEmployeeId）'）
 WHERE empXML.exist（'ROOT / Employee [@Id eq sql：variable（@ OldEmployeeId）]'）= 1; 
 
 SELECT * FROM @Employee; 

输出：

 <   ROOT  >  <  员工    Id   =  100    /  >  <  员工    Id   =  102    /  >  <   / ROOT  >  
 <   ROOT  >  <  员工    Id   =  100    /  >  <  员工    Id   =  106    /  >  <  员工    Id   =  107    /  >  <   / ROOT  >  
 <   ROOT  >  <  员工    Id   =  100    /  >  <  员工    Id   =  102     /  >  <  员工    Id   =  109    /  >  <   / ROOT  >  
 < ;   ROOT  >  <  员工    Id   =  111    /  >  <  员工    Id   =  107    /  >  <  员工    Id   =  100    /  >  <   / ROOT  >  

I want to update xml node attribute value with new value. based on following condition
Condition 1: If new value already exists in xml row then delete old value node from xml
Condition 2: If new value does not exists in xml row then replace old value with new value.

What I have tried:

DECLARE @Employee TABLE(empXML XML); 

INSERT INTO @Employee VALUES('<ROOT><Employee Id="100"/><Employee Id="101"/><Employee Id="102"/></ROOT>')
INSERT INTO @Employee VALUES('<ROOT><Employee Id="100"/><Employee Id="106"/><Employee Id="107"/></ROOT>')
INSERT INTO @Employee VALUES('<ROOT><Employee Id="100"/><Employee Id="102"/><Employee Id="109"/></ROOT>')
INSERT INTO @Employee VALUES('<ROOT><Employee Id="111"/><Employee Id="107"/><Employee Id="101"/></ROOT>')

SELECT * FROM @Employee

DECLARE @OldEmployeeId INT=101, @NewEmployeeId INT=100;

IF EXISTS(SELECT 1 FROM @Employee WHERE empXML.exist('ROOT/Employee[@Id=sql:variable("@NewEmployeeId")]') = 1)
BEGIN
	 UPDATE @Employee 
	 SET empXML.modify('delete ROOT/Employee[@Id eq sql:variable("@OldEmployeeId")]') 
END
ELSE
BEGIN
	UPDATE @Employee
	SET empXML.modify('replace value of (ROOT/Employee[@Id eq sql:variable("@OldEmployeeId")]/@Id)[1] with sql:variable("@NewEmployeeId")')
	WHERE empXML.exist('ROOT/Employee[@Id eq sql:variable("@OldEmployeeId")]') = 1;	
END

SELECT * FROM @Employee

But it is not working as expected.Please help me.

解决方案

If I've understood what you're trying to do, something like this should work:

DECLARE @Employee TABLE(empXML XML); 

INSERT INTO @Employee VALUES('<ROOT><Employee Id="100"><Employee Id="101"/><Employee Id="102"/></ROOT>');
INSERT INTO @Employee VALUES('<ROOT>>Employee Id="100"/><Employee Id="106"/><Employee Id="107"/></ROOT>');
INSERT INTO @Employee VALUES('<ROOT><Employee Id="100"/><Employee Id="102"/><Employee Id="109"/></ROOT>');
INSERT INTO @Employee VALUES('<ROOT><Employee Id="111"/><Employee Id="107"/><Employee Id="101"/></ROOT>');

SELECT * FROM @Employee;

DECLARE @OldEmployeeId INT=101, @NewEmployeeId INT=100;

UPDATE @Employee 
SET empXML.modify('delete ROOT/Employee[@Id eq sql:variable("@OldEmployeeId")]')
WHERE empXML.exist('ROOT/Employee[@Id eq sql:variable("@NewEmployeeId")]') = 1;

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

SELECT * FROM @Employee;

Output:

<ROOT><Employee Id="100" /><Employee Id="102" /></ROOT>
<ROOT><Employee Id="100" /><Employee Id="106" /><Employee Id="107" /></ROOT>
<ROOT><Employee Id="100" /><Employee Id="102" /><Employee Id="109" /></ROOT>
<ROOT><Employee Id="111" /><Employee Id="107" /><Employee Id="100" /></ROOT>

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