Conditionally delete XML elements in XML field in SQL Server using T-SQL

Question

I would like to conditionally remove elements in an XML field on SQL Server 2005. For example, I have the following data:

<stats>
    <stat date="2011-09-25 00:00:00" val="1"/>
    <stat date="2011-09-26 00:00:00" val="5"/>
    <stat date="2011-09-27 00:00:00" val="3"/>
</stats>

Using T-SQL, how can I remove the stat elements that have a date value older than, say, one day?

Theoretically, I guess the ideal solution can be expressed as:

UPDATE XML_TEST
    SET XML_DATA_FIELD.modify('delete (/stats/stat)')
    WHERE XML_DATA_FIELD.nodes('/stats/stat').value('@date',
    'datetime') < DATEADD(day, -1, GETDATE())

Which of course, does not work.

Answer 1

This should achieve what you're looking for:

declare @testXml xml = '<stats>
    <stat date="2011-09-25 00:00:00" val="1"/>
    <stat date="2011-09-26 00:00:00" val="5"/>
    <stat date="2011-09-27 00:00:00" val="3"/>
</stats>'
,@yesterday varchar(20) = convert(varchar, dateadd(day, -1, getdate()), 120)

set @testXml.modify('delete //stat[@date < sql:variable("@yesterday")]')

Ew, string comparison...