Before we talk about Updategrams and Bulk Load the file that provides this
functionality needs to be installed. This file, “xml for sql.exe” is
included with this tutorial. Save this file to your hard drive and click
on Start | Run. Type the path to the directory where you saved this
file. Click OK. Click Next on the Welcome screen. Follow the
directions and make sure all options are installed. Click Finish when
done. It may prompt you to reboot your computer.
Behind sliced bread and XML Templates, Updategrams are the next greatest
thing. That’s because Updategrams are XML Templates with special
tags. Instead of writing SQL statements to do your INSERT, UPDATES, and
DELETES these special tags are used like before and after images of the
database.
For example, instead of writing an INSERT statement to insert a record, the
Upgradegrams allow me to provide a before image of what the table look likes
and an after image of the table, this inserting the new record.
If this doesn’t make sense now, it will when we do some examples. In
simple form, the basic syntax structure of an Updategram looks like this:
<ROOT xmlns:updg=”urn:schemas=Microsoft-com:xml-updategram”>
<updg:sync>
<updg:before>
:
</updg:before>
<updg:after>
:
</updg:after>
</updg:sync>
<ROOT>
The three main pieces of the Updategram are <sync>, <before>,
and <after>. The <before> and <after> blocks come in
pairs to create a single transaction. These blocks are contained within a
<sync> block that defines the transaction. The <before> block
is a before image prior to the transaction taking place. The
<after> block is the after image, post transaction having taken place.
In other words, the <before> block is the state of the existing data, the
<after> block is the state of the data after the transaction occurs.
Updategram Insert
Time for some examples. We will start simple and work our way to more
complex examples. Let’s begin with an Insert. Using our model
above, let’s create the following Updategram to insert a new user into our
Users table.
<ROOT xmlns:updg=”urn:schemas=Microsoft-com:xml-updategram”>
<updg:sync>
<updg:before>
</updg:before>
<updg:after>
<Users FirstName=”Brad” LastName=”Pitt” />
</updg:after>
</updg:sync>
<ROOT>
Save this as NewUser.xml where you have been saving everything else, open up
your browser and type the following URL:
http://localhost/SQLXMLTutorial/Tutorial/NewUser.XML
All we are going to get back is a message stating that our Updategram ran
successfully. If you were to now select all the rows from the Users
table, you should see the newly created record of Brad Pitt.
A quick explanation of how this worked is due. We first did not
specify any detail inside the <before> block. Inside our
<after> block we specified some data, specifically a first name and last
name. When we ran this Updategram, SQL Server looked at what it was sent
and noticed that the <before> block was empty and that the <after>
block contained some information. SQL Server understood this as an INSERT
instruction and therefore created a new user record in the Users table.
Realistically we could have completely left out the <before> block and
it would have worked just the same. Good coding practices say to leave it
in for better reading.
Updategram Delete
Let’s do a DELETE. Modify the example above to look like this:
<ROOT xmlns:updg=”urn:schemas=Microsoft-com:xml-updategram”>
<updg:sync>
<updg:before>
<Users FirstName=”Brad”
LastName=”Pitt” />
</updg:before>
<updg:after>
</updg:after>
</updg:sync>
<ROOT>
Save this as DeleteUser.xml and type the following URL into your browser:
http://localhost/SQLXMLTutorial/Tutorial/DeleteUser.XML
Again, all we get back is a message letting us know our statement executed
successfully. Now go back and look at the Users table. The record
containing Brad Pitt is now gone. This operated the same way the previous
example did but in reverse order. SQL Server understood this to be a
DELETE instruction because the <before> block contained some user
information and the <after> block was empty.
Updategram Update
We’ve done and INSERT and DELETE, now let’s do a UPDATE. Modify your
previous example to look like this:
<ROOT xmlns:updg=”urn:schemas=Microsoft-com:xml-updategram”>
<updg:sync>
<updg:before>
<Users UserID=”2” />
</updg:before>
<updg:after>
<Users FirstName=”Brad”
LastName=”Pitt” />
</updg:after>
</updg:sync>
<ROOT>
Save this as UpdateUser.xml. Before executing this in your browser,
open up Query Analyzer and query all the records from the Users table.
UserID 2 has a FirstName and Last name of James Bond, correct? Now type
the following URL into your browser:
http://localhost/SQLXMLTutorial/Tutorial/UpdateUser.XML
Go back into Query Analyzer and re-query all the records from the Users
table. UserID 2 should now have a FirstName and LastName of Brad Pitt.
As was with the last two examples you should get a message stating
successful execution. The only difference with this example was that we
specified data in both the <before> block and <after> block.
SQL Server understood this as an update because the <before> block
specifies the record to be updated and the <after> block specifies how
the record should look after the update.
|
Email TopXML