Lesson 5: Deleting XML data

This lesson shows you how to use SQL statements to delete entire XML documents or only sections of XML documents.

Deleting entire XML documents

To delete entire XML documents, use the DELETE SQL statement. Use the XMLEXISTS predicate to identify particular documents to delete.

To delete only XML documents that have a <customerinfo> element with an attribute Cid=1003 from the Info column, issue the following statement:

DELETE FROM Customer 
WHERE XMLEXISTS (
 'declare default element namespace "http://posample.org";
  $doc/customerinfo[@Cid = 1003]' 
  passing INFO as "doc")~

If you can identify XML documents in a table by using values in the non-XML columns of the same table, you can use SQL comparison predicates to identify which rows to delete. In the previous example, where the Cid value from the XML document is also stored in the CID column of the CUSTOMER table, you could have performed the same operation by using the following DELETE statement, which applies an SQL comparison predicate to the CID column to identify the row:

DELETE FROM Customer WHERE Cid=1003~

To confirm that the XML documents were deleted, issue the following SQL statement:

SELECT * FROM Customer~

Two records are returned.

Deleting sections of XML documents

To delete only sections of an XML document instead of deleting the entire document, use an UPDATE SQL statement that contains a delete XQuery updating expression.

To delete all phone information from a customer record where the value of Cid is 1002, issue the following SQL statement, which uses the XMLQUERY function:

UPDATE Customer 
SET info = XMLQUERY(
   'declare default element namespace "http://posample.org"; 
    transform
    copy $newinfo := $info
    modify do delete ($newinfo/customerinfo/phone)
    return $newinfo' passing info as "info")
WHERE cid = 1002~

To remove the <phone> element, the XMLQUERY function executes an XQuery transform expression that uses a delete expression and then returns the updated information to the UPDATE statement, as follows:

The XMLQUERY passing clause uses the identifier info to pass the customer information to the XQuery expression from the XML column INFO.
In the copy clause of the transform expression, a logical snapshot of the customer information is taken and assigned to the $newinfo variable.
In the modify clause of the transform expression, the delete expression deletes the <phone> element in the copy of the customer information.
XMLQUERY returns the updated customer document in the $newinfo variable.

To confirm that the customer record no longer contains a <phone> element, issue the following statement:

SELECT * FROM Customer WHERE Cid=1002~

Return to the tutorial