如何在MSSQL中更新XML空节点？现有代码失效求解决

问题原因 #

你编写的SQL代码无法生效，核心原因是空的<DefaultValue/>节点不存在文本子节点，而你的modify('replace value of ... /DefaultValue/text())[1] ...')试图匹配文本节点，路径找不到目标，因此更新操作不会执行。

解决方案 #

针对存在<DefaultValue/>空节点且对应DataType=1的<Field>节点，我们需要向空节点插入文本内容，而非替换不存在的文本节点。以下是可行的SQL代码：

DECLARE @SearchType NVARCHAR(100) = N'1';
DECLARE @ReplaceWith NVARCHAR(100) = N'TODAY()';

UPDATE FormSchema
SET Fields.modify('insert text{sql:variable("@ReplaceWith")} into 
                     (/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue)[1]')
WHERE Fields.exist('/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue[not(text())]') = 1;

代码说明 #

1. XPath条件：/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue[not(text())] 精准匹配：
   • 对应DataType等于指定值的<Field>节点
   • 该节点下的<DefaultValue>为空（无文本内容）
2. modify操作：insert text{...} into ... 直接向空的<DefaultValue>节点插入文本内容，将<DefaultValue/>转换为<DefaultValue>TODAY()</DefaultValue>

额外处理：针对xsi:nil="true"的节点 #

如果存在<DefaultValue xsi:nil="true"/>这类标注为nil的节点，需要先移除nil属性，再插入文本：

-- 先移除xsi:nil属性
UPDATE FormSchema
SET Fields.modify('delete (/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue/@xsi:nil)[1]')
WHERE Fields.exist('/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue/@xsi:nil') = 1;

-- 再插入文本
UPDATE FormSchema
SET Fields.modify('insert text{sql:variable("@ReplaceWith")} into 
                     (/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue)[1]')
WHERE Fields.exist('/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue[not(text())]') = 1;

内容的提问来源于stack exchange，提问作者tfs