SQL Server XML 替换属性中的值 [英] SQL Server XML Replace values in attribute

问题描述

我想替换 XML 中属性中的值.此属性在 xml 中多次出现.我怎样才能一次全部更换这些

I want to replace the value in an attribute in a XML. This attribute comes multiple times in the xml. How can i replace these all at once

我的 xml 看起来像下面这样:

my xml lools some what like below :

 <Example>
  <A>
   <B Type = "x">qqq</B>
   <B Type = "x">www</B>
  </A>
  <C>
   <D Type = "x">aaa</D>
   <D Type = "x">uuu</D>
  </C>
 </Example>

我想用 y 替换所有 x

I want to replace all x with y

推荐答案

您不能使用 替换(XML DML)的值.你必须循环执行.

You can not replace all at once using replace value of (XML DML). You have to do it in a loop.

declare @xml xml = '
<Example>
  <A>
   <B Type = "x">qqq</B>
   <B Type = "x">www</B>
  </A>
  <C>
   <D Type = "x">aaa</D>
   <D Type = "x">uuu</D>
  </C>
 </Example>
'

while (select @xml.exist('//*[@Type = "x"]')) = 1
begin
  set @xml.modify('replace value of (//*[@Type = "x"]/@Type)[1] with "y"')
end

select @xml

结果:

<Example>
  <A>
    <B Type="y">qqq</B>
    <B Type="y">www</B>
  </A>
  <C>
    <D Type="y">aaa</D>
    <D Type="y">uuu</D>
  </C>
</Example>

更新

用 y 替换 x 和 z 值:

Replace values x and z with y:

while (select @xml.exist('//*[@Type = ("x","z")]')) = 1
begin
  set @xml.modify('replace value of (//*[@Type = ("x","z")]/@Type)[1] with "y"')
end

用 y 替换所有值:

while (select @xml.exist('//*[@Type != "y"]')) = 1
begin
  set @xml.modify('replace value of (//*[@Type != "y"]/@Type)[1] with "y"')
end

这篇关于SQL Server XML 替换属性中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！