问题描述
我现在正在为此苦苦挣扎:如何替换文本等于某个变量值的无类型 XML 列中的节点值?可能吗?
I am struggling with this now: How do you replace the value of a node in an untyped XML column where the text is equal to a certain variable value? Is it possible?
我的 XML:
<attrs> <attr>ManualInsert</attr> <attr>ManualInsert2</attr> <attr>ManualInsert4</attr> <attr>ManualInsert8</attr> </attrs>
我的尝试:
DECLARE @OldValue Varchar(255) = 'ManualInsert' DECLARE @NewValue Varchar(255) = 'ReplacedValue' UPDATE Labels SET Attributes.modify('replace value of (/attrs/attr/text())[1] with if ((/attrs/attr/text() = sql:variable("@OldValue"))) then sql:variable("@NewValue") else () ') WHERE Id = 2000046
消息:(0 行受影响)
DECLARE @OldValue Varchar(255) = 'ManualInsert' DECLARE @NewValue Varchar(255) = 'ReplacedValue' UPDATE Labels SET Attributes.modify('replace value of (/attrs/attr[text() = sql:variable("@OldValue")])[1] with sql:variable("@NewValue")') WHERE Id = 2000046
留言:
Msg 2356, Level 16, State 1, Line 7 XQuery [Labels.Attributes.modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element(attr,xdt:untyped) ?'
预期结果:
<attrs> <attr>ReplacedValue</attr> <attr>ManualInsert2</attr> <attr>ManualInsert4</attr> <attr>ManualInsert8</attr> </attrs>
推荐答案
modify('replace value of (/attrs/attr[. = sql:variable("@OldValue")]/text())[1] with sql:variable("@NewValue")')
您的第二次尝试实际上只是缺少指定应该替换的是 text() .这也可以.
Your second attempt is actually just missing to specify that it is the text() that should be replaced. This will also work.
modify('replace value of (/attrs/attr[text() = sql:variable("@OldValue")]/text())[1] with sql:variable("@NewValue")')