title: Conditionally delete XML elements in XML field in SQL Server using T-SQL
body: <p>I would like to conditionally remove elements in an XML field on SQL Server 2005.  For example, I have the following data:</p>
  
  <pre><code>&lt;stats&gt;
      &lt;stat date="2011-09-25 00:00:00" val="1"/&gt;
      &lt;stat date="2011-09-26 00:00:00" val="5"/&gt;
      &lt;stat date="2011-09-27 00:00:00" val="3"/&gt;
  &lt;/stats&gt;
  </code></pre>
  
  <p>Using T-SQL, how can I remove the stat elements that have a date value older than, say, one day?</p>
  
  <p>Theoretically, I guess the ideal solution can be expressed as:</p>
  
  <pre><code>UPDATE XML_TEST
      SET XML_DATA_FIELD.modify('delete (/stats/stat)')
      WHERE XML_DATA_FIELD.nodes('/stats/stat').value('@date',
      'datetime') &lt; DATEADD(day, -1, GETDATE())
  </code></pre>
  
  <p>Which of course, does not work.</p>