This is the last part of a 3 part blog which will attempt to show some different ways of how you can use the xml modify() method to update the contents of an XML document. This part, starting at the absolute basics, will cover the ability to delete data in an existing XML document

XML DML is an extension of the XQuery language that allows us to do in place updates of the contents of an XML document. XML DML is very flexible and most scenarios can be dealt with quite nicely with the use of the modify() function. We can however only use the modify() function as part of an UPDATE or SET statement. 

Here is a very simple example XML document that we will be using with all the example queries during this article which can be downloaded with all of the example queries here.

<Person Id="1234">
    <?Demographics 18099105?>
    <!-- Comment Number 1 -->
    <Fullname></Fullname>
    <Surname>Smith</Surname>
    <Forenames>John Peter</Forenames>
    <Address>
        <!-- Comment Number 2 -->
        <AddressLine>1 Church Lane</AddressLine>
        <AddressLine>Littlewood</AddressLine> 
        <AddressLine>Upper Westshire</AddressLine>
        <AddressLine>England</AddressLine>
        <Postcode>AA1 1ZZ</Postcode>
    </Address>    
    <!-- Comment Number 3 -->
    <HasPostcode>Unknown</HasPostcode>
</Person>

The Basics

The delete keyword is incredibly simplistic in functionality. It allows for the deletion of a single or mixture of element, attribute, text, comment and PI nodes defined from an XML document by specifying a single expression. MSDN detail the syntax as:

delete Expression

Very straightforward! So let us start with a very simple example that will delete the /Person/Fullname node from the example XML document.

