sql-server - 在 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>

Answer 1

可能有正当理由将 XML 存储在 [n]varchar(max) 中。如果你只想存储 XML，那完全没问题，但如果你想使用 TSQL 修改部分 XML，或者如果你需要查询 XML 的值或在 where 子句中使用节点/属性值，你应该切换到 XML where您可以从数据索引中受益并跳过类型转换。由于 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