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.

Lukasz Szozda · Accepted Answer · 2018-12-27 16:45:14Z

3

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;

edited Dec 27, 2018 at 16:45

answered Dec 27, 2018 at 15:43

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

Conditional XML Modify in SQL Server

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