How to update an XML column with value from T-SQL built-in function?

Question

I have a MS SQL 2008 R2 Standard database. I have a column with varchar(250) data and a column with xml.

CREATE TABLE [dbo].[art](
[id] [int] IDENTITY(1,1) NOT NULL,
[idstr] [varchar](250) NULL,
[rest] [xml] NOT NULL,
    CONSTRAINT [PK_art] PRIMARY KEY CLUSTERED ([id] ASC)
)

The problem is I want to insert result of a string function into into xml, in about 140 records. I've tried to use xml.modify with dynamically generated text.

UPDATE [pwi_new].[dbo].[art]
SET rest.modify('insert <e><k>link</k><v>' 
      + my_string_function(idstr) + '</v></e> into (/root)[1]')
  WHERE parent = 160
  AND idstr LIKE '%&%'
GO

However, I've got this error:

The argument 1 of the XML data type method "modify" must be a string literal.

Any ideas? I'd like to avoid using temporal fields, external languages and executing TSQL from generated string? (I've heard of sql:variable and sql:column, but this is a result of tsql function.)

Answer 1

Not sure what you want to do here. You mention a TSQL function and if that is the replace & to & it is not necessary. It is taken care of by SQL Server for you.

A test using a table variable @art:

declare @art table(parent int, idstr varchar(250), rest xml)

insert into @art values
(160, '123&456', '<root></root>')

update @art 
set rest.modify('insert (<e><k>link</k><v>{sql:column("idstr")}</v></e>) into (/root)[1]') 
where parent = 160 and
      idstr like '%&%'

select rest
from @art 

Result:

<root>
  <e>
    <k>link</k>
    <v>123&amp;456</v>
  </e>
</root>

Update

For the not so trivial situations you can use a cross apply to get the values you need into a column.

declare @art table(parent int, idstr varchar(250), rest xml)

insert into @art values
(160, '123&456', '<root></root>')

update a
set rest.modify('insert (<e><k>link</k><v>{sql:column("T.X")}</v></e>) into (/root)[1]') 
from @art as a
  cross apply (select reverse(replace(idstr, '2', '8')+'NotSoTrivial')) as T(X)
where parent = 160 and
      idstr like '%&%'

select rest
from @art 

Result:

<root>
  <e>
    <k>link</k>
    <v>laivirToStoN654&amp;381</v>
  </e>
</root>