Deleting Data Using XML Updategrams (SQLXML 4.0)

Applies to: SQL Server Azure SQL Database

An updategram indicates a delete operation when a record instance appears in the <before> block with no corresponding records in the <after> block. In this case, the updategram deletes the record in the <before> block from the database.

This is the updategram format for a delete operation:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">  
  <updg:sync [mapping-schema="SampleSchema.xml"]  >  
   <updg:before>  
       <ElementName />  
      [<ElementName .../>... ]  
   </updg:before>  
    [<updg:after>  
    </updg:after>]  
  </updg:sync>  
</ROOT>  

You can omit the <after> tag if the updategram is performing only a delete operation. If you do not specify the optional mapping-schema attribute, the <ElementName> specified in the updategram maps to a database table and the child elements or attributes map to columns in the table.

If an element specified in the updategram either matches more than one row in the table or does not match any row, the updategram returns an error and cancels the entire <sync> block. Only one record at a time can be deleted by an element in the updategram.

Examples

Examples in this section use default mapping (that is, no mapping schema is specified in the updategram). For more examples of updategrams that use mapping schemas, see Specifying an Annotated Mapping Schema in an Updategram (SQLXML 4.0).

To create working samples using the following examples, you must meet the requirements specified in Requirements for Running SQLXML Examples.

A. Deleting a record by using an updategram

The following updategrams deletes two records from the HumanResources.Shift table.

In these examples, the updategram does not specify a mapping schema. Therefore, the updategram uses default mapping, in which the element name maps to table name and the attributes or subelements map to columns.

This first updategram is attribute-centric and identifies two shifts (Day-Evening and Evening-Night) in the <before> block. Because there is no corresponding record in the <after> block, this is a delete operation.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">  
<updg:sync >  
  <updg:before>  
       <HumanResources.Shift ShiftID="4"  
                        Name="Day-Evening"  
                        StartTime="1900-01-01 11:00:00.000"  
                        EndTime="1900-01-01 19:00:00.000"  
                        ModifiedDate="2004-01-01 00:00:00.000" />  
       <HumanResources.Shift ShiftID="5"  
                        Name="Evening-Night"  
                        StartTime="1900-01-01 19:00:00.000"  
                        EndTime="1900-01-01 03:00:00.000"  
                        ModifiedDate="2004-01-01 00:00:00.000" />  
  </updg:before>  
  <updg:after>  
  </updg:after>  
</updg:sync>  
</ROOT>  
To test the updategram
  1. Complete example B ("Inserting multiple records by using an updategram") in Inserting Data Using XML Updategrams (SQLXML 4.0).

  2. Copy the updategram above to Notepad and save it as Updategram-RemoveShifts.xml in the same folder as was used to complete ("Inserting multiple records by using an updategram") in Inserting Data Using XML Updategrams (SQLXML 4.0).

  3. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the updategram.

    For more information, see Using ADO to Execute SQLXML 4.0 Queries.

See Also

Updategram Security Considerations (SQLXML 4.0)