Как обновить столбец XML значением из встроенной функции T-SQL?

У меня есть стандартная база данных MS SQL 2008 R2. У меня есть столбец с данными varchar(250) и столбец с данными 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)
)

Проблема в том, что я хочу вставить результат строковой функции в xml примерно в 140 записях. Я пытался использовать xml.modify с динамически генерируемым текстом.

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

Однако у меня есть эта ошибка:

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

Есть идеи? Я хотел бы избежать использования временных полей, внешних языков и выполнения TSQL из сгенерированной строки? (Я слышал о sql:variable и sql:column, но это результат функции tsql.)


xml sql-server tsql sql-server-2008-r2 xml-dml
person Michas    schedule 22.09.2011    source источник
comment
Можете ли вы показать что-то похожее на то, что вы пытаетесь сделать? Сейчас это довольно абстрактно. Также не могли бы вы указать версию SQL Server и действительно ли столбец является устаревшим типом TEXT или если вы имеете в виду строковый столбец, например VARCHAR/NVARCHAR...   -  person Aaron Bertrand    schedule 22.09.2011
comment
Я отредактировал свой вопрос. Теперь должно быть более ясно.   -  person Michas    schedule 22.09.2011

Ответы (2)


arrow_upward
7
arrow_downward

Не уверен, что вы хотите сделать здесь. Вы упоминаете функцию TSQL, и если это замена & на &, то это не нужно. Об этом позаботится SQL Server.

Тест с использованием табличной переменной @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

Результат:

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

Обновить

В нетривиальных ситуациях вы можете использовать перекрестное применение, чтобы получить нужные вам значения в столбце.

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

Результат:

<root>
  <e>
    <k>link</k>
    <v>laivirToStoN654&amp;381</v>
  </e>
</root>
person Mikael Eriksson    schedule 22.09.2011
comment
Но при необходимости замену можно выполнить так: ОБНОВИТЬ НАБОР rest.modify('insert ‹e›‹k›link‹/k›‹v›{sql:column(idstr_new)}‹/v›‹/ e› в (/root)[1]') FROM (SELECT B.rest,REPLACE(B.idstr,'&','&') idstr_new FROM @Art B WHERE idstr LIKE '%&%' --AND parent = 160 ) А' - person Dalex; 22.09.2011
comment
Хорошо. Похоже, это работает. Моя проблема была тривиальной в конце концов. Однако мне нужно решение, которое работает с более сложными выражениями TSQL. - person Michas; 22.09.2011
comment
@Michas - обновленный ответ с более сложным выражением. - person Mikael Eriksson; 22.09.2011

arrow_upward
1
arrow_downward

Предполагая, что в вашей таблице есть ключ:

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;
person Aaron Bertrand    schedule 22.09.2011
comment
Похоже, это работает. Однако я бы хотел избежать выполнения TSQL из строк. - person Michas; 22.09.2011