Sql Xquery 如何替换更新查询中的文本 [英] Sql Xquery How to Replace text in Update Query
问题描述
表被命名为MasterTable
列
ID
type BIGINT
,
Name
type VARCHAR(200)
(由于某些原因存储xml
类型数据)
Name
type VARCHAR(200)
(stores xml
type data for some reasons)
Name
包含结构为
<en-US>SomeEnglishText</en-US><it-IT>SomeItalicText</it-IT>
当我需要 Update
Master
表时,我需要将 Varchar
转换为 xml
然后有条件地更新/替换特定标签的value
部分,即en-US/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
.
还有可能 没有数据/标签在 Name
列中,所以我认为在插入数据时它会 Insert
表中的空标签元素如
,所以 update
查询必须处理标签元素中的空值,即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
非法使用 xml 数据类型方法修改".在这种情况下需要一个非增变方法.
Illegal use of xml data type method 'modify'. A non-mutator method is expected in this context.
推荐答案
您不能从 xml.modify 分配.修改直接作用于变量/列.您也不能对演员表使用修改.
You can not assign from a xml.modify. Modify works on the variable/column directly. You can also not use modify on a cast.
您可以将名称提取到 xml 变量中,修改 xml,然后将其放回表中.
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
如果您需要一次处理多于一行,您可以使用包含列 id
和 name
的表变量,其中 name 的数据类型为 xml
而不是 @xml
变量.
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
这篇关于Sql Xquery 如何替换更新查询中的文本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!