SQL Server XML file update the value of one node where text of other node is XXX

Question

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>

I want the update the value of node(DisplayOrder) by depending on the node(Name). For example if I give name as medical it should update the displayorder value as required.

This is what I tried so far:

UPDATE payroll.pays set 
    CtcConfiguration.modify('replace value of (/CtcConfiguration/SalaryComponent/SalaryComponentConfiguration/DisplayOrder/text())[1] with ("99999")') 
where 
    CtcConfiguration.value('((/CtcConfiguration/SalaryComponent/SalaryComponentConfiguration/Name)[]/text())[1]','varchar(50)') = 'HR'

Shnugo · Accepted Answer · 2018-06-04T14:09:39.893

Try it like this:

hint: This example manipulates the <Value> but it works in the same way for <DisplayOrder> too.

DECLARE @mockupTable TABLE(ID INT IDENTITY, YourXML XML);
INSERT INTO @mockupTable VALUES
(N'<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>');


DECLARE @AttributeName VARCHAR(100)=N'medical';
DECLARE @NewValue INT=12345;

UPDATE @mockupTable
SET YourXML.modify(N'replace value of (/CtcConfiguration
                                       /SalaryComponent
                                       /SalaryComponentConfiguration[(Name/text())[1]=sql:variable("@AttributeName")]
                                       /Value/text())[1] 
                     with sql:variable("@NewValue")');


SELECT * FROM @mockupTable;

Short explanation

your XML is a rather simple Attribute-Value model (with a visible rank). You use an XQuery predicate to filter the correct attribute and set this special <Value>. So you have to look for the <SalaryComponentConfiguration> where the <Name> has got a special content.

Hi @Shnugo, thanks for the response it worked as expected. I come up with a new question is there any way to update the existing node value by multiplying the value with n times? — Naveen, Jun 05 '18 at 07:55
Hi @Naveen, I'm glad to help you... One principle of SO is: *One issue, one question*. This makes it easy to search within millions of answered questions. So please do not ask *follow-up* questions in comments. Start a new question, place a link here and wait for the armada rushing in to help you. — Shnugo, Jun 05 '18 at 07:58
https://stackoverflow.com/questions/50695380/how-to-update-the-existing-node-value-of-xml-by-multiplying-it-with-n-times-in-s — Naveen, Jun 05 '18 at 08:21

SQL Server XML file update the value of one node where text of other node is XXX

1 Answers1

Short explanation