Using DiffGrams for XML data modifications (XML and SQL part 9) - Page 2

August 22, 2003

There are two situations to consider when inserting rows into the Shippers table, which contains identity column (ShipperID):

• value in the identity column should be assigned using the next available value (and value specified in the diffgram should be ignored). This is accomplished by setting the identity annotation with the value of ignore (as defined in the "urn:schemas-microsoft-com:mapping-schema" namespace)
  
  
• Value in the identity column should be assigned according to the data value in the diffgram. This is accomplished by setting the identity annotation with the value of useValue (as defined in the "urn:schemas-microsoft-com:mapping-schema" namespace)

For example, to insert the record into the Shippers table of Northwind database and set the value of the identity column according to the value provided in the diffgram (the second case), our schema would take the form:

<?xml version="1.0" ?>
<xsd:schema xmlns:xsd=
	"http://www.w3.org/2001/XMLSchema" xmlns:dt=
	  "urn:schemas-microsoft-com:datatypes" 
xmlns:msch="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Shippers" msch:relation="Shippers"
 	type="Shippers_type"/>
  <xsd:complexType name="Shippers_type">
    <xsd:attribute name="ShipperID" type="xsd:integer"
		msch:identity="useValue"/>
    <xsd:attribute name="CompanyName" type="xsd:string"/>
    <xsd:attribute name="Phone" type="xsd:string"/>
  </xsd:complexType>
</xsd:schema>

With the XSD template defined as above, you would create a diffgram in the following format:

>ShippersList xmlns:sql="urn:schemas-microsoft-com:xml-sql" 
          sql:mapping-schema="ShippersID.xsd"<
  >diffgr:diffgram 
     xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" 
     xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"<
  >DataInstance<
       >Shippers diffgr:id="Shipper4" msdata:rowOrder="0"  
                 diffgr:hasChanges="inserted"
		 ShipperID="4" 
 		 CompanyName="Speedy Gonzalez"
        	 Phone="(503) 555-9934"/<
    >/DataInstance<
  >/diffgr:diffgram<
>/ShippersList<

The diffgram above assumes that the XSD schema created previously has been saved as Shippers.xsd and stored in the same folder as the diffgram itself. Save it as InsertShippers.xml.

In order to examine how Diffgrams operate, use the following sequence of steps:

• Create a physical folder that will host a virtual directory that will be created in the next step. For the sake of an example, let's assume that the folder will be C:\Inetpub\wwwroot\Northwind.
• Create virtual directory Northwind using IIS Virtual Directory Management for SQLXML 3.0, with the folder created in the previous step as the local path. Make sure that "Allow template queries" is selected on the Settings tab.
• Create a subfolder in the folder hosting the Northwind virtual directory called Diffgrams (this name is arbitrary),
• Create a new virtual name (we will call it Diffgrams, but you can choose any name) of the template type and point it to C:\Inetpub\wwwroot\Northwind\Diffgrams,
• Copy the Shippers.xsd and InsertShippers.xml file to the Diffgrams subfolder.
• Finally, type
  http://localhost/ServerName/Diffgrams/InsertShippers.xml
  in the URL box of the browser, where ServerName is the name of the Web server hosting the virtual directory. Providing the operation was successful, you will see the following being displayed:
  <ShippersList xmlns:sql="urn:schemas-microsoft-com:xml-sql" />

Now let's demonstrate how to delete data from a SQL Server 2000 database with Diffgrams. Our example will remove the same row that was just inserted. Since the schema has not changed, we simply need to create a new XML document that will contain an empty <DataInstance> element and appropriate values in the <diffgr:before> element. This will take the following form:

<ShippersList xmlns:sql="urn:schemas-microsoft-com:xml-sql" 
          sql:mapping-schema="Shippers.xsd">
  <diffgr:diffgram 
     xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" 
     xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
  <DataInstance />
  <diffgr:before>
       <Shippers diffgr:id="Shipper4"  
		 ShipperID="4" 
 		 CompanyName="Speedy Gonzalez"
        	 Phone="(503) 555-9934"/>
  </diffgr:before>
  </diffgr:diffgram>
</ShippersList>

Note that, with existing schema, you need to provide values for all columns of the row to be deleted. If you omit any of them, they will be substituted with NULL value, which, in our case, would not produce the desired results (since the row for ShipperID of 4 does not have a NULL value in any of its columns, the row would not get deleted). If your intention is to delete rows based on the value of ShipperID only, you will need to modify the Shippers.XSD schema so it includes only this single attribute.

Even though diffgrams seem to be more cumbersome to use than updategrams, it is likely that you will run across them sooner or later, since they are the preferred method of dealing with SQL server database modifications with ADO.NET. You can use it as an alternative to updategrams, keeping in mind the pros and cons of each.

In the next article, I will present another feature included in SQLXML 2.0 and 3.0, called client-side XML processing.

» See All Articles by Columnist Marcin Policht