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.

canon · Accepted Answer · 2011-09-27 18:16:38Z

3

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

edited Sep 27, 2011 at 18:16

answered Sep 27, 2011 at 18:03

canon

41.9k10 gold badges77 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related