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:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
</updg:before>
<updg:after>
<Employees FirstName="Nancy" LastName="Davolio" />
</updg:after>
</updg:sync>
</ROOT>
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
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
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>
########################################################
This tip has been originally published on Microsoft Italia's web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli
########################################################
If you have a hot tip and we publish it, we'll pay you. However, due to accounting overhead we no longer pay $10 for a single tip submission. You must accumulate 10 acceptable tips to receive payment. Be sure to include a clear explanation of what the technique does and why it's useful. If it includes code, limit it to 20 lines if possible.
Submit your tip here.
