Deletion of XML data from tables

To delete rows that contain XML documents, use the DELETE SQL statement. Include a WHERE clause when you want to delete specific rows.

You can specify which rows are to be deleted based on values within XML columns. To find values within XML documents, you need to use XQuery expressions. One way of specifying XQuery expressions is the XMLEXISTS predicate, which allows you to specify an XQuery expression and determine if the expression results in an empty sequence. When XMLEXISTS is specified in the WHERE clause, rows will be deleted if the XQuery expression returns a non-empty sequence.

An XML column must either be NULL or contain a well-formed XML document. To delete an XML document from an XML column without deleting the row, use the UPDATE SQL statement with SET NULL, to set the column to NULL, if the column is defined as nullable. To delete objects such as attributes or elements from an existing XML document, use the UPDATE SQL statement with XQuery updating expressions. XQuery updating expressions can to make changes to a copy of the existing XML document. The UPDATE statement then applies the changed copy returned by the XQuery updating expression to the XML column for the specified row.

The following examples demonstrate how XML data can be deleted from XML columns. The examples use table MyCustomer, which is a copy of the sample Customer table, and assume that MyCustomer has been populated with all of the Customer data.

Example: Delete the rows from table MyCustomer for which the Cid column value is 1002.

DELETE FROM MyCustomer WHERE Cid=1002

Example: Delete the rows from table MyCustomer for which the value of the city element is Markham. This statement deletes the row that has a customer ID of 1002.

DELETE FROM MyCustomer
 WHERE XMLEXISTS ('$d//addr[city="Markham"]' passing INFO as "d")

Example: Delete the XML document in the row of MyCustomer for which the value of the city element is Markham, but leave the row. This statement should delete the XML data from the Info column for the row that has a customer ID of 1002.

UPDATE MyCustomer SET Info = NULL 
  WHERE XMLEXISTS ('$d//addr[city="Markham"]' passing INFO as "d")

Example: The following example deletes phone information from existing XML data from the from the MyCustomer table. The SQL UPDATE statement operates on a row of the MyCustomer table. The XQuery transform expression creates a copy of the XML document from the INFO column of the row, and uses the XQuery delete expression to remove the work phone number from the copy of the document. The UPDATE statement replaces the document in the INFO column of the row with the copy of the document modified by the transform expression:

UPDATE MyCustomer 
SET info = XMLQUERY(
   'transform
    copy $newinfo := $info
    modify do delete ($newinfo/customerinfo/phone[@type="work"])
    return $newinfo' passing info as "info")
WHERE cid = 1004