Sql Xquery 如何替换更新查询中的文本

发布于 2024-10-19 07:12:44 字数 1087 浏览 3 评论 0原文

表命名为 MasterTable

列

ID 类型 BIGINT，

Name 类型 VARCHAR(200) （由于某些原因存储 xml 类型数据）

Name 包含结构为

SomeEnglishText的数据;SomeItalicText

当我需要更新 Master 表时，我需要转换 Varchar > 到 xml，然后有条件地更新/替换特定标记的 value 部分，即 en-US / it-IT。

此外，名称列中也有可能没有数据/标签，因此我认为在插入数据时，它会插入空标签元素在这样的表中，因此 update 查询 < strong>必须处理标记元素中的空值，即en-US/it-IT。

我正在尝试像以下更新查询一样执行此操作。

DECLARE @Str VARCHAR(200)

SET @Str = 'Test Text'

UPDATE [MasterTable]
SET [Name] = cast([MasterTable].[Name] as xml).modify('replace value of (en-US/text())[1] with sql:variable("@Str")')
WHERE [ID]=18

运行查询时出现以下错误

非法使用 xml 数据类型方法“修改”。在这种情况下，需要使用非修改器方法。

原文

Table is named as MasterTable

Columns

ID type BIGINT,

Name type VARCHAR(200) (stores xml type data for some reasons)

Name contains data structured as

<en-US>SomeEnglishText</en-US><it-IT>SomeItalicText</it-IT>

When I need to Update the Master Table then at that time I Need to cast the Varchar to xml then conditionally update / replace the value part of particular tag i.e either en-US / it-IT.

Also there are chances that No data/tags are there in Name column so I think at the time of Inserting data it would Insert empty tag elements in the table like <en-US></en-US><it-IT></it-IT>, so the update query must handle empty value in tag elements namely en-US/it-IT.

I am trying to do it like following update query.

DECLARE @Str VARCHAR(200)

SET @Str = 'Test Text'

UPDATE [MasterTable]
SET [Name] = cast([MasterTable].[Name] as xml).modify('replace value of (en-US/text())[1] with sql:variable("@Str")')
WHERE [ID]=18

I getting following error when running the query

Illegal use of xml data type method 'modify'. A non-mutator method is expected in this context.

分享到QQ

分享到微博

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

发布评论

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

带刺的爱情 2024-10-26 07:12:44

您不能从 xml.modify 进行分配。修改直接作用于变量/列。您也不能在强制转换上使用修改。

您可以将名称提取到 xml 变量，修改 xml，然后将其放回表中。

declare @str varchar(200) = 'Test'
declare @xml xml

select @xml = cast(Name as xml)
from MasterTable
where ID = 18

set @xml.modify('replace value of (en-US/text())[1] with sql:variable("@Str")')

update MasterTable
set Name = cast(@xml as varchar(200))
where ID = 18

如果您需要一次处理多行，您可以使用包含 id 和 name 列的表变量，其中 name 的数据类型为 xml< /code> 而不是 @xml 变量。

declare @str varchar(200) = 'Test Text'
declare @T table (ID int, Name xml)

insert into @T
select ID, cast(Name as xml)
from MasterTable
where Name is not null

update @T
set Name.modify('replace value of (en-US/text())[1] with sql:variable("@Str")')

update MasterTable
set Name = cast(T.Name as varchar(200))
from @T as T
where MasterTable.ID = T.ID

You can not assign from a xml.modify. Modify works on the variable/column directly. You can also not use modify on a cast.

You can extract the name to a xml variable, modify the xml and then put it back to the table.

declare @str varchar(200) = 'Test'
declare @xml xml

select @xml = cast(Name as xml)
from MasterTable
where ID = 18

set @xml.modify('replace value of (en-US/text())[1] with sql:variable("@Str")')

update MasterTable
set Name = cast(@xml as varchar(200))
where ID = 18

If you need this to work over more than one row at a time you can use a table variable with columns id and name where data type for name is xml instead of the @xml variable.

declare @str varchar(200) = 'Test Text'
declare @T table (ID int, Name xml)

insert into @T
select ID, cast(Name as xml)
from MasterTable
where Name is not null

update @T
set Name.modify('replace value of (en-US/text())[1] with sql:variable("@Str")')

update MasterTable
set Name = cast(T.Name as varchar(200))
from @T as T
where MasterTable.ID = T.ID

回复收藏 0 原文

~没有更多了~