如何在SQL Server中通过将XML乘以n倍来更新XML的现有节点值 [英] How to update the existing node value of XML by multiplying it with n times in SQL Server

问题描述

这是我在表格字段中使用的XML

Here is the XML I have in my Table Field

<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>

我想通过将其乘以n来更新现有的node(DisplayOrder)值.

I want the update the existing node(DisplayOrder) value by multiplying it with n times.

到目前为止，这是我更新节点值的方法:

This is what I get so far to update a node value:

    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")');

推荐答案

我想为您提供两种方法:

I want to offer you two approaches:

使用这些变量选择合适的节点并定义乘数

Use these variables to pick the right node and to define the multiplier

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()

--Alternatively you can use an updateable CTE:
--In this case you use sql:column() instead of 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")');

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