Conditional XML Modify in SQL Server

Question

Is it possible to use XML.modify within a case statement like this or would this have to be 2 separate update statements?

UPDATE s
SET     
   CASE WHEN [XMLData] IS NOT NULL THEN [XMLData].modify('delete //invoice/account/contactinformation') END,
   CASE WHEN [SummaryXMLData] IS NOT NULL THEN [SummaryXMLData].modify('delete //invoice/account/contactinformation') END 
FROM   
    ITS_CSC.[Statement].[StatementSummary] s    

SQL Server throws this error:

Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'CASE'.

NB: Calling the modify method directly on a NULL value fails with the following message hence need to check for NULLs

Mutator 'modify()' on 'column name' cannot be called on a null value.

Answer 1

It is incorrect syntax. You could use 2 separate statements instead:

UPDATE ITS_CSC.[Statement].[StatementSummary]
SET  [XMLData].modify('delete //invoice/account/contactinformation') 
WHERE [XMLData] IS NOT NULL;

UPDATE ITS_CSC.[Statement].[StatementSummary]
SET [SummaryXMLData].modify('delete //invoice/account/contactinformation')
WHERE [SummaryXMLData] IS NOT NULL;