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>

Mikael Eriksson · Accepted Answer · 2013-03-15 15:05:45Z

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

answered Mar 15, 2013 at 15:05

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mikael Eriksson Over a year ago

From here and from here :).

Eon Over a year ago

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

sarat Over a year ago

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

Mikael Eriksson Over a year ago

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

sarat Over a year ago

Thanks @MikaelEriksson . I asked it over here - stackoverflow.com/questions/21411595/…

Collectives™ on Stack Overflow

Dynamically replacing the value of a node in XML DML

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related