How to replace attribute value of *all* matching elements with XQuery?

Question

Hi, I'm trying without luck to create a modify() statement to change the value of an attribute in all elements that have that attribute value -- so far I can only get it to change the value in the first matched element. I created an example below of what I have so far, which I'm running in SQL Server 2005:

DECLARE @x XML
SELECT @x = '
<FootballApparel>
  <Item Team="Phoenix Cardinals" Type="Hat" Cost="$14.99" />
  <Item Team="Indianapolis Colts" Type="Hat" Cost="$14.99" />
  <Item Team="Cincinnati Bengals" Type="Hat" Cost="$14.99" />
  <Item Team="Phoenix Cardinals" Type="Shirt" Cost="$21.99" />
  <Item Team="Indianapolis Colts" Type="Shirt" Cost="$21.99" />
  <Item Team="Cincinnati Bengals" Type="Shirt" Cost="$21.99" />
</FootballApparel>
';

SET @x.modify('
  replace value of
    (/FootballApparel/Item[@Team="Phoenix Cardinals"]/@Team)[1]
  with "Arizona Cardinals"
');

SELECT @x;

Running this gives the results below -- only the first instance of Phoenix Cardinals has been changed.

<FootballApparel>
  <Item Team="Arizona Cardinals" Type="Hat" Cost="$14.99" />
  <Item Team="Indianapolis Colts" Type="Hat" Cost="$14.99" />
  <Item Team="Cincinnati Bengals" Type="Hat" Cost="$14.99" />
  <Item Team="Phoenix Cardinals" Type="Shirt" Cost="$21.99" />
  <Item Team="Indianapolis Colts" Type="Shirt" Cost="$21.99" />
  <Item Team="Cincinnati Bengals" Type="Shirt" Cost="$21.99" />
</FootballApparel>

Can you please help me with the correct modify() statement to replace all instances?

Thanks!
Kevin

Answer 1

You're very close - what you need to do is loop (and there's no other way I know of to do it in this case) and repeatedly replace the values:

WHILE @x.exist('(/FootballApparel/Item[@Team=sql:variable("@oldTeamName")])[1]') = 1
SET @x.modify('
  replace value of (
 /FootballApparel/Item[@Team=sql:variable("@oldTeamName")]/@Team
  )[1]
  with sql:variable("@newTeamName")
');

That should do the trick.

Marc