日期:2014-05-17  浏览次数:20502 次

如何替换xml类型字段的多个属性值

SQL code
 
DECLARE @myDoc xml
SET @myDoc = ' <Root>
<Location LocationID="10"
            LaborHours="1.1"
            MachineHours=".2" >Manufacturing steps are described here.
<step>Manufacturing step 1 at this work center </step>
<step>Manufacturing step 2 at this work center </step>
</Location>
</Root>'
SELECT @myDoc
-- update attribute value
SET @myDoc.modify('
  replace value of (/Root/Location/@LaborHours)[1]
  with    "100.0"
')
SELECT @myDoc



查MSDN知道,replace value of ...可以用来替换xml类型的值,
示例也是摘抄MSDN的,如果要在一条sql里完成对LocationID,LaborHours,MachineHours属性的修改,应该怎么做?

我说过写多条 replace value of (...) with "" 不行。

------解决方案--------------------
DECLARE @myDoc xml
SET @myDoc = '<Root>
<Location LocationID="10" 
LaborHours="1.1"
MachineHours=".2" >Manufacturing steps are described here.
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>
</Location>
</Root>'
SELECT @myDoc
-- update attribute value
SET @myDoc.modify('
replace value of (/Root/Location/@LaborHours)[1]
with "100.0"
')
SET @myDoc.modify('
replace value of (/Root/Location/@MachineHours)[1]
with "100.0"
')

SELECT @myDoc