Edit

DiffGram Examples (SQLXML 4.0)

  • Article

Applies to: SQL Server Azure SQL Database

The examples in this topic consist of DiffGrams that perform insert, update, and delete operations to the database. Before using the examples, note the following:

  • The examples use two tables (Cust and Ord) that must be created if you want to test the DiffGram examples:

    Cust(CustomerID, CompanyName, ContactName)  
Ord(OrderID, CustomerID)

  • Most of the examples in this topic use the following XSD Schema:

    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  

<xsd:annotation>  
  <xsd:documentation>  
    Diffgram Customers/Orders Schema.  
  </xsd:documentation>  
  <xsd:appinfo>  
       <sql:relationship name="CustomersOrders"   
                  parent="Cust"  
                  parent-key="CustomerID"  
                  child-key="CustomerID"  
                  child="Ord"/>  
  </xsd:appinfo>  
</xsd:annotation>  

<xsd:element name="Customer" sql:relation="Cust">  
  <xsd:complexType>  
    <xsd:sequence>  
      <xsd:element name="CompanyName"    type="xsd:string"/>  
      <xsd:element name="ContactName"    type="xsd:string"/>  
       <xsd:element name="Order" sql:relation="Ord" sql:relationship="CustomersOrders">  
        <xsd:complexType>  
          <xsd:attribute name="OrderID" type="xsd:int" sql:field="OrderID"/>  
          <xsd:attribute name="CustomerID" type="xsd:string"/>  
        </xsd:complexType>  
      </xsd:element>  
    </xsd:sequence>  
    <xsd:attribute name="CustomerID" type="xsd:string" sql:field="CustomerID"/>  
  </xsd:complexType>  
</xsd:element>  

</xsd:schema>

    Save this schema as DiffGramSchema.xml in the same folder where you save other files used in the examples.

A. Deleting a record by using a DiffGram

The DiffGram in this example deletes a customer (whose CustomerID is ALFKI) record from the Cust table and deletes the corresponding order record (whose OrderID is 1) from the Ord table.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:mapping-schema="DiffGramSchema.xml">  
  <diffgr:diffgram   
           xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"   
           xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">  
    <DataInstance/>  
  
    <diffgr:before>  
        <Order diffgr:id="Order1"   
               msdata:rowOrder="0"   
               CustomerID="ALFKI"   
               OrderID="1"/>  
        <Customer diffgr:id="Customer1"   
                  msdata:rowOrder="0"   
                  CustomerID="ALFKI">  
           <CompanyName>Alfreds Futterkiste</CompanyName>  
           <ContactName>Maria Anders</ContactName>  
        </Customer>  
    </diffgr:before>  
    <msdata:errors/>  
  </diffgr:diffgram>  
</ROOT>

In the <before> block, there is an <Order> element (diffgr:id="Order1") and a <Customer> element (diffgr:id="Customer1"). These elements represent existing records in the database. The <DataInstance> element does not have the corresponding records (with the same diffgr:id). This indicates a delete operation.

To test the DiffGram

  1. Create these tables in the tempdb database.

    CREATE TABLE Cust(  
        CustomerID  nchar(5) Primary Key,  
        CompanyName nvarchar(40) NOT NULL ,  
        ContactName nvarchar(60) NULL)  
GO  

CREATE TABLE Ord(  
   OrderID    int Primary Key,  
   CustomerID nchar(5) Foreign Key REFERENCES Cust(CustomerID))  
GO

  2. Add this sample data:

    INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ALFKI', N'Alfreds Futterkiste', N'Maria Anders')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANATR', N'Ana Trujillo Emparedados y helados', N'Ana Trujillo')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANTON', N'Antonio Moreno Taquería', N'Antonio Moreno')  

INSERT INTO Ord(OrderID, CustomerID) VALUES(1, N'ALFKI')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(2, N'ANATR')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(3, N'ANTON')

  3. Copy the DiffGram above and paste it into a text file. Save the file as MyDiffGram.xml in the same folder used in the previous step.

  4. Copy the DiffGramSchema provided earlier in this topic and paste it into a text file. Save the file as DiffGramSchema.xml.

  5. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the DiffGram.

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

B. Inserting a record by using a DiffGram

In this example, the DiffGram inserts a record in the Cust table and a record in the Ord table.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"   
      sql:mapping-schema="DiffGramSchema.xml">  
  <diffgr:diffgram   
          xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"   
          xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">  
    <DataInstance>  
       <Customer diffgr:id="Customer1" msdata:rowOrder="0"    
                 diffgr:hasChanges="inserted" CustomerID="ALFKI">  
        <CompanyName>C3Company</CompanyName>  
        <ContactName>C3Contact</ContactName>  
        <Order diffgr:id="Order1"   
               msdata:rowOrder="0"  
               diffgr:hasChanges="inserted"   
               CustomerID="ALFKI" OrderID="1"/>  
      </Customer>  
    </DataInstance>  
  
  </diffgr:diffgram>  
</ROOT>

