How can I change the consistent text of an element using XQuery?
I'm trying to collect all the examples I've seen for finding items and modifying them, but I haven't come up with something that works yet. I have created an example below what I have so far which I am running in SQL Server 2005. I am trying to change 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 happens is ItemID 3 becomes changed to 999 instead of 4 as shown in the results below. I think what is happening is that "/ ItemGroup [ItemID =" 4 "] defines the correct <ItemGroup> and then the position filter [1] provides the first <ItemID> in that <ItemGroup> (which is ID 3) - I just didn't find 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 that this is something simple that my googling and stackoverflowing hasn't come up yet. I appreciate your help! Kevin
source to share