CODEWITHSUNDEEP
sqlsql-serverxmlsql-update
Richard
Richard

Reputation: 3367

Delete node from XML column with where

I have a xml column in my sql table which contains something like this:

<date>
  <entry action="Log in" insertedTime="2014-01-05T15:05:18.4476032+02:00" />
  <entry action="Log in" insertedTime="2014-01-10T16:02:10.8974142+02:00" />
  <entry action="Log out" insertedTime="2014-01-11T16:44:54.8380633+02:00" />
</date>

I am trying to clean up this column (I have way more than 3 entries, 100s) and all I want to do is retain the last 2 days of data, i.e. delete anything older than the (max date) - 2 days.

I can get the max inserted time and put that into a variable, but of course that is the max insert time for all the rows in the table, I want to do this on a "per row" basis. I.e. delete the last 2 days of date from each row. The max date value for each row might be different.

The delete I'm using is (currently using a temp table for testing):

update #xmltemp
set data.modify('delete //entry[@insertedTime < sql:variable("@maxDate")]')

Is there any way of doing this?

EDIT: I've got it running with a cursor now, but is there another way of doing this?

Thanks

Upvotes: 0

Views: 184

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Figure out the max datetime value for each row in the table in a cross apply and use sql:column in the delete predicate.

update X 
set data.modify('delete /date/entry[@insertedTime < sql:column("M.I")]')
from xmltemp as X
  cross apply (
              select dateadd(day, -2, max(E.X.value('@insertedTime', 'datetime')))
              from X.data.nodes('/date/entry') as E(X)
              ) as M(I)

SQL Fiddle

Upvotes: 1

Related Questions