問題描述

我現在正在為此苦苦掙扎:如何替換文本等于某個變量值的無類型 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")')

這篇關于動態替換 XML DML 中節點的值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！