How to delete all comments from XML using XQuery on SQL Server 2005?

Question

I can easily select all comments from XML by

select @XML.query('comment()')

but can't find how to delete them. Is there appropriate syntax for @XML.modify('delete ...')?

Answer 1

set @XML.modify('delete comment()')
select @XML

Here it is.

Answer 2

Maybe this article provides you the solution Improving XML Update Performance with SQL Server 2005

UPDATE Store SET Demographics.modify('delete //newnode') WHERE CustomerID = @custid