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:
According to general syntactical rules, Diffgrams consist of five main elements:
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:
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:
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>