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.)

Can you show something that resembles what you're trying to do? Right now it's pretty abstract. Also can you please specify the version of SQL Server, and whether the column really is the deprecated type `TEXT` or if you mean a string column like `VARCHAR`/`NVARCHAR`... — Aaron Bertrand, Sep 22 '11 at 12:21

Mikael Eriksson · Accepted Answer · 2011-09-22T13:27:41.507

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>

But if need, replacement can be done as: **UPDATE A SET rest.modify('insert link{sql:column("idstr_new")} into (/root)[1]') FROM (SELECT B.rest,REPLACE(B.idstr,'&','&') idstr_new FROM @Art B WHERE idstr LIKE '%&%' --AND parent = 160 ) A'** — Dalex, Sep 22 '11 at 13:04
Well. It looks it works. My problem was trivial after all. However, I'd like a solution that works with more complicated TSQL expressions. — Michas, Sep 22 '11 at 13:08
@Michas - Updated answer with a more "complicated" expression. — Mikael Eriksson, Sep 22 '11 at 13:28

score 1 · Answer 2 · answered Sep 22 '11 at 12:50

1

Assuming your table has a key:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + 'UPDATE [pwi_new].[dbo].[art] '
    + ' SET rest.modify(''insert <e><k>link</k><v>' 
    + REPLACE(idstr,'&','&amp;') 
    + '</v></e> into (/root)[1]'') WHERE key_column = ' 
    + CONVERT(VARCHAR(12), key_column) + ';'
FROM [pwi_new].[dbo].[art]
  WHERE parent = 160
  AND idstr LIKE '%&%';

PRINT @sql;
--EXEC sp_executesql @sql;

answered Sep 22 '11 at 12:50

Aaron Bertrand

272,866
37
466
490

It looks it works. However, I'd like to avoid executing TSQL from strings. – Michas Sep 22 '11 at 12:58

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

2 Answers2