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

August 22, 2003

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>