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
– Aaron Bertrand, Commented Sep 22, 2011 at 12:21

Mikael Eriksson · Accepted Answer · 2011-09-22 13:27:41Z

7

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>

edited Sep 22, 2011 at 13:27

answered Sep 22, 2011 at 12:58

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

3 Comments

Dalex Over a year ago

But if need, replacement can be done as: UPDATE A SET rest.modify('insert <e><k>link</k><v>{sql:column("idstr_new")}</v></e> into (/root)[1]') FROM (SELECT B.rest,REPLACE(B.idstr,'&','&') idstr_new FROM @Art B WHERE idstr LIKE '%&%' --AND parent = 160 ) A'

Michas Over a year ago

Well. It looks it works. My problem was trivial after all. However, I'd like a solution that works with more complicated TSQL expressions.

Mikael Eriksson Over a year ago

@Michas - Updated answer with a more "complicated" expression.

Aaron Bertrand · Accepted Answer · 2011-09-22 12:50:40Z

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, 2011 at 12:50

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

1 Comment

Michas Over a year ago

It looks it works. However, I'd like to avoid executing TSQL from strings.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related