SQL Server 2008 R2 table update XML column modify ignoring where clause

I am trying to update an element that is part of a complex type in an XML string. The where clause is being ignored and the first element of the string is the only element that is upated.

The XML schema collection (simplified form):

<element name="rangeDef">
   <complexType>
        <sequence>
            <element maxOccurs="unbounded" name="defs">
               <complexType>
                     <sequence>
                         <element name="ID" type="string"/>
                         <element name="available" type="string"/>
                     </sequence>
               </complexType>
            </element>
         </sequence>
    </complexType>
</element>

populate开发者_高级运维 the string is:

<rangeDef>
   <defs>
      <ID>AA</ID>
      <available>Y</available>
   </defs>
   <defs>
      <ID>AB</ID>
      <available>Y</available>
   </defs>
   <defs>
      <ID>AC</ID>
      <available>Y</available>
   </defs>
   <defs>
      <ID>AD</ID>
      <available>Y</available>
   </defs>
   <defs>
      <ID>AE</ID>
      <available>Y</available>
   </defs>
</rangeDef>

this is save in a table --> testDef (key char(10), rangeDef xml)

the update statement is:

 update testDef
 set rangeDef.modify('replace value of (//defs/available)[1] with "N"')
 where rangeDef.exist ('//defs[ID = sql:variable("@myValue")] = 1 and
 key = @myKey

@myValue is declare as a char(2) and set to 'AD' @myKey is simple search key.

With the modify syntax of (//...)[1] a single node is selected with that node being the first in the XML string and the where clause is ignored. Not sure what corrections are needed in the syntax such the the where rangeDef.exist will seek to the proper element of the XML string. My thinking is that I should use the position() function instead of indication [1], but I am not clear as to how that is implemented.

The where clause filters the rows you want to update. You need to specify the node to be updated in the replace value of ....

update testDef set 
  rangeDef.modify('replace value of (//defs[ID = sql:variable("@myValue")]/available/text())[1] 
                   with "N"')
where key = @myKey

I figured out the issue with the update statement. The where clause must be part of the modify statement:

update testDef set rangeDef.modify('replace value of (//defs[ID = sql:variable("@myValue")]/available)[1] with "N"') where key = @myKey