How to update XML node using XML DML in SQL Server

Question

I want to update a XML variable in SQL Server 2012.

Sample XML:

<ArrayOfFieldInfo>
    <FieldInfo>
        <Name>abc</Name>
        <FriendlyName>friend2</FriendlyName>
        <Description>Custom added field</Description>
        <FieldGroupName>Custom Fields</FieldGroupName>
    </FieldInfo>
    <FieldInfo>
        <Name>efg</Name>
        <FriendlyName>friend1</FriendlyName>
        <Description>Custom added field</Description>
        <FieldGroupName>Custom Fields</FieldGroupName>
    </FieldInfo>
</ArrayOfFieldInfo>

I want to update node <FriendlyName> with new value 'friend3' where <Name> node value is 'efg'.

I have tried following code but it doesn't work.

DECLARE @xmlTable table (xmlfield xml)

INSERT INTO @xmlTable (xmlfield) 
VALUES (
    '<ArrayOfFieldInfo>
        <FieldInfo>
            <Name>abc</Name>
            <FriendlyName>friend2</FriendlyName>
            <Description>Custom added field</Description>
            <FieldGroupName>Custom Fields</FieldGroupName>
        </FieldInfo>
        <FieldInfo>
            <Name>efg</Name>
            <FriendlyName>friend1</FriendlyName>
            <Description>Custom added field</Description>
            <FieldGroupName>Custom Fields</FieldGroupName>
        </FieldInfo>
    </ArrayOfFieldInfo>'
)


UPDATE @xmlTable
SET xmlfield.modify('replace value of (/ArrayOfFieldInfo/FieldInfo/FriendlyName/text())[1] with "friend3"')
WHERE xmlfield.value('(/ArrayOfFieldInfo/FieldInfo/Name/text())[1]', 'nvarchar(50)') = 'efg'

Answer 1

Yes, but you must add the filter as a predicate:

Your mockup. I added an ID column and three cases

1. One "efg"
2. No "efg"
3. Multiple occurances of "efg"

--Check it out

DECLARE @xmlTable table (ID INT IDENTITY, xmlfield xml)

INSERT INTO @xmlTable (xmlfield) 
VALUES (
    '<ArrayOfFieldInfo>
        <FieldInfo>
            <Name>abc</Name>
            <FriendlyName>friend2</FriendlyName>
        </FieldInfo>
        <FieldInfo>
            <Name>efg</Name>
            <FriendlyName>friend1</FriendlyName>
        </FieldInfo>
    </ArrayOfFieldInfo>'
)
,(
    '<ArrayOfFieldInfo>
        <FieldInfo>
            <Name>abc</Name>
            <FriendlyName>friend2</FriendlyName>
        </FieldInfo>
        <FieldInfo>
            <Name>xyz</Name>
            <FriendlyName>friend1</FriendlyName>
        </FieldInfo>
    </ArrayOfFieldInfo>'
)
,(
    '<ArrayOfFieldInfo>
        <FieldInfo>
            <Name>efg</Name>
            <FriendlyName>friend2</FriendlyName>
        </FieldInfo>
        <FieldInfo>
            <Name>efg</Name>
            <FriendlyName>friend1</FriendlyName>
        </FieldInfo>
    </ArrayOfFieldInfo>'
)

--Let's use some variables to get this more generic

DECLARE @SearchName NVARCHAR(100)=N'efg';
DECLARE @ReplaceWith NVARCHAR(100)=N'ABCDEFG';

--The query

UPDATE @xmlTable
SET xmlfield.modify('replace value of 
                     (/ArrayOfFieldInfo
                      /FieldInfo[Name=sql:variable("@SearchName")]
                      /FriendlyName/text())[1] 
                     with sql:variable("@ReplaceWith")')
WHERE xmlfield.exist('/ArrayOfFieldInfo
                      /FieldInfo[Name=sql:variable("@SearchName")]')=1;

--Check the result

SELECT * 
FROM @xmlTable t
ORDER BY t.ID;

The first case will get the value replaced.
No replacements in the second case.
Just ONE (the first) occurance in the third case is replaced.

The idea in short:

The XML-DML-method replace value of needs a singleton. You tried to filter with a WHERE externally, but you must tell the engine which node within the XML you want to address. It is the <FieldInfo>, where the interal <Name> equals your search phrase. Below this <FieldInfo> we pick the <FriendlyName> and replace the first one we've found.

I added a WHERE using .exist(). This will/can speed up the process as it will reduce the .modify() to rows, which have at least one fitting target element.

And you can see, that .modify() is limited to just one change per call. This can be very annoying... A workaround might be to shred and recreate the XML or to use XQuery-FLWOR.