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

Question

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

marc_s · Accepted Answer · 2009-10-14 14:48:43Z

5

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

answered Oct 14, 2009 at 14:48

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Radhi Over a year ago

Hi Marc, i have to update multiple nodes in database. so is it possible using one xml statement? or i need to write statement you have explained above for all nodes?

marc_s Over a year ago

@Radhi: can you select all those nodes in the XML in a single XPath statement? If yes, you can update those in a single operation; otherwise, you need multiple "modify" operations

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related