如何在 SQL Server 中通过将 XML 乘以 n 次来更新 XML 的现有节点值

Question

这是我的表字段中的XML

<CtcConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Ctc>3</Ctc>
<SalaryComponent>
<SalaryComponentConfiguration>
<Name>Basic</Name>
<DisplayOrder>0</DisplayOrder>
<Value>5634655</Value>
</SalaryComponentConfiguration>
<SalaryComponentConfiguration>
<Name>HR</Name>
<DisplayOrder>0</DisplayOrder>
<Value>1234</Value>
</SalaryComponentConfiguration>
<SalaryComponentConfiguration>
<Name>medical</Name>
<DisplayOrder>0</DisplayOrder>
<Value>0</Value>
</SalaryComponentConfiguration>
</SalaryComponent>
</CtcConfiguration>

我希望通过将现有node(DisplayOrder)值乘以 n 次来更新它。

这是我到目前为止更新节点值所得到的：

DECLARE @NodeName VARCHAR(100)=N'Basic';
DECLARE @NewValue INT=3;
UPDATE payroll.pays 
SET CtcConfiguration.modify(
N'replace value of (/CtcConfiguration
/SalaryComponent
/SalaryComponentConfiguration[(Name/text())[1]=sql:variable("@NodeName")]
/Value/text())[1] 
with sql:variable("@NewValue")');

Answer 1

我想为您提供两种方法：

使用这些变量来选择正确的节点并定义乘数

DECLARE @AttributeName VARCHAR(100)=N'medical';
DECLARE @Multiply INT=2;
UPDATE YourTable
SET YourXML.modify(N'replace value of (/CtcConfiguration
/SalaryComponent
/SalaryComponentConfiguration[(Name/text())[1]=sql:variable("@AttributeName")]
/DisplayOrder/text())[1] 
with xs:int((/CtcConfiguration
/SalaryComponent
/SalaryComponentConfiguration[(Name/text())[1]=sql:variable("@AttributeName")]
/DisplayOrder/text())[1]) * sql:variable("@Multiply")');

--或者，您可以使用可更新的 CTE：
--在这种情况下，您可以使用sql:column()而不是sql:variable()

SET @AttributeName=N'Basic';
WITH cte AS
(
SELECT *
--you can place any multiplier here
,10 * YourXML.value(N'(/CtcConfiguration
/SalaryComponent
/SalaryComponentConfiguration[(Name/text())[1]=sql:variable("@AttributeName")]
/DisplayOrder/text())[1] ',N'int') AS NewValue
FROM YourTable
)
UPDATE cte
SET YourXML.modify(N'replace value of (/CtcConfiguration
/SalaryComponent
/SalaryComponentConfiguration[(Name/text())[1]=sql:variable("@AttributeName")]
/DisplayOrder/text())[1] 
with sql:column("NewValue")');