Sql Xquery How to Replace text in Update Query

Question

Table is named as MasterTable

Columns

ID type BIGINT,

Name type VARCHAR(200) (stores xml type data for some reasons)

Name contains data structured as

<en-US>SomeEnglishText</en-US><it-IT>SomeItalicText</it-IT>

When I need to Update the Master Table then at that time I Need to cast the Varchar to xml then conditionally update / replace the value part of particular tag i.e either en-US / it-IT.

Also there are chances that No data/tags are there in Name column so I think at the time of Inserting data it would Insert empty tag elements in the table like <en-US></en-US><it-IT></it-IT>, so the update query must handle empty value in tag elements namely en-US/it-IT.

I am trying to do it like following update query.

DECLARE @Str VARCHAR(200)

SET @Str = 'Test Text'

UPDATE [MasterTable]
SET [Name] = cast([MasterTable].[Name] as xml).modify('replace value of (en-US/text())[1] with sql:variable("@Str")')
WHERE [ID]=18

I getting following error when running the query

Illegal use of xml data type method 'modify'. A non-mutator method is expected in this context.

Answer 1

You can not assign from a xml.modify. Modify works on the variable/column directly. You can also not use modify on a cast.

You can extract the name to a xml variable, modify the xml and then put it back to the table.

declare @str varchar(200) = 'Test'
declare @xml xml

select @xml = cast(Name as xml)
from MasterTable
where ID = 18

set @xml.modify('replace value of (en-US/text())[1] with sql:variable("@Str")')

update MasterTable
set Name = cast(@xml as varchar(200))
where ID = 18

If you need this to work over more than one row at a time you can use a table variable with columns id and name where data type for name is xml instead of the @xml variable.

declare @str varchar(200) = 'Test Text'
declare @T table (ID int, Name xml)

insert into @T
select ID, cast(Name as xml)
from MasterTable
where Name is not null

update @T
set Name.modify('replace value of (en-US/text())[1] with sql:variable("@Str")')

update MasterTable
set Name = cast(T.Name as varchar(200))
from @T as T
where MasterTable.ID = T.ID