Binaryworld - Update SQL Server database with Updategrams ... [ SQL Server -> T-SQL ]

You can use XML to transfer both data and commands. In this column I'll show how you can update a database by means of XML commands sent over the HTTP protocol and, even more interesting, how to perform bulk uploads with these commands.

An updategram is a piece of XML data that contains information about how to modify data in a database, expressed as an insert, update, or delete operation of existing records. Consider the following updategram:

Click here to copy the following block

The updg:sync element embeds the information that specifies how to manipulate data. Its child element, updg:before, contains information about the record that is going to be modified. In this particular case this information is missing, and the opening tag is immediately followed by the closing tag. Next, consider the updg:after element: the data in this tag represents an element named Employees, which in turn contains the FirstName and LastName attributes. This updategram will be converted into an INSERT command that will insert a new record and assign the specified values ("Nancy" and "Davolio") to these fields. Here are two more examples that show how records can be deleted and updated.
The following updategram is equivalent to this DELETE statement

Click here to copy the following block

DELETE FROM Employees WHERE FirstName="Nancy" AND LastName="Fuller"

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<Employees FirstName="Nancy" LastName="Fuller" />
</updg:before>
<updg:after>
</updg:after>
</updg:sync>
</ROOT>

The following updategram is equivalent to this UPDATE statement

Click here to copy the following block

UPDATE Employees SET Lastname="Fuller" WHERE EmployeeID=1

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<Employees EmployeeID="1" />
</updg:before>
<updg:after>
<Employees LastName="Fuller" />
</updg:after>
</updg:sync>
</ROOT>