Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First posted :
03/24/2008
Times viewed :
305
SQLXML - .NET Updategrams
SQLXML exposes a fully XML-based data access API allowing to INSERT, UPDATE
and DELETE data from the database. The API is not based on a W3C standard and
it is very different from SQL, but it is not hard to understand. The concept of
the API is to send specially formatted query templates to SQL Server. The
templates define the modifications to execute by identifying the items to
modify and their state after the modification. The template format is called an
Updategram and its structure is outlined below.
2 Updategram
Format. Optional elements are shown in square brackets.
Each <sync> block inside an Updategram defines an atomic set of
changes, i.e. all modifications succeed or fail together. An Updategram can
contain multiple <sync> sections if necessary. A <sync> block
identifies the rows we need to manipulate in the <before> section. The
<after> section lists the changes to carry out. Similar to regular batch
updates, the <before> and <after> sections can list more than one
XML element to modify. In order for SQLXML to match up the elements in the
<before> and the <after> sections we have identify the matching
ones either explicitly through the id attribute or include the key-fields
annotation in the mapping schema.
Expressing insert and delete operations with the Updategram syntax is very
simple. When we insert data we can simply omit the before section or leave it
empty, while the after section contains the data we want to insert. Likewise,
we can delete data from the database by omitting the after section or leaving
it empty.
Behind the scenes, SQLXML does nothing but convert the Updategrams to
INSERT, UPDATE and DELETE statements. When performing and insert operation the
Updategram results in a SQL INSERT … VALUES statement containing all items from
the <after> section. For update and delete operations, SQLXML will
compose UPDATE or DELETE statements with WHERE clauses containing the elements
and attributes specified in the <before> section of the Updategram, as
shown in the following diagram:
Figure 15.3: SQLXML turns the components of an Updategram into a SQL statement.
The more details you include in the Updategram the more specific the
generated where clause is going to be. It’s important to remember this
because we cannot explicitly leverage SQL Server’s locking mechanisms when we
query data with SQLXML. Since the query results are not associated with a
persistent connection, we cannot hold any locks while our application modifies
the data and writes it back to the database. That is a good architecture with
respect to throughput and database scalability, because nobody has to wait for
outstanding, but it poses certain challenges to ensure that two users do not
overwrite each others changes. The only way to guarantee that this does not
happen is to include every column in the <before> section, i.e. the WHERE
clause of an update (or delete) statement. In terms of an Updategram this means
we have to decide which elements we need to include in the <before>
section when sending Updategrams to the database. If SQLXML does not find a
record matching the data in the <before> section of an Updategram it will
always return an error to help us develop applications that can properly handle
these concurrency issues.
Of course Updategrams are not limited to operations on a single SQL Server
table. We can specify entire XML trees in the <before> and <after>
sections and SQLXML will translate them into an SQL statement that spawns all
the tables referenced by the XML types in the tree. In these cases it is extra
important to define the relationships between the tables with the
<relationship> annotation and identifie key columns with the
<key-fields> annotation in the mapping schema. SQLXML determines the order
in which the changes to the database need to occur to avoid violating any
constraints in the database. Imagine you need to insert an XML document with
nested elements OrderHeader and OrderItem with the following Updategram:
If you have a foreign key constraint on the OrderID from the OrderItem to
the OrderHeader table then the correct order of the two inserts is very
important. SQLXML needs to insert the row in the OrderHeader table before it
inserts the row in the OrderItem table or SQL Server will abort with a
constraint violation because the foreign key is not valid. With the
<relationship> annotation SQLXML executes the SQL statements in the correct
order. But that is not the only where we need to make sure the key-fields
annotations are in place. SQLXML also requires the key-fields annotation to
match up the corresponding elements when the <before> and <after>
sections contain multiple elements of the same type. Matching up the elements
on their key values is difficult though, if we are trying to change the key
value with the updategram. In the next section show a solution to this problem.
