In a previous article of this series, I described using bulk loading of XML data with scripting interfaces included in the SQLXML version 2.0 and later. Now, we are going to explore another feature introduced in SQLXML version 2.0 (and enhanced in the version 3.0) called Diffgrams.

In many aspects, Diffgrams are similar to the Updategrams described in an earlier article. They both can be used to modify relational data stored in a SQL database and they both accomplish this by comparing the "before" and "after" representation of this data presented in the XML format. However, there are also significant differences between the two, which deal with the following topics:

• support for insert operations on tables with identity columns - even though it is available in both cases - is more complex in the case of diffgrams,
• support for parameters - available only in updategrams,
• presence of corresponding mapping schemas - in some (typically the simplest) cases, it is possible to use updategrams without corresponding mapping schemas, but schemas are always required when using diffgrams,
• integration with ADO - available in both cases, however diffgrams provide much better integration with ADO.NET object model.

According to general syntactical rules, Diffgrams consist of five main elements:

• <?xml version="1.0" ?> predicate
• <diffgr:diffgram> element, which contains references to the namespaces and schemas used in the diffgram (including xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" namespace),
• <DataInstance> element, which contains data values that will be used to apply changes to the database. If the change involves deletion of data, than this element is empty (but it has to be present).
• <diffgr:before> element, which contains data values to which the change will be applied. If the change involves insertion of the data, then this element is omitted.
• <diffgr:errors> element, which is intended as a container for errors that might have taken place during data modification. This element, however, is not used for SQL Server 2000 data modifications via SQLXML (so we will not be using it throughout our examples).

When modifying SQL Server 2000 data via SQLXML 2.0 or 3.0, diffgrams are stored on a Web server hosting the virtual directory representing the target database (just as XML templates are). This has two main implications in terms of IIS configuration:

• "Allow template queries" on the Settings tab of a virtual directory properties dialog box needs to be enabled.
• XML document representing diffgram, needs to reside in a folder associated with a virtual name of type template assigned to it.

Starting with the SQLXML 2.0, the management of the Web server is done using the IIS Virtual Directory Management tool. Even though the name of the tool has changed, its interface and most of functionality is very similar to the Configure SQL XML support in IIS tool included with SQL Server 2000. However, if your virtual directory was created using the original version of SQLXML, you will also need to upgrade it. This is done by launching IIS Virtual Directory Management tool, bringing up the virtual directory Properties dialog box, and clicking on "Upgrade to version 3" (or version 2 with SQLXML 2.0) command button located on identically labeled tab. This tab disappears once the upgrade is complete.

In addition, diffgrams require use of XDS mapping schemas. In the examples presented so far, we have been using XDR mapping schemas. As we pointed out in our previous article, creation of XDR schemas is simplified with the XML View Mapper utility. This freely downloadable GUI-based tool utilizes tables from a source database as the basis for schema definition. For example, XLM View Mapper produces the following schema for the Shippers table from the Northwind database:

<?xml  version="1.0"  encoding="windows-1252" ?>

<!-- Generated by XMLMapper.exe XDR Publisher -->

<Schema xmlns="urn:schemas-microsoft-com:xml-data" 

        xmlns:dt="urn:schemas-microsoft-com:datatypes" 

        xmlns:sql="urn:schemas-microsoft-com:xml-sql" > 

 <ElementType name="Shippers" 

              content="mixed" 

              order="many" > 

   <AttributeType name="ShipperID" 

                  dt:type="int" > 

   </AttributeType>

   <AttributeType name="CompanyName" 

                  dt:type="string" > 

   </AttributeType>

   <AttributeType name="Phone" 

                  dt:type="string" > 

   </AttributeType>

   <attribute type="ShipperID" 

              required="no" > 

   </attribute>

   <attribute type="CompanyName" 

              required="no" > 

   </attribute>

   <attribute type="Phone" 

              required="no" > 

   </attribute>

 </ElementType>

</Schema>

Once the XDR schema is available, you can convert it to XDS format by applying one of the following two methods:

• by running CVTSCHEMA.EXE included with SQLXML 3.0 (the file resides in the Program Files\SQLXML 3.0\bin folder),
• by running the XSD.EXE tool (the file resides in the Program Files\Microsoft.NET\SDK\v1.1\Bin folder), included with the Microsoft .NET Framework SDK, downloadable from the Microsoft Web site. Keep in mind that SDK takes a while to download (version 1.1 takes roughly 106 MB). One of many features offered by this tool is the ability to generate XSD schema (using various sources, including an XDR schema or an XML document).

After the conversion, we will end up with XSD schema in the format:

<?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"/>

    <xsd:attribute name="CompanyName" type="xsd:string"/>

    <xsd:attribute name="Phone" type="xsd:string"/>

  </xsd:complexType>

</xsd:schema>

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