xml-sql:更新多个节点 [英] xml-sql: update multiple nodes

问题描述

我有以下问题.我有一个 xml 文件存储在 sql 数据库中.我应该通过除以 100 来更改所有 VALUE 标记值.这是 xml 的结构的摘录:

I have the following problem. i have a xml file stored in a sql database. i should change all the VALUE tag values by dividing per 100. here is an extract the structure of the xml:

<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201110180000</HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201110250000 </HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201111020000 </HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201111080000 </HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>20 </VALUE>
  <HEIGHTC_DATE>201111150000 </HEIGHTC_DATE>
</HEIGHTC>
<HEIGHTC>
  <VALUE>15 </VALUE>
  <HEIGHTC_DATE>201111290000 </HEIGHTC_DATE>
</HEIGHTC>

我发现了以下查询:

DECLARE @var varchar(50)
set @var='HEIGHTC'
UPDATE tcdc.dbo.BADM_Xml
SET  xml_badm.modify('replace value of (/ROOT/*[local-name()=sql:variable("@var")]/VALUE/text())[1] with (/ROOT/*[local-name()=sql:variable("@var")]/VALUE)[1] * 0.01')

并且它一次对单个节点工作正常:有没有办法在单个指令中概括和更新所有内容?提前谢谢迭戈

and it works fine for a single node at a time: is there a way to generalize and update all in a single instruction? thanx in advance diego

推荐答案

replace value of一次只能更新一个节点.

replace value of can only update one node at a time.

找出所有要更新的 XML 中使用的最大节点数，并使用更新语句中的循环变量一次修改一个节点.

Find the max number of nodes used in all the XML's you want to update and use the loop variable in the update statement to modify one node at a time.

where 子句检查是否存在要修改的节点.否则，您将在每次迭代时修改表中的每一行.

The where clause checks for the existence if nodes to modify. Without that you would modify every row in the table for each iteration.

declare @I int

select @I = max(xml_badm.value('count(/ROOT/HEIGHTC/VALUE)', 'int'))
from YourTable

while @I > 0 
begin
  update YourTable
  set xml_badm.modify
    ('replace value of ((/ROOT/HEIGHTC/VALUE)[sql:variable("@I")]/text())[1]
      with ((/ROOT/HEIGHTC/VALUE)[sql:variable("@I")]/text())[1] * 0.01')
  where xml_badm.exist('(/ROOT/HEIGHTC/VALUE)[sql:variable("@I")]') = 1
  set @I = @I - 1
end

这篇关于xml-sql:更新多个节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！