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:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync [mapping-schema= "XDRSchemaFile.xml"]>
    <updg:before>
       ...
    </updg:before>
    <updg:after>
       ...
    </updg:after>
  </updg:sync>
</ROOT>

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:

<ROOT xmlns:eps="urn:schemas-microsoft-com:xml-updategram">
  <eps:sync mapping-schema=["XDRSchemaFile.xml"]>
    <eps:before>
       ...
    </eps:before>
    <eps:after>
       ...
    </eps:after>
  </eps:sync>
</ROOT>

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:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
   <updg:sync >
      <updg:before>
      </updg:before>
      <updg:after>
         <Customers CustomerID="TEST" CompanyName="Test insert comp."/>
      </updg:after>
   </updg:sync>
</ROOT>

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.

Implicit Mapping in UpdateGrams

An element-centric UpdateGram is comprised of elements that contain sub-elements. The elements map to a table in the database and the sub-elements map to fields within that table. So, to use element-centric mapping, we would have an XML document that looks something like this:

<Customers>
   <CustomerID>TEST</CustomerID>
   <CompanyName>Test insert comp</CompanyName>
</Customers>

In this case, to perform an INSERT, we would construct an UpdateGram that would look something like this:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
   <updg:sync >
      <updg:after>
         <Customers>
            <CustomerID>TEST</CustomerID>
            <CompanyName>Test insert comp</CompanyName>
         </Customers>
      </updg:after>
   </updg:sync>
</ROOT>

Note that there is not a <before> element, yet this UpdateGram will still work. That's because for an INSERT operation there is no corresponding information for the record, therefore the <before> element is optional. The same holds true for a DELETE operation. Since there will not be any corresponding information after the delete is performed, the <after> element can be omitted.

For attribute-centric mapping, the table element contains attributes that map to fields within the table. The same example above could be represented as an attribute-centric approach by having an XML document like this:

<Customers CustomerID="TEST" CompanyName="Test insert comp"/>

You could use an XML UpdateGram like this:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
   <updg:sync >
      <updg:after>
         <Customers CustomerID="TEST" CompanyName="Test insert comp"/>
      </updg:after>
   </updg:sync>
</ROOT>

Interestingly, a combination of element-centric and attribute-centric mapping can be used. As long as the elements and attributes map back into the database using the default mapping provided by FOR XML AUTO, the two approaches can be mixed. For instance, imagine the following XML document:

<Customers CustomerID="Test">
   <CompanyName>Test insert comp</CompanyName>
</Customers>

The above record could be inserted into SQL Server by using an XML UpdateGram like the following:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
   <updg:sync >
      <updg:after>
         <Customers CustomerID="TEST">
            <CompanyName>Test insert comp</CompanyName>
         </Customers>
      </updg:after>
   </updg:sync>
</ROOT>

Explicit Mapping in UpdateGrams

In the case of complex mappings, you can specify an annotated XDR schema for each transaction the XML UpdateGram performs by specifying the name of the corresponding XDR schema in the mapping-schema attribute of the <sync> element. By doing this, explicit mapping is chosen and every element and attribute of the XML document must map to the elements and attributes within the specified XDR schema. For example, in a simple case, an annotated XDR schema named SampleSchema1.xml could be created with the contents shown in Listing 1.

Using that schema, an XML document like the following could be placed into an XML UpdateGram.

<CUSTOMER CustomerID="TEST" CompanyName="Test insert comp" ContactName="Test contact" Address="Some address" City="Unknown" Phone="9999999"/>

To insert this XML document into the Customers table of the Northwind database, an UpdateGram like the following needs to be posted the SQL Server virtual directory:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
   <updg:sync mapping-schema="SampleSchema1.xml">
      <updg:after>
         <CUSTOMER CustomerID="TEST" CompanyName="Test insert comp" ContactName="Test contact" Address="Some address" City="Unknown" Phone="9999999"/>
      </updg:after>
   </updg:sync>
</ROOT>

However, the XDR schemas and the corresponding XML documents can get quite complex. For instance, the XDR schema in Listing 2, SampleSchema6.xml, maps fields and tables from the Northwind database into a hierarchical XML document.

Based upon the schema in Listing 2, an XML document like the following could be placed into an XML UpdateGram.

<CUSTOMER>
   <CUSTOMERID>TEST</CUSTOMERID>
   <COMPANY>Test insert comp</COMPANY>
   <CONTACT>Test contact</CONTACT>
   <ADDRESS>Test address</ADDRESS>
   <CITY>Test city</CITY>
   <PHONE>9999999</PHONE>
   <ORDER updg:at-identity="OrderId">
      <ORDERDATE>2001-07-23</ORDERDATE>
      <DETAILS>
         <ORDERID></ORDERID>
         <PRODUCTID>28</PRODUCTID>
         <UNITPRICE>45.6</UNITPRICE>
         <QUANTITY>15</QUANTITY>
      </DETAILS>
   </ORDER>
</CUSTOMER>

Given this XML document, you can insert the information by creating the XML UpdateGram in Listing 3.

In this example, we see a new attribute for the updategram: at-identity. This attribute captures the identity value for the identity column of the referenced table and stores it in the specified variable for later use in the UpdateGram, as seen on the <ORDERID> element contained in the <DETAILS> element.

NULL Handling in UpdateGrams

NULL fields in SQL Server are not returned in the XML document. However, it is sometimes useful to set a field to NULL. Using UpdateGrams, this can be accomplished by using a nullvalue attribute in the <sync> element. By specifying the nullvalue attribute, SQL ISAPI can insert NULL into the field when it encounters the specified string. For example, to insert a NULL value into the CompanyName field of the Customers table, you could use the following XML UpdateGram:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
   <updg:sync updg:nullvalue=".NULL." >
      <updg:before>
         <Customers CustomerID="ALFKI"/>
      </updg:before>
      <updg:after>
        <Customers CustomerID="ALFKI" CompanyName=".NULL." />
     </updg:after>
   </updg:sync>
</ROOT>

When SQL ISAPI encounters a NULL value in the CompanyName attribute, it inserts NULL into the CompanyName field.

Executing UpdateGrams

Knowing how XML UpdateGrams function is important, but how do we get SQL ISAPI to perform the necessary operations? Quite simply, we POST the UpdateGram to the SQL Server virtual directory. This is accomplished using an HTML page, wwIPStuff available from West Wind Technologies, or the XMLHTTP COM object available in MSXML2. For example, you could do the following in Visual Foxpro to POST an XML UpdateGram to a SQL Server virtual directory called localhost/sql2000:

LOCAL loXML, loPost, lcStatus

loXML = CreateObject("MSXML2.DOMDocument")
loPost = CreateObject("MSXML2.XMLHTTP")

If VarType(loPost) = "O" And ;
   VarType(loXML) = "O"
   
   loXML.Async = .F.
   loXML.LoadXML(MyUpdategram)
   
   loPost.Open("POST", ;
     "http://localhost/sql2000", .F.)
   loPost.SetRequestHeader("Content-type", ;
     "text/xml")
   loPost.Send(loXML)
EndIf

You can then query the ResponseText property of the XMLHTTP object to find out whether there was an error.

Unfortunately, I'm not able to include every detail of UpdateGrams within this one article. For more information, download the SQL Server Web Release 2 from the MSDN Web site. That download includes a help file with the complete information and examples on XML UpdateGrams.