Dynamically replacing the value of a node in XML DML

Question

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?

My XML:

<attrs>
  <attr>ManualInsert</attr>
  <attr>ManualInsert2</attr>
  <attr>ManualInsert4</attr>
  <attr>ManualInsert8</attr>
</attrs>

My Tries:

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

message: (0 row(s) affected)

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

message:

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) ?'

expected result:

<attrs>
  <attr>ReplacedValue</attr>
  <attr>ManualInsert2</attr>
  <attr>ManualInsert4</attr>
  <attr>ManualInsert8</attr>
</attrs>

Aparri

Answer 1

modify('replace value of (/attrs/attr[. = sql:variable("@OldValue")]/text())[1]
        with sql:variable("@NewValue")')

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")')

Quiescent

From here and from here :). – Quiescent

Suicide time. How did i miss that? ^_^ I was on one of those pages. Thanks – Aparri

Is this supported? (check for my index). i.e. none of the code examples actually not supporting replacing the index but other params. modify('replace value of (/attrs/attr[text() = sql:variable("@OldValue")]/text())[sql:variable("@myindex")] with sql:variable("@NewValue")') – Brazil

@Brazil You should ask that as a new question to get a proper answer. Include some test XML data and the expected end result. That said, I think it will not work for you. Instead you probably want something like this modify('replace value of (/attrs/attr[text() = sql:variable("@OldValue") and position() = sql:variable("@myindex")]/text())[1] with sql:variable("@NewValue")') – Quiescent

Thanks @MikaelEriksson . I asked it over here - #21412095 – Brazil