Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS2K
Expertise: Intermediate
Aug 8, 2002

Update SQL Server database with Updategrams

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

########################################################

Giuseppe Dimauro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date