SQL中更新XML列指定节点值的问题求助

需求：通过SQL语句将XML列中<row nota="49">deadline details</row>的文本内容修改为“Deadline Balance”，XML列结构如下：

<rows>
   <row nota="2">Contratto depositato [...] :-) </row>
   <row nota="3">
       Gli è stata concessa una dilazione.
       Porre attenzione alle condizioni.
    </row>
    <row nota="49">deadline details</row>
</rows>

尝试执行的SQL语句：

UPDATE CF 
SET NoteXML.modify('replace value of (/rows/row[@nota="49"]/text())[1] with "deadline detail"')
WHERE Cd_CF = 'C000001'

出现报错：

XQuery [CF.NoteXML.modify()]: The operator could not be applied "=" to the operands "xs:unsignedInt" and "xs:string".

报错原因 #

XML解析器自动将nota属性的数值49识别为xs:unsignedInt类型，但SQL语句中用字符串"49"进行比较，导致类型不匹配触发报错。

解决方法 #

两种方式可修正类型匹配问题：

1. 将字符串"49"转换为无符号整数类型后比较

UPDATE CF 
SET NoteXML.modify('replace value of (/rows/row[@nota=xs:unsignedInt("49")]/text())[1] with "Deadline Balance"')
WHERE Cd_CF = 'C000001'

2. 将nota属性值转换为字符串类型后比较

UPDATE CF 
SET NoteXML.modify('replace value of (/rows/row[string(@nota)="49"]/text())[1] with "Deadline Balance"')
WHERE Cd_CF = 'C000001'

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