How to replace values in xml from sql?

Question

I have an xml in my SQL Server table like this:

<Category>
    <Attributes>
        <Attribute>
            <Name>GeneratorOnBoard1</Name>
            <Value>Yes</Value>
        </Attribute>
        <Attribute>
            <Name>GeneratorOnBoard2</Name>
            <Value>Yes</Value>
        </Attribute>
    </Attributes>
</Category>

I want to replace the value of GeneratorOnBoard1 from 'yes' to 'yes please' but should not change the value of GeneratorOnBoard2.

If I use this:

declare @xml xml=''

select cast (replace (cast(@xml as nvarchar(max)), 'yes','yes please') as xml)

it might replace all the yes values.

What should I do?

Refer - https://stackoverflow.com/questions/12545964/updating-xml-attributes-with-new-values-in-a-sql-server-2008-table hope it helps. — Krunal Shah, Sep 26 '18 at 05:37

score 0 · Answer 1 · answered Sep 26 '18 at 06:04

0

Bit easy and alternative way is to use STUFF with PATINDEX as below

SELECT 
    STUFF(CAST(@xml AS NVARCHAR(MAX)), 
           PATINDEX('%Yes%', CAST(@xml AS NVARCHAR(MAX))), 3, 'Yes Please');

answered Sep 26 '18 at 06:04

Shushil Bohara

5,556
2
15
32

Mikael Eriksson · Answer 2 · 2018-09-26T09:47:09.647

0

Have a look at using replace value of (XML DML).

declare @xml xml = '<Category>
    <Attributes>
        <Attribute>
            <Name>GeneratorOnBoard1</Name>
            <Value>Yes</Value>
        </Attribute>
        <Attribute>
            <Name>GeneratorOnBoard2</Name>
            <Value>Yes</Value>
        </Attribute>
    </Attributes>
</Category>';

set @xml.modify('replace value of (/Category
                                    /Attributes
                                     /Attribute[(Name/text())[1] = "GeneratorOnBoard1" and
                                                (Value/text())[1] = "Yes"]
                                      /Value/text())[1] 
                 with "yes please"');

edited Sep 26 '18 at 09:47

answered Sep 26 '18 at 06:37

Mikael Eriksson

136,425
22
210
281

it will replace all the values of generatoronboard valuse ryt? i dont want to replace if value is no, only for yes i need to change – hilda_sonica_vish Sep 26 '18 at 09:01
@hilda_sonica_vish No it will not. It will only replace the the first value it finds. If you want to replace multiple values you have to do that in a loop. I added the condition that the Value has to be "Yes" to my answer. – Mikael Eriksson Sep 26 '18 at 09:48

How to replace values in xml from sql?

2 Answers2