Replacing multiple attributes with transact-sql replace - XML

Question

If you have this xml

<AssetComponent name="AssetComponent4881" width="300">
  <Asset id="5" type="1" />
</AssetComponent>
<AssetComponent name="AssetComponent4882" width="300">
  <Asset id="5" type="1" />
</AssetComponent>

Is it possible to replace all the ids from 5 to 6 in one query?

With this sql only one attribute at the time can be replaced:

SET @myDoc.modify('
  replace value of (//Asset/@id)[1]
  with     sql:variable("@BinaryAssetId")
')

From the sql docs I can see that the replace should return a atomic node, just one, so is there another way to do this?

Answer 1

The last time I looked into this, there was no such way - replace only operates on a single item at a time. The (unpleasant) solution I came up with at the time was (pseudo code) this:

Suppose we want to change records from one state to another; let us call those states the 'from' state (eg having Asset/@id = '5') and the 'to' state (eg having Asset/@id = '6'). Then execute this loop:

WHILE there exists a record that is in the 'from' state
    BEGIN
    Execute a .modify that changes the first record in the 'from' state 
        to being in the 'to' state
    END

It's not pretty, but it works.

Answer 2

While(@Maxcount <> 0)
BEGIN
        SET @myDoc.modify(' 
          replace value of (//Asset/@id)[sql:variable("@Maxcount")] 
          with     sql:variable("@BinaryAssetId") 
        ') 
        SET @MaxCount = @MaxCount - 1
END