Varad, The .NET Guy!

SQL Server 2000 - XML UpdateGrams

_{^{We have implemented the feature of XML Updategrams in one of our recent projects, I was very much attracted by the ease coding and performance provided by this new feature in SQL 2000. Now this is the time where many are talking about SQL Server Express and Yukon, But I think this might be useful to many developing for SQL 2000.}}

What are Updategrams?

In essence, Updategrams are a new feature of SQL Server 2000 that allow SQL Server database updates to be defined as XML.  This is ultimately achieved by mapping the XML nodes against corresponding database columns.

Updategrams can be used to replace existing data access components in a middle tier. A typical Windows NT DNA application will include a middle tier consisting of Business Logic and Data Access code. The Data Access code will interface with the database using disconnected Recordsets and Command objects calling SQL Server stored procedures etc. Most of the Data Access section of your middle tier can be replaced with Updategrams.

What are the benefits?

Most Data Access tiers (both middle tier code and stored procedures) will individually deal with specific database tables or groups of related tables. This can inhibit performance and quite often several round trips to the database are required to complete a transaction.

Updategrams can solve this problem by including all of your data in an XML document, which is then mapped to database tables and columns. The entire database update can then be accomplished in one foul swoop. This update can include inserting, updating and deleting data.

How do Updategrams work?

An Updategram consists of data which is mapped to corresponding tables and columns in the database and transaction instructions which are defined as <updg:before> and <updg:after> XML nodes.  To update the database, both <updg:before> and <updg:after> information is supplied.  To Insert new data, just <updg:after> data is supplied and to delete data, just <updg:before> data is supplied.  The whole database update is embedded within an <updg:sync> node which acts as the transaction.  <updg:sync> works in a similar way to BEGIN TRAN and COMMIT TRAN in Transact-SQL. When perfoming Updates and Deletes, affected records are identified by the data contained within the <updg:before>.

Learn more about Updategrams at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/updategram_375f.asp