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 :
242
.NET SQLXML Pitfalls
Even though features like XPath queries and Updategrams make SQL Server
appear like an XML database we need to realize that it’s still the same old
relational database engine under the hood. And the only interface to that
relational engine is SQL. SQLXML does a good job to hide the SQL interface, but
in some situations SQL’s limitations just come right back in your face to
remind you that certain things are not possible. The opposite is true, too.
Certain things possible in SQL work differently in XPath. In the following
section we will examine some of the limitations to help you stay clear of some
of the pitfalls.
XPath queries against an annotated schema are case sensitive. While SQL
queries are case-insensitive the check of the XPath expression against the
schema – like everything else in XML – is case sensitive. If SQLXML cannot
validate the XPath expression against the schema it will return an error and
not execute the query.
You cannot include elements mapping to an image, text or ntext type column
in the <before> section of an updategram. Elements in a <before>
section wind up in the WHERE clause of an UPDATE or a DELETE statement, but SQL
Server 2000 cannot compare against values in columns of these data types.
Unfortunately you may not realize which elements map to a text type column
until you start unit testing.
The W3C standard for XML schemas defines that dateTime values in XML
documents be formatted based on the ISO 8601 standard for dates and times. An
XML value representing midnight on Christmas Day in the year 2002 in the US
central time zone would look like:
This format orders the fields in a way that you can apply
alphanumerical a sorting algorithms to sort dateTime values, which is very
helpful considering the poor support for date types in XSLT. However, SQL
Server does not understand this particular flavor of ISO 8601 formatted dates.
If you try to execute an Updategram containing a properly formatted dateTime
value you will receive an error telling you that SQL Server does not like your
date value – unless you annotate the element in the schema with the datatype
annotation from the urn:schemas-microsoft-com:mapping-schema namespace:
SQLXML needs to match up corresponding elements in the <before> and
<after> sections of an updategram to determine what type of SQL to
generate. It looks for the nodes identified as key-fields in the annotated
schema and also for the updategram-specific id attribute in the updategram. If
SQLXML fails to match up corresponding elements it generates two unrelated
INSERT and DELETE statements to insert the element from the after section and
delete the element from the before section. If you fail to catch a bug like
this during development you could wind up deleting data from your production
database that should not be deleted.
Make sure you always add the key-fields annotation to identify the key
columns of a table. Also, if you know your application is going to change
values in a key column to add the id attribute to the corresponding elements in
your updategram.
