How to change matched element text with XQuery?

Question

Hi, I'm trying to piece together all the examples I've seen for finding elements and modifying them, but I haven't come up with something that works yet. I created an example below of what I have so far, which I'm running in SQL Server 2005. I'm trying to change the ItemID 4 to 999:

DECLARE @x XML
SELECT @x = '
<ValueCollection>
  <ItemGroup Name="Group A">
    <ItemID>1</ItemID>
    <ItemID>2</ItemID>
  </ItemGroup>
  <ItemGroup Name="Group B">
    <ItemID>3</ItemID>
    <ItemID>4</ItemID>
  </ItemGroup>
</ValueCollection>
';

SET @x.modify ('
replace value of
    (/ValueCollection/ItemGroup[ItemID="4"]/ItemID/text())[1]
with "999"
')

SELECT @x;

But what's happening is ItemID 3 is getting changed to 999 instead of 4, as shown in the results below. I think what is happening is the "/ItemGroup[ItemID="4"] is locating the correct <ItemGroup>, and then the position filter [1] is giving the first <ItemID> in that <ItemGroup> (which is ID 3) -- I just haven't found out how to change the position filter to a variable.

<ValueCollection>
  <ItemGroup Name="Group A">
    <ItemID>1</ItemID>
    <ItemID>2</ItemID>
  </ItemGroup>
  <ItemGroup Name="Group B">
    <ItemID>999</ItemID>
    <ItemID>4</ItemID>
  </ItemGroup>
</ValueCollection>

I have a feeling it is something simple that my googling and stackoverflowing hasn't turned up yet. I appreciate your help!
Kevin

Answer 1

SET @x.modify ('replace value of
    (/ValueCollection/ItemGroup/ItemID[text()=4]/text())[1]
    with "999"
    ')

or if you want to be more precise:

SET @x.modify ('replace value of
    (/ValueCollection/ItemGroup[@Name="Group B"]/ItemID[text()=4]/text())[1]
     with "999"
    ')