SQL XQuery如何在更新查询中替换文本。

Question

表名为MasterTable

列

ID类型为BIGINT，

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

Name包含结构化数据

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

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

还有可能Name列中没有数据/标签，因此我认为在插入数据时应该在表中插入空标签元素，如<en-US></en-US><it-IT></it-IT>，因此必须处理标签元素中的空值，即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数据类型方法“modify”。在此上下文中，应该使用非变异方法。

- Harsh Baid

Answer 1

您无法从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 的表变量，其中名称的数据类型为 xml 而不是 @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

- Mikael Eriksson

---

@Harsh：你不能直接在一个varchar列上调用modify。你需要将它移动到一个xml列（或变量）中，才能像我回答中的代码一样使用modify。 - Mikael Eriksson

所以在这里，我们需要首先将值存储在单独的@xml变量中，因此如果我们有多列Name需要更新，则需要声明多个@xml变量来更新多个语言列，我认为这不是一个好方法... - Harsh Baid

@MikaelEriksson，你能否编辑一下这个问题，因为我不知道如何添加多行。 - Harsh Baid

@MikaelEriksson 你非常有帮助，非常感谢你:D - Harsh Baid

您还可以使用 sql:column 来使用其他列中的值。例如：UPDATE @TXML SET Config.modify('replace value of (/UNIT_CONFIG/CONTRACTOR/text())[1] with sql:column("NewContractorId")') - scar80