Michael Rys

Some comments on “Kiss the Middle-tier Goodbye with SQL Server Yukon”

In March, Klaus Aschenbrenner published an article on SQL Server 2005's XML support titled “Kiss the Middle-tier Goodbye with SQL Server Yukon” (not that I would recommend that :-)). It is a pretty good, general overview of the Beta1 functionality. Since I started to comment on such articles, let me add some comments and clarifications to this article. I will leave out nitpicks on missing parens in the code and typos, and point out some differences introduced in Beta2.

This article, as many others, has the potential of introducing some of the previously mentioned confusions. Here are some additional, technical comments:

1. XML Schema management has changed in Beta2. Instead of registering a single XML Schema and using its target namespace to constrain an XML datatype, you will create an XML Schema collection instead that can contain more than one schemata using a slightly modified DDL statement of the form CREATE XML SCHEMA COLLECTION [SchemaColl] AS .... The XML column can then be constrained with the collection name such as XML(SchemaColl).
2. I don't know what the value of the table mapping XSD to SQL datatypes is. The value() method casts to the given SQL datatype. That table may indicate an internal storage representation, but it is not useful for what you can cast a value to. For example, you certainly can cast an xs:datetime value to a SQL string type and do not need to map it to a varbinary.
3. The XML DML statement “update ... to ... “ has been changed for grammar reason's in Beta2 to be “replace value of ... with ...”. Also, stricter static typing rules have been added. And the original “update value of” had the same semantics as “update”.
4. Regarding the mentioned limitations:
1. XML can't be casted to text or ntext.
   This is by design. TEXT and NTEXT are being deprecated and replaced by (n)varchar(max). So there is no need to cast to these two types.
2. An XML column can't be part of a primary—or foreign key constraint.
   Correct. However note, that this would require a clear understanding of when two XML instances are the same. A topic that even the XQuery WG is struggling with.
3. Only strings can be casted to XML.
   Starting with Beta2 (even with earlier internal builds), you can also cast binary types to XML (this allows better transport of instance based encoding information).
4. An XML column can't be used in a GROUP BY statement.
   This as 2 (and 5 if I understand it correctly) all come from the fact that we have no comparison operation defined on the XML datatype. And neither has the ISO SQL-2003 standard. The reason: there is no clear agreement how such an operation should be defined. Should you include or exclude comments? PIs? Should you compare typed values or string values?
5. An XML column can't be part of an index.
   Obviously you can define an index on the XML column. And since there is no comparison defined, you cannot use it as an index key.
6. It is only possible to create 32 XML columns per table.
   We never had such a limit.

The XML schema limitations:

1. Annotations (like comments) are not stored in the metasystem of Yukon.
   This is correct. Although we plan to support this in a future version.
2. The XSD ID attribute is not supported.
   This is incorrect. We support ID/IDREF(S). We do not currently support key/keyrefs.
3. Default values can't be longer than 4000 unicode characters.
   This is correct. If there is a need for longer default values, we will look into adding support in a future release.
4. XML schemas can't be converted to their origin state. Therefore, you need to manage XML schemas separately.
   They can be converted into a schema that is equivalent to the original one w.r.t. the supported validation. Support of the XML schema collections as collections of XML data type instances representing the original schemata will be looked at for a future release (where concepts may be added that would make this a more natural mechanism).

In summary, Klaus' article is a good overview on the SQL Server 2005 Beta1 XML functionality. My comments above point out some small mistakes and provides some information about where we improved the design from Beta1 to Beta2. Thanks to Klaus for his good write-up.

posted on Monday, May 10, 2004 9:15 PM by mrys