sql server xml delete two attributes at the same time

Question

I found the following which shows how to delete an attribute from XML in SQL Server: How to delete an attribute from an XML variable in sql server 2008?

But I would like to delete two attributes at once. I tried something like the following:

UPDATE TABLE SET
  xmlField.modify('delete  (/clue_personal_auto/@attr1)[1]'),
  xmlField.modify('delete  (/clue_personal_auto/@attr2)[1]')
 WHERE compare = 357

But I get an error that the same column cannot be modified more than once during an update. Is there a way to delete both within once statement, or should I just run two separate updates?

If you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Jun 19 '12 at 15:04

score 3 · Accepted Answer · edited Jan 18 '21 at 12:30

3

UPDATE TABLE SET
  xmlField.modify('delete (/clue_personal_auto/@*[local-name()=("attr1", "attr2")])')
 WHERE compare = 357

SE-Data

edited Jan 18 '21 at 12:30

Community

1
1

answered Jun 19 '12 at 15:20

Mikael Eriksson

136,425
22
210
281

sql server xml delete two attributes at the same time

1 Answers1