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
- 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
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
<?xml version="1.0" ?>
<xsd:element name="Shippers" msch:relation="Shippers"
<xsd:attribute name="ShipperID" type="xsd:integer"
<xsd:attribute name="CompanyName" type="xsd:string"/>
<xsd:attribute name="Phone" type="xsd:string"/>
With the XSD template defined as above, you would create a diffgram in the
>Shippers diffgr:id="Shipper4" msdata:rowOrder="0"
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
- 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
- Copy the Shippers.xsd and InsertShippers.xml file to the
- Finally, type
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:
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
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