Using DiffGrams for XML data modifications (XML and SQL part 9) - Page 2
August 22, 2003
There are two situations to consider when inserting rows into the Shippers table, which contains identity column (ShipperID):
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:
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.