In this DiffGram the <before> block is not specified (no existing database records identified). There are two record instances (identified by the <Customer> and <Order> elements in the <DataInstance> block) that map to Cust and Ord tables, respectively. Both of these elements specify the diffgr:hasChanges attribute (hasChanges="inserted"). This indicates an insert operation. In this DiffGram, if you specify hasChanges="modified", you are indicating that you want to modify a record that does not exist, which results in an error.

To test the DiffGram

  1. Create these tables in the tempdb database.

    CREATE TABLE Cust(  
        CustomerID  nchar(5) Primary Key,  
        CompanyName nvarchar(40) NOT NULL ,  
        ContactName nvarchar(60) NULL)  
GO  

CREATE TABLE Ord(  
   OrderID    int Primary Key,  
   CustomerID nchar(5) Foreign Key REFERENCES Cust(CustomerID))  
GO

  2. Add this sample data:

    INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ALFKI', N'Alfreds Futterkiste', N'Maria Anders')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANATR', N'Ana Trujillo Emparedados y helados', N'Ana Trujillo')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANTON', N'Antonio Moreno Taquería', N'Antonio Moreno')  

INSERT INTO Ord(OrderID, CustomerID) VALUES(1, N'ALFKI')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(2, N'ANATR')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(3, N'ANTON')

  3. Copy the DiffGram above and paste it into a text file. Save the file as MyDiffGram.xml in the same folder used in the previous step.

  4. Copy the DiffGramSchema provided earlier in this topic and paste it into a text file. Save the file as DiffGramSchema.xml.

  5. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the DiffGram.

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

C. Updating an existing record by using a DiffGram

In this example, the DiffGram updates customer information (CompanyName and ContactName) for customer ALFKI.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:mapping-schema="DiffGramSchema.xml">  
  <diffgr:diffgram   
           xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"   
           xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">  
    <DataInstance>  
      <Customer diffgr:id="Customer1"   
                msdata:rowOrder="0" diffgr:hasChanges="modified"   
                CustomerID="ALFKI">  
          <CompanyName>Bottom Dollar Markets</CompanyName>  
          <ContactName>Antonio Moreno</ContactName>  
      </Customer>  
    </DataInstance>  
  
    <diffgr:before>  
     <Customer diffgr:id="Customer1"   
               msdata:rowOrder="0"   
               CustomerID="ALFKI">  
        <CompanyName>Alfreds Futterkiste</CompanyName>  
        <ContactName>Maria Anders</ContactName>  
      </Customer>  
    </diffgr:before>  
  
  </diffgr:diffgram>  
</ROOT>

The <before> block includes a <Customer> element (diffgr:id="Customer1"). The <DataInstance> block includes the corresponding <Customer> element with same id. The <customer> element in the <NewDataSet> also specifies diffgr:hasChanges="modified". This indicates an update operation, and the customer record in the Cust table is updated accordingly. Note that if the diffgr:hasChanges attribute is not specified, the DiffGram processing logic ignores this element and no updates are performed.

To test the DiffGram

  1. Create these tables in the tempdb database.

    CREATE TABLE Cust(  
        CustomerID  nchar(5) Primary Key,  
        CompanyName nvarchar(40) NOT NULL ,  
        ContactName nvarchar(60) NULL)  
GO  

CREATE TABLE Ord(  
   OrderID    int Primary Key,  
   CustomerID nchar(5) Foreign Key REFERENCES Cust(CustomerID))  
GO

  2. Add this sample data:

    INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ALFKI', N'Alfreds Futterkiste', N'Maria Anders')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANATR', N'Ana Trujillo Emparedados y helados', N'Ana Trujillo')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANTON', N'Antonio Moreno Taquería', N'Antonio Moreno')  

INSERT INTO Ord(OrderID, CustomerID) VALUES(1, N'ALFKI')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(2, N'ANATR')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(3, N'ANTON')

  3. Copy the DiffGram above and paste it into a text file. Save the file as MyDiffGram.xml in the same folder used in the previous step.

  4. Copy the DiffGramSchema provided earlier in this topic and paste it into a text file. Save the file as DiffGramSchema.xml.

  5. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the DiffGram.

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

D. Inserting, updating, and deleting records by using a DiffGram

