Deletion of rows with XML documents from tables

To delete rows that contain XML documents, you can use the DELETE SQL statement. You can 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 with the XMLEXISTS predicate. When you specify XMLEXISTS in a WHERE clause, rows are deleted if the XQuery expression returns a non-empty sequence.

If an XML column is nullable, to delete a value from the XML column without deleting the row, use the UPDATE SQL statement to set the column value to NULL.

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.

DELETE FROM MYCUSTOMER
 WHERE XMLEXISTS ('declare default element namespace "http://posample.org"; 
//addr[city="Markham"]' passing INFO)

Example: Delete the XML document in the row of MYCUSTOMER for which the value of the city element is Markham, but leave the row.

UPDATE MYCUSTOMER SET INFO = NULL 
  WHERE XMLEXISTS ('$declare default element namespace "http://posample.org";
  //addr[city="Markham"]' passing INFO)