5

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>
Eon
  • 3,833
  • 10
  • 46
  • 75

1 Answers1

10
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")')
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 3
    From [here](http://msdn.microsoft.com/en-us/library/ms190675.aspx) and from [here](http://en.wikipedia.org/wiki/Trial_and_error) :). – Mikael Eriksson Mar 15 '13 at 15:12
  • Suicide time. How did i miss that? ^_^ I was on one of those pages. Thanks – Eon Mar 15 '13 at 15:20
  • 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")')` – sarat Jan 28 '14 at 13:58
  • @sarat 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")')` – Mikael Eriksson Jan 28 '14 at 14:31
  • Thanks @MikaelEriksson . I asked it over here - http://stackoverflow.com/questions/21411595/how-to-dynamically-change-the-index-value-with-sql-dml – sarat Jan 28 '14 at 16:43