CODEWITHSUNDEEP
sql-serverxmlt-sql
DisposableCoder
DisposableCoder

Reputation: 33

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

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.

Upvotes: 3

Views: 3322

Answers (1)

canon
canon

Reputation: 41675

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...

Upvotes: 3

Related Questions