XML UpdateGrams in SQL Server

The new XML features in SQL Server 2000 give the developer more power to implement distributed solutions. One of the newest features, XML UpdateGrams, allows the developer to handle the inserting, updating and deleting of records while getting around some of the limitations of URL queries and OPENXML. XML UpdateGrams perform their operations against an XML view, which is provided by an annotated XDR schema that contains the necessary information to map elements and attributes back to their corresponding tables and fields.

Until recently, developers have had to rely on OPENXML and stored procedures to modify data based on an XML document by parsing the document and providing it as a rowset that can then be manipulated like any other cursor or table. OPENXML, however, was clumsy to use, at best, as SQL Server does not provide an easy interface to iterate over a rowset record-by-record using Transact-SQL.

Rather than having to code multiple stored procedures to handle inserting, updating and deleting records within a database, you can use XML UpdateGrams instead. XML UpdateGrams, which are XML documents themselves, are posted to SQL Server's virtual directory and are intercepted by the SQL ISAPI extension. SQL Server can modify the data in the database by using various elements within an XML UpdateGram.

The basic premise behind an XML UpdateGram is that it behaves like a template, in that it uses a snapshot of before and after information to determine how SQL Server should proceed. By using a combination of <sync> elements, which mark the beginning and end of a transaction, and <before> and <after> elements, SQL Server can determine whether a new record is being created or an existing record is being modified or deleted.

The <before> element identifies the existing state of the database, while the <after> element identifies the database's new state. The <sync> element indicates what a transaction encompasses. It contains one or more <before> and <after> elements, and all the pairs within a <sync> element are executed as one transaction. So, either everything or nothing will be performed within a <sync> element.

The basic structure of an XML UpdateGram appears as follows:

Note the inclusion of the UpdateGram namespace, urn:schemas-microsoft-com:xml-updategram. This namespace is required for XML UpdateGrams. However, the actual namespace you use within your UpdateGrams is completely up to you. For instance, the following is also a valid XML UpdateGram structure:

An XML UpdateGram determines what function to perform based upon the contents of the <before> and <after> elements, which is outlined as follows:

If a record's XML definition appears only in the <before> element, with no corresponding definition in the <after> element, the XML UpdateGram performs a delete operation.

If a record's XML appears only in the <after> element, with no corresponding XML in the <before> element, it is an insert operation.

If XML for a record appears in the <before> element and has a corresponding definition in the <after> element, it is an update operation. In this case, the UpdateGram updates the record instance to the values specified in the <after> element.

Mapping the XML to the Database

An XML UpdateGram can map the XML back into the database either implicitly or explicitly. In other words, specifying the XDR schema in the UpdateGram is optional, as long as you follow a few simple rules. First and foremost, you must use FOR XML AUTO or FOR XML AUTO,ELEMENTS in order to use the default mapping that SQL ISAPI provides.

In simple insert, update, or delete scenarios, implicit mapping may be enough to perform the necessary operation on the given XML fragment. SQL ISAPI attempts to map the elements and attributes back to the database in a similar fashion to the way it maps the database to an XML fragment using FOR XML AUTO. The key for this approach, however, is that each element, which represents a table, must be named the same as the table name. Remember, XML is case sensitive. Also, each element or attribute that represents a field must be named the same. For instance, the following XML UpdateGram will insert a new customer into the Customers table of the Northwind database:

By examining the above XML UpdateGram, you can see that it identifies how SQL Server should behave. SQL ISAPI sees that there is nothing in the <before> element that corresponding to an <after> element; therefore an INSERT must be performed. SQL ISAPI also examines the contents of the <after> element and determines from the existence of the <Customers> element that a new record will be inserted into the Customers table. Furthermore, SQL ISAPI realizes that two fields will have values specified for them, CustomerID and CompanyName, based on the occurrence of attributes with those names.

For complex mappings, you can explicitly identify an annotated XDR schema to map the elements and attributes back to the appropriate tables and fields in the database. Each transaction unit, identified by a <sync> element, can have its own mapping schema to map elements and attributes back into the database.

By: Travis Vandersypen

Travis Vandersypen is a Senior Software Developer at EPS Software Corporation, located in Houston, Texas.

He concentrates on consulting on and developing COM-based, distributed applications. He is an international author and speaker, co-author of the Fox Extension Classes, co-author of the Voodoo Web Controls, and 3-time Visual FoxPro Excellence Award Nominee.

He can be reached via email at: travis@eps-software.com.

travis@dilmad.com