Replacing Xml (SQL REPLACE) not affected-CodePudding

I have table with xml field xValue

Here is an example of xValue:

<EP><P ID="Expy_ServiceType" Value="box" /><P ID="Expy_ServiceName" Value="Expy Box" /><P ID="Expy_HasBranches" Value="True" /><P ID="ExpyType_Code" Value="BOX" /></EP>

I created update but value is not replaced.

UPDATE temp_tab
    SET xValue = REPLACE(CAST(xValue AS varchar(max)), 
        '<P ID="ExpyType_Code" Value="BOX" />', '<P ID="ExpyType_Code" Value="NEW_BOX" />')
    WHERE id = 1

CodePudding user response：

This is because when your xml value is cast to varchar it removes any spaces before closing tags, so you need to do the same for your replace:

REPLACE(CAST(xValue AS varchar(max)), 
  '<P ID="ExpyType_Code" Value="BOX"/>', '<P ID="ExpyType_Code" Value="NEW_BOX"/>')

See working fiddle