SET @XMLData.modify('
    delete (/Person/Fullname)
')
SELECT @XMLData

Parentheses are optional when not specifying a sequence like in the example above, but I like to use them consistently to aid readability.

The other thing to note is that if the expression evaluates to multiple nodes within the XML document, then all matching nodes will be removed from the XML document. You do not have to ensure that the target node is a singleton like you have to for the insert and replace value of keywords. Conversely, if the target expression evaluates to nothing, then nothing will be deleted and no error will be raised either. The next example demonstrates that if the XPath expression evaluates to multiple nodes, then all nodes are removed by removing all 4 <AddressLine> nodes within one statement.

SET @XMLData.modify('
    delete (/Person/Address/AddressLine)
')
SELECT @XMLData

Variables

You can use variables as part of a predicate to identify nodes for deletion but you cannot use a variable to define an XPath expression. The following example shows the use of a variable to remove the /Person/Surname node if the contents of the node equals "Jones"

DECLARE @Surname VARCHAR(20)
SET @Surname = 'Jones'
SET @XMLData.modify('
    delete /Person/Surname[. = sql:variable("@Surname")]
')
SELECT @XMLData

Sequences

If you need to remove more than one node from the XML document and the nodes don't share the same name then this is possible to do by specifying a sequence. The sequence is essentially a list of XPath expressions evaluating to the nodes that you want to delete seperated by a comma. The following example deletes 2 differently named elements and an attribute within a single delete operation. 

SET @XMLData.modify('
    delete (
/Person/Forenames, 
/Person/@Id,
/Person/Surname[. = "Jones"]
)
')
SELECT @XMLData

Returns (/Person/Forenames and /Person/@Id removed):

<Person>
  <?Demographics 18099105?>
  <!-- Comment Number 1 -->
  <Fullname />
  <Surname>Smith</Surname>
  <Address>
    <!-- Comment Number 2 -->
    <AddressLine>1 Church Lane</AddressLine>
    <AddressLine>Littlewood</AddressLine>
    <AddressLine>Upper Westshire</AddressLine>
    <AddressLine>England</AddressLine>
    <Postcode>AA1 1ZZ</Postcode>
  </Address>
  <!-- Comment Number 3 -->
  <HasPostcode>Unknown</HasPostcode>
</Person>

Note that even though only two of the three XPaths actually evaluate to a node in the example XML document those nodes are still deleted. The third node that evaluates to nothing because of the predicate does not prevent the delete occuring for the nodes that do match.

Text Nodes

For untyped XML documents, it is possible to remove text nodes from simple types and complex types by using the text() function. For typed XML documents, you can only use the text() function on complex types. If you have multiple text nodes within an element and you do not specify an individual node to delete, then all text nodes in that element are removed. The following example removes the first text node from the /Person/HasPostcode element. Because the example is an untyped XML document, this works fine, however if the XML document was strongly typed, this would fail as the text() function is not supported on simple types.

SET @XMLData.modify('
    delete (/Person/HasPostcode/text()[1])
')
SELECT @XMLData

Returns (text node removed from /Person/HasPostcode):

<Person>
  <?Demographics 18099105?>
  <!-- Comment Number 1 -->
  <Fullname />
  <Surname>Smith</Surname>
  <Address>
    <!-- Comment Number 2 -->
    <AddressLine>1 Church Lane</AddressLine>
    <AddressLine>Littlewood</AddressLine>
    <AddressLine>Upper Westshire</AddressLine>
    <AddressLine>England</AddressLine>
    <Postcode>AA1 1ZZ</Postcode>
  </Address>
  <!-- Comment Number 3 -->
  <HasPostcode />
</Person>

Node() or *

If you need to remove all child elements or nodes from a parent node whilst keeping the parent node element then instead of specifying each individual XPath expression, the use of * or the node() function can help. The asterix (*) when used will identify all child element nodes whilst the node() function will identify all nodes such as comments and PIs. The next example will delete all child element nodes from /Person/Address.

SET @XMLData.modify('
    delete /Person/Address/*
')
SELECT @XMLData

Returns (all nodes except for comment node removed from /Person/Address):

<Person Id="1234">
  <?Demographics 18099105?>
  <!-- Comment Number 1 -->
  <Fullname />
  <Surname>Smith</Surname>
  <Forenames>John Peter</Forenames>
  <Address>
    <!-- Comment Number 2 -->
  </Address>
  <!-- Comment Number 3 -->
  <HasPostcode>Unknown</HasPostcode>
</Person>

This example is similar to the above, but uses the node() function instead to remove all nodes from /Person/Address:

SET @XMLData.modify('
    delete /Person/Address/node()
')
SELECT @XMLData

Returns (all nodes removed from /Person/Address):

<Person Id="1234">
  <?Demographics 18099105?>
  <!-- Comment Number 1 -->
  <Fullname />
  <Surname>Smith</Surname>
  <Forenames>John Peter</Forenames>
  <Address />
  <!-- Comment Number 3 -->
  <HasPostcode>Unknown</HasPostcode>
</Person>

Comments and PIs

It is possible to delete comment nodes and processing-instruction nodes from an XML document as well. This is achieved by using either the comment() function or the processing-instruction() function. The next example uses a sequence to remove all comment nodes under /Person and also the "Demographics" processing instruction node under /Person. If we only wanted to remove one of the comments then we could supply the ordinal position of the comment to delete via a predicate e.g. [2].

SET @XMLData.modify('
    delete (
/Person/comment(),
/Person/processing-instruction("Demographics")
)
')
SELECT @XMLData

Returns (all comments from /Person removed and the single PI removed):

<Person Id="1234">
  <Fullname />
  <Surname>Smith</Surname>
  <Forenames>John Peter</Forenames>
  <Address>
    <!-- Comment Number 2 -->
    <AddressLine>1 Church Lane</AddressLine>
    <AddressLine>Littlewood</AddressLine>
    <AddressLine>Upper Westshire</AddressLine>
    <AddressLine>England</AddressLine>
    <Postcode>AA1 1ZZ</Postcode>
  </Address>
  <HasPostcode>Unknown</HasPostcode>
</Person>

So that concludes this part and this introductory series which I hope has shown how to use the all three XML DML keywords in a variety of ways. I have tried to keep the examples as simplistic as possible to show the functionality but with a little imagination it is easy to see just how powerful and flexible the XML DML syntax allows you to be. 

Enjoy!