Start of change

Delete expression

A delete expression deletes zero or more nodes from a node sequence.

Syntax

Read syntax diagram
>>-delete--+-nodes-+--target-expression------------------------><
           '-node--'                      
delete nodes or delete node
The keywords that begin a delete expression. delete nodes or delete node is valid, regardless of the number of nodes that are to be deleted.
target-expression
An XQuery expression that is not an updating expression. The result of target-expression must be a sequence of zero or more nodes.

The result of the delete expression is a list of nodes that are to be deleted. Any node that matches target-expression is marked for deletion. If no nodes match target-expression, no nodes are deleted. The deleted nodes are detached from their parent nodes. The nodes and the nodes' children are no longer part of the node sequence. An error is returned if a node's parent property is empty.

If insertion of nodes results in adjacent text nodes with the same parent, the adjacent text nodes are merged into a single text node. The string value of the resulting text node is the concatenation of the string values of the adjacent text nodes, with no spaces added between string values.

Example

Suppose that a purchaseOrder document looks like this:

<ipo:purchaseOrder
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:ipo="http://www.example.com/IPO"
   orderDate="2008-12-01">
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Helen Zoe</name>
    <street>55 Eden Street</street>
    <city>San Jose</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Joe Lee</name>
    <street>66 University Avenue</street>
    <city>Palo Alto</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <billTo xsi:type="ipo:USAddress">
    <name>Robert Smith</name>
    <street>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <items>
    <item partNum="833-AA">
      <productName>Lapis necklace</productName>
      <quantity>1</quantity>
      <USPrice>99.95</USPrice>
      <ipo:comment>Want this for the holidays!</ipo:comment>
      <shipDate>2008-12-05</shipDate>
    </item>
    <item partNum="945-ZG">
      <productName>Sapphire Bracelet</productName>
      <quantity>2</quantity>
      <USPrice>178.99</USPrice>
      <shipDate>2009-01-03</shipDate>
    </item>
  </items>
</ipo:purchaseOrder>

The following SQL UPDATE statement uses a basic updating expression to delete the item whose productName value is "Lapis necklace" from the purchaseOrder document.

Start of change
UPDATE purchaseOrders
 SET PO = XMLMODIFY(
 'declare namespace ipo="http://www.example.com/IPO";
 delete nodes /ipo:purchaseOrder/items/item[productName="Lapis necklace"]')
End of change

The result of the statement is:

<ipo:purchaseOrder
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:ipo="http://www.example.com/IPO"
   orderDate="2008-12-01">
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Helen Zoe</name>
    <street>55 Eden Street</street>
    <city>San Jose</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Joe Lee</name>
    <street>66 University Avenue</street>
    <city>Palo Alto</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <billTo xsi:type="ipo:USAddress">
    <name>Robert Smith</name>
    <street>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <items>
    <item partNum="945-ZG">
      <productName>Sapphire Bracelet</productName>
      <quantity>2</quantity>
      <USPrice>178.99</USPrice>
      <shipDate>2009-01-03</shipDate>
    </item>
  </items>
</ipo:purchaseOrder>
End of change