Delete multiple XmlData sibling nodes with .remove()

Question

This question is along the lines of Deleting Multiple Nodes in Single XQuery for SQL Server. The difference is that I want to indiscriminately remove all nodes within the document.

XML:

<root>      
    <Attachment id="Holding_1_attachment_0">
      <AttachmentData>(B64 Enconded string)</AttachmentData>
      <MimeTypeTC tc="17" />
      <AttachmentLocation tc="2">URL Reference</AttachmentLocation>
    </Attachment>
    <Attachment id="Holding_1_attachment_0">
      <AttachmentData>(B64 Enconded string)</AttachmentData>
      <MimeTypeTC tc="17" />
      <AttachmentLocation tc="2">URL Reference</AttachmentLocation>
    </Attachment>
    <Attachment id="234">
      <AttachmentBasicType tc="3">File</AttachmentBasicType>
      <AttachmentSource>C:\Windows Ding.wav</AttachmentSource>
      <AttachmentData>(B64 Enconded string)</AttachmentData>
      <MimeTypeTC tc="7">WAV</MimeTypeTC>
      <TransferEncodingTypeTC tc="4">Base64</TransferEncodingTypeTC>
      <AttachmentLocation tc="1">Inline</AttachmentLocation>
      <FileName>Windows Ding.wav</FileName>
    </Attachment>
    <Attachment id="234">
      <AttachmentBasicType tc="3">File</AttachmentBasicType>
      <AttachmentSource>C:\Windows Ding.wav</AttachmentSource>
      <AttachmentData>(B64 Enconded string)</AttachmentData>
      <MimeTypeTC tc="7">WAV</MimeTypeTC>
      <TransferEncodingTypeTC tc="4">Base64</TransferEncodingTypeTC>
      <AttachmentLocation tc="1">Inline</AttachmentLocation>
      <FileName>Windows Ding2.wav</FileName>
    </Attachment>
</root>

Essentially I have a huge document that has the above XML, and I would like to either remove all the Attachment nodes (including children), or remove the AttachmentData nodes (I haven't quite decided which approach I want to use).

I tried the following to delete the nodes:

UPDATE tblXmlDocumentData
SET DocumentXml = DocumentXml.modify('delete (//Attachment)') /* or //Attachment/AttachmentData */
Where DocumentId = 1

To which SQL replies: Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.

I'm assuming this is because I'm not specifying which Attachment node I want to delete. Can I delete all nodes without having to do it one at a time?

`DocumentXml` is an xml not null column: `DocumentXml (XML(.), not null)` is what SSMS shows. But the error is still present. — Matt R, Jan 17 '14 at 14:32

score 1 · Accepted Answer · answered Jan 17 '14 at 18:53

1

Try this query:

UPDATE tblXmlDocumentData
SET DocumentXml.modify('delete (//Attachment)') 
Where DocumentId = 1

answered Jan 17 '14 at 18:53

Bogdan Sahlean

19,233
3
42
57

I realized my mistake a while ago. The `SET DocumentXml = Document...` was what caused the error. – Matt R Jan 21 '14 at 15:22

Delete multiple XmlData sibling nodes with .remove()

1 Answers1