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.

Mikael Eriksson · Accepted Answer · 2011-02-26 10:31:21Z

20

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

edited Feb 26, 2011 at 10:31

answered Feb 23, 2011 at 9:20

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Harsh Baid Over a year ago

I had changed this declare @str varchar(200) = 'Test' to declare @str varchar(200) set @str = 'Test' and it worked.. Thanks a lot

Harsh Baid Over a year ago

Can you tell that how to do if the previous code was COALESCE(@Name, Name) and now I want to changed it as UPDATE MasterTable set Name = Name.modify('replace value of (en-US/text())[1] with sql:variable("@Str")') where ID = 18 like-wise i.e handle NULLs in @Str

Harsh Baid Over a year ago

Also as the Name column is VARCHAR(200) so I am facing problem of directly calling modify on it

Mikael Eriksson Over a year ago

@Harsh: You can not call modify directly on a varchar column. You need to move it to a xml column (or variable) to use modify as in the code in my answer.

Harsh Baid Over a year ago

So here as we need to first take the values in separate @xml variable so if We have multi columns as Name on which we want to update then we would need to declare multiple @xml variables to update multiple language columns, this is not good I think...

|

Collectives™ on Stack Overflow

Sql Xquery How to Replace text in Update Query

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related