CODEWITHSUNDEEP
sqlsql-serverdatabase
dodo
dodo

Reputation: 159

Replacing Xml (SQL REPLACE) not affected

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

Upvotes: 0

Views: 30

Answers (2)

Charlieface
Charlieface

Reputation: 71638

Ideally you would not use REPLACE on XML in the first place. SQL Server has good XQuery capabilities, and you can replace a single node of XML using the special UPDATE ... SET ... .modify syntax

UPDATE temp_tab
SET xValue.modify('
    replace value of
    (/EP/P[@ID = "ExpyType_Code"]/@Value[. = "BOX"])[1]
    with
      "NEW_BOX"
  ')
WHERE ID = 1
  AND xValue.exist('/EP/P[@ID = "ExpyType_Code"]/@Value[. = "BOX"]') = 1;

db<>fiddle

The final exist clause is only necessary if you want to check if the old value actually exists before attempting to modify it.

Upvotes: 0

Stu
Stu

Reputation: 32609

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

Upvotes: 1

Related Questions