In this example, a relatively complex DiffGram is used to perform insert, update, and delete operations.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:mapping-schema="DiffGramSchema.xml">  
  <diffgr:diffgram   
         xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"   
         xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">  
    <DataInstance>  
      <Customer diffgr:id="Customer2" msdata:rowOrder="1"   
                diffgr:hasChanges="modified"   
                CustomerID="ANATR">  
          <CompanyName>Bottom Dollar Markets</CompanyName>  
          <ContactName>Elizabeth Lincoln</ContactName>  
          <Order diffgr:id="Order2" msdata:rowOrder="1"   
               msdata:hiddenCustomerID="ANATR"   
               CustomerID="ANATR" OrderID="2"/>  
      </Customer>  
  
      <Customer diffgr:id="Customer3" msdata:rowOrder="2"   
                CustomerID="ANTON">  
         <CompanyName>Chop-suey Chinese</CompanyName>  
         <ContactName>Yang Wang</ContactName>  
         <Order diffgr:id="Order3" msdata:rowOrder="2"   
               msdata:hiddenCustomerID="ANTON"   
               CustomerID="ANTON" OrderID="3"/>  
      </Customer>  
      <Customer diffgr:id="Customer4" msdata:rowOrder="3"   
                diffgr:hasChanges="inserted"   
                CustomerID="AROUT">  
         <CompanyName>Around the Horn</CompanyName>  
         <ContactName>Thomas Hardy</ContactName>  
         <Order diffgr:id="Order4" msdata:rowOrder="3"   
                diffgr:hasChanges="inserted"   
                msdata:hiddenCustomerID="AROUT"   
               CustomerID="AROUT" OrderID="4"/>  
      </Customer>  
    </DataInstance>  
    <diffgr:before>  
      <Order diffgr:id="Order1" msdata:rowOrder="0"   
             msdata:hiddenCustomerID="ALFKI"   
             CustomerID="ALFKI" OrderID="1"/>  
      <Customer diffgr:id="Customer1" msdata:rowOrder="0"   
                CustomerID="ALFKI">  
        <CompanyName>Alfreds Futterkiste</CompanyName>  
        <ContactName>Maria Anders</ContactName>  
      </Customer>  
      <Customer diffgr:id="Customer2" msdata:rowOrder="1"   
                CustomerID="ANATR">  
        <CompanyName>Ana Trujillo Emparedados y helados</CompanyName>  
        <ContactName>Ana Trujillo</ContactName>  
      </Customer>  
    </diffgr:before>  
  </diffgr:diffgram>  
</ROOT>

The DiffGram logic processes this DiffGram as follows:

  • In accordance with DiffGram processing logic, all the top-level elements in the <before> block map to corresponding tables, as described in the mapping schema.

  • The <before> block has an <Order> element (dffgr:id="Order1") and a <Customer> element (diffgr:id="Customer1") for which there is no corresponding element in the <DataInstance> block (with the same ID). This indicates a delete operation, and the records are deleted from the Cust and Ord tables.

  • The <before> block has a <Customer> element (diffgr:id="Customer2") for which there is a corresponding <Customer> element in the <DataInstance> block (with the same ID). The element in the <DataInstance> block specifies diffgr:hasChanges="modified". This is an update operation in which for customer ANATR, the CompanyName and ContactName information is updated in the Cust table using values that are specified in the <DataInstance> block.

  • The <DataInstance> block has a <Customer> element (diffgr:id="Customer3") and an <Order> element (diffgr:id="Order3"). Neither of these elements specify the diffgr:hasChanges attribute. Therefore, the DiffGram processing logic ignores these elements.

  • The <DataInstance> block has a <Customer> element (diffgr:id="Customer4") and an <Order> element (diffgr:id="Order4") for which there are no corresponding elements in the <before> block. These elements in the <DataInstance> block specify diffgr:hasChanges="inserted". Therefore, a new record is added in the Cust table and in the Ord table.

To test the DiffGram

  1. Create the following tables in the tempdb database.

    CREATE TABLE Cust(  
        CustomerID  nchar(5) Primary Key,  
        CompanyName nvarchar(40) NOT NULL ,  
        ContactName nvarchar(60) NULL)  
GO  

CREATE TABLE Ord(  
   OrderID    int Primary Key,  
   CustomerID nchar(5) Foreign Key REFERENCES Cust(CustomerID))  
GO

  2. Add this sample data:

    INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ALFKI', N'Alfreds Futterkiste', N'Maria Anders')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANATR', N'Ana Trujillo Emparedados y helados', N'Ana Trujillo')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANTON', N'Antonio Moreno Taquería', N'Antonio Moreno')  

INSERT INTO Ord(OrderID, CustomerID) VALUES(1, N'ALFKI')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(2, N'ANATR')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(3, N'ANTON')

  3. Copy the DiffGram above and paste it into a text file. Save the file as MyDiffGram.xml in the same folder used in the previous step.

  4. Copy the DiffGramSchema provided earlier in this topic and paste it into a text file. Save the file as DiffGramSchema.xml.

  5. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the DiffGram.

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

E. Applying updates by using a DiffGram with the diffgr:parentID annotation

This example illustrates how the parentID annotation that is specified in the <before> block of the DiffGram is used in applying the updates.

<NewDataSet />  
<diffgr:before>  
   <Order diffgr:id="Order1" msdata:rowOrder="0" OrderID="2" />  
   <Order diffgr:id="Order3" msdata:rowOrder="2" OrderID="4" />  
  
   <OrderDetail diffgr:id="OrderDetail1"   
                diffgr:parentId="Order1"   
                msdata:rowOrder="0"   
                ProductID="13"   
                OrderID="2" />  
   <OrderDetail diffgr:id="OrderDetail3"   
                diffgr:parentId="Order3"  
                ProductID="77"  
                OrderID="4"/>  
</diffgr:before>  
</diffgr:diffgram>

This DiffGram specifies a delete operation because there is only a <before> block. In the DiffGram, the parentID annotation is used to specify a parent-child relationship between the orders and order details. When SQLXML deletes the records, it deletes records from the child table that is identified by this relationship and then deletes the records from the corresponding parent table.