Question

在表一栏中,我有XML的例子:

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

我如何把A node的价值改为:

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

我尝试了以下解决办法:

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

但它没有发挥作用......

Answer 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

友情链接