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

+2


source to share


1 answer


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"
    ')

      

+2


source







All Articles