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

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

  • <?xml
    version="1.0" ?>

  • <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 –>
xmlns_sql=”urn:schemas-microsoft-com:xml-sql” >
<ElementType name=”Shippers”
order=”many” >
<AttributeType name=”ShipperID”
dt_type=”int” >
<AttributeType name=”CompanyName”
dt_type=”string” >
<AttributeType name=”Phone”
dt_type=”string” >
<attribute type=”ShipperID”
required=”no” >
<attribute type=”CompanyName”
required=”no” >
<attribute type=”Phone”
required=”no” >

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
    FilesSQLXML 3.0bin folder),

  • by running the
    XSD.EXE tool (the file resides in the Program FilesMicrosoft.NETSDKv1.1Bin
    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=
“” xmlns_dt=”urn:schemas-microsoft-com:datatypes”
<xsd:element name=”Shippers” msch_relation=”Shippers”
<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”/>

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles