如何使用 T-SQL 内置函数的值更新 XML 列？

发布于 2024-12-06 03:01:06 字数 864 浏览 0 评论 0原文

我有一个 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 函数的结果。）

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

仅冇旳回忆 2024-12-13 03:01:06

不确定你想在这里做什么。您提到了一个 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&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&381</v>
  </e>
</root>

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&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&381</v>
  </e>
</root>

回复收藏 0 原文

把昨日还给我 2024-12-13 03:01:06

假设你的表有一个键：

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,'&','&') 
    + '</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;

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,'&','&') 
    + '</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;

回复收藏 0 原文

~没有更多了~