10

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.

Harsh Baid
  • 7,199
  • 5
  • 48
  • 92

1 Answers1

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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 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 Feb 24 '11 at 08:54
  • 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 Feb 26 '11 at 05:43
  • Also as the `Name` column is `VARCHAR(200)` so I am facing problem of directly calling `modify` on it – Harsh Baid Feb 26 '11 at 05:46
  • 1
    @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. – Mikael Eriksson Feb 26 '11 at 06:56
  • 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... – Harsh Baid Feb 26 '11 at 07:32
  • @Harsh: You can use a table variable with multiple xml columns. – Mikael Eriksson Feb 26 '11 at 08:04
  • @MikaelEriksson can you edit the question for multiple rows because I am not able to get how to do that. – Harsh Baid Feb 26 '11 at 09:07
  • @MikaelEriksson You are much helpful Huge Thank to you :D – Harsh Baid Feb 26 '11 at 10:55
  • You can also use sql:column to use value from other column. Example: UPDATE @TXML SET Config.modify('replace value of (/UNIT_CONFIG/CONTRACTOR/text())[1] with sql:column("NewContractorId")') – scar80 Sep 04 '17 at 07:56