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>
IE 7
Firefox 2.0
Comment and Contribute
