在SQL Server 2005中使用转换更新XML节点

Question

我有一个包含XML数据的列，但它是TEXT类型而不是XML类型。（由于另一个原因，我必须保留它。）

基本上，我需要先将其转换为NText，然后再转换为XML。唯一的问题是，我当前可用于选择节点值的格式无法用于更新节点值。

错误信息：'AS' 关键字附近语法不正确。

UPDATE tbl_Module_RequestForms_Items
 SET CAST(CAST(TicorOregon..tbl_Module_RequestForms_Items.XML AS NTEXT) AS XML).value('(//Record/Submitted)[1]', 'NVARCHAR(max)') = 'True'
 WHERE CAST(CAST(TicorOregon..tbl_Module_RequestForms_Items.XML AS NTEXT) AS XML).value('(//Record/Submitted)[1]', 'NVARCHAR(max)') <> 'True'

XML 数据：

 <Record>
   <Submitted>False</Submitted>
 </Record>

- the sandman

---

如果它是XML格式 - 将其作为“XML”列 - 这样你就可以避免很多麻烦和问题... - marc_s

有没有什么办法可以绕过这个问题。很难解释，但是现在我无法更改列类型。我已经让选择操作起作用了，但就是无法让这个更新操作起作用。谢谢。 - the sandman

Answer 1

可能有合理的理由将XML存储在[n]varchar(max)中。如果你只想存储XML，那么完全没问题，但是如果你想使用TSQL修改XML的某些部分，或者需要查询XML的值或在where子句中使用节点/属性值，则应切换到XML，这样可以从数据上获得索引并跳过类型转换。由于text已被弃用，因此您至少应考虑将数据类型切换为[n]varchar(max)。

如果您的数据位于XML列中，则可以使用XML DML来修改XML。在您的情况下，您将使用replace value of，如下所示。

update tbl_Module_RequestForms_Items
set XMLData.modify('replace value of (/Record/Submitted/text())[1] with "True"')
where XMLData.value('(/Record/Submitted)[1]', 'bit') = 0

如果没有XML数据类型，这是不可能的，因此您必须提取整个XML文档，修改它，然后使用修改后的XML文档更新表。

当然，您可以使用某种客户端开发工具来完成这项任务，但在TSQL中也可以实现。

1. 声明一个带有tbl_Module_RequestForms_Items的主键和XMLData列的表变量，但数据类型为XML。
2. 将行从tbl_Module_RequestForms_Items复制到应更新的表变量中。
3. 使用replace value of更新XML。
4. 将更改应用回tbl_Module_RequestForms_Items。

假设ID是tbl_Module_RequestForms_Items的主键，而您的XML数据位于XMLData列中，类似于以下内容：

declare @T table
(
  ID int primary key,
  XMLData xml
)

insert into @T 
select M.ID,
       M.XMLData
from tbl_Module_RequestForms_Items as M
where cast(cast(XMLData as nvarchar(max)) as xml).value('(/Record/Submitted)[1]', 'bit') = 0

update @T
set XMLData.modify('replace value of (/Record/Submitted/text())[1] with "True"')

update M
set XMLData = cast(T.XMLData as nvarchar(max))
from tbl_Module_RequestForms_Items as M
  inner join @T as T
    on M.ID = T.ID

- Mikael Eriksson