使用 .remove() 删除多个 XmlData 同级节点

Question

这个问题与 SQL Server 的单个 XQuery 中的多个节点类似。不同之处在于我想不分青红皂白地删除文档中的所有节点。

.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>

本质上，我有一个包含上述XML的巨大文档，我想删除所有Attachment节点（包括子节点），或删除AttachmentData节点（我还没有完全决定要使用哪种方法）。

我尝试了以下方法来删除节点：

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

SQL 回复： Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.

我假设这是因为我没有指定要删除的Attachment节点。我可以删除所有节点而不必一次删除一个节点吗？

Answer 1

尝试以下查询：

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