Changing xml node value in SQL with xquery [SQL Server]

Question

I have example XML in one of the table column:

<Root>
   <A>
      <C>c</C>
      <D>d</D>
   </A>
   <B>b</B>
</Root>

How can I replace value of A node into something like that:

<Root>
   <A>
      <E>e</E>
      <F>f</F>
   </A>
   <B>b</B>
</Root>

I tried the following solution

DECLARE @var varchar(100);
SET @var = '<E>e</E><F>f</F>'
SET @xml.modify('replace value of (/Root/A/text())[1] with sql:variable("@var")');

but it did not work...

regards

If this is SQL-Server, then it's not an XQuery Update question but XML Data Modification Language (XML DML) — user357812
– user357812, Commented Mar 14, 2011 at 13:47

Mikael Eriksson · Accepted Answer · 2011-03-14 15:05:23Z

1

I do not think it is possible to use replace to replace nodes, only values. You can use delete and then insert instead.

declare @xml as xml = '
<Root>
  <A>
    <C>c</C>
    <D>d</D>
  </A>
  <B>b</B>
</Root>'

declare @var xml
set @var = '<E>e</E><F>f</F>'

set @xml.modify('delete /Root/A/*')
set @xml.modify('insert sql:variable("@var") into (/Root/A)[1]')

select @xml

answered Mar 14, 2011 at 15:05

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Collectives™ on Stack Overflow

Changing xml node value in SQL with xquery [SQL Server]

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related