如何在 MS SQL 2005 中使用 SQL XQuery 修改多个节点 [英] How to modify multiple nodes using SQL XQuery in MS SQL 2005
问题描述
在一张表中,我有以下内容.
In an table I have the following.
ParameterID (int) ParameterValue (XML)
------------ --------------
1 <USER><User ID="1" Name="Billy"/><USER>
<USER><User ID="2" Name="Billy"/><USER>
<MANAGER><User ID="1" Name="Billy"/><MANAGER>
2 <USER><User ID="1" Name="John"/><USER>
<USER><User ID="2" Name="Billy"/><USER>
<MANAGER><User ID="1" Name="Billy"/><MANAGER>
3 <USER><User ID="1" Name="David"/><USER>
<USER><User ID="2" Name="Billy"/><USER>
<MANAGER><User ID="1" Name="Billy"/><MANAGER>
如何将 Billy 的所有实例修改为 Peter?
How do I modify all instances of Billy to be Peter?
我试过了
-- Update the table
UPDATE @tbXML
SET ParameterValue.modify('replace value of (//User/@Name[.="Billy"])[1] with "Peter"')
但只有行中的第一个 Billy 被更新.
But only the first Billy in the row was updated.
多次运行更新:
For Row 1:
1st Time = 1 <USER><User ID="1" Name="Peter"/><USER> <USER><User ID="2" Name="Billy"/><USER> <MANAGER><User ID="1" Name="Billy"/><MANAGER>
2nd Time 1 <USER><User ID="1" Name="Peter"/><USER> <USER><User ID="2" Name="Peter"/><USER> <MANAGER><User ID="1" Name="Billy"/><MANAGER>
3rd Time 1 <USER><User ID="1" Name="Peter"/><USER> <USER><User ID="2" Name="Peter"/><USER> <MANAGER><User ID="1" Name="Peter"/><MANAGER>
推荐答案
简单"的方法是将 XML 列转换为 VARCHAR(MAX) 并简单地对其进行 REPLACE:
The "simple" approach would be to cast the XML column to VARCHAR(MAX) and just simply do a REPLACE on it:
UPDATE
YourTable
SET
ParameterValue = CAST(REPLACE(CAST(ParameterValue AS VARCHAR(MAX)), '
Billy', 'Peter') AS XML)
WHERE
....
就像 Richard Szalay 解释的那样,您几乎无法在单个 UPDATE 语句中更新多个 XML 节点值这里:
It almost seems like you cannot do an update of multiple XML node values in a single UPDATE statement, as Richard Szalay explains here:
不幸的是,该实现似乎受到了极大的限制,因为它无法在一次更新中对同一值进行任意数量的修改.
所以我猜你要么必须使用上面提到的愚蠢"的 VARCHAR(MAX) 方法,要么在循环中进行更新(当你仍然找到一个带有billy"的节点时,更新该节点以读取彼得"代替).
So I guess you'll either have to use the "dumb" VARCHAR(MAX) approach mentioned above, or do the update in a loop (WHILE you still find a node with "billy", UPDATE that node to read "Peter" instead).
马克
这篇关于如何在 MS SQL 2005 中使用 SQL XQuery 修改多个节点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!