title: How do I delete a particular node of an XML column in sql server 2008 R2 whose sub node matches a given criteria
body: <pre><code>&lt;VirAddresses&gt;
      &lt;VirAddress&gt;
          &lt;VirAddKey&gt;0&lt;/VirAddKey&gt;
          &lt;VirAddressLine&gt;abc@gmail.com&lt;/VirAddressLine&gt;
          &lt;Bounced&gt;false&lt;/Bounced&gt;
          &lt;Functions&gt;
              &lt;Function&gt;E-mail&lt;/Function&gt;
          &lt;/Functions&gt;
      &lt;/VirAddress&gt;
      &lt;VirAddress&gt;
          &lt;VirAddKey&gt;1&lt;/VirAddKey&gt;
          &lt;VirAddressLine&gt;0123456789&lt;/VirAddressLine&gt;
          &lt;Bounced&gt;false&lt;/Bounced&gt;
          &lt;Functions&gt;
              &lt;Function&gt;Mobile&lt;/Function&gt;
          &lt;/Functions&gt;
      &lt;/VirAddress&gt;
  &lt;/VirAddresses&gt;
  </code></pre>
  
  <p>My end goal is to modify the XML column, <code>IF the Function=E-mail then</code> need to delete this particular VirAddress node among multiple VirAddress nodes
   under VirAddresses.<br>
  The position of VirAddress <code>(where Function=E-mail)</code> is not fixed in XML, like above sample having VirAddress <code>(where Function=E-mail)</code> at first 
  position but it may vary in other xml.</p>
  
  <p>I have tried query like this :</p>
  
  <pre><code>UPDATE MyTable  
  SET xmlCol.modify('delete(/VirAddresses/VirAddress/Functions[Function="E-mail"]/../../VirAddress)') 
  where ID in (347,349) and 
  xmlCol.exist('/VirAddresses/VirAddress/Functions[Function="E-mail"]') = 1
  </code></pre>
  
  <p>But above query removed all VirAddress nodes under VirAddresses node.</p>
  
  <p>I  have been at this for several hours to no avail. Help is appreciated. </p>
  
  <p>Thanks.</p>