The XML changes in the February CTP
[Update 2005-03-09: This posting has been updated with an additional breaking change below.]
As you probably have heard by now, we not only have released a new SQL Server edition called Workgroup that is quite a bargain given the included functionality, but now have also released the February CTP for SQL Server 2005. Tom lists many of the cool additions (Report Builder and some others). Although I was awake earlier than him (well, I am on the East Coast right now, more on that in a separate post). Christa points to where to get the SQL Server 2005 Express CTP. If you have a MSDN Universal, Enterprise, or Professional subscription, you can get it from the MSDN Subscriber Downloads site, and beta program participants can download it from BetaPlace.
But enough of the link orgy. This CTP contains some important changes in the XML area. Among them several performance improvements over Beta2 and the December CTP for XQuery and the XML-DML (more to follow in the next CTPs/Beta), but also new features and some adjustments that may require changes to your code. They are all listed in the CTP Note, but I will list them here as well and give some additional information for some of them.
The following is a list of potentially breaking changes introduced in the February CTP:
- XQuery expressions on the XML datatype now support only Unicode code point collation regardless of the database default collation. Note that this collation is trailing-spaces sensitive and considers "abc" to be different from "abc ".
This change was added since none of the other collations where trailing-spaces sensitive in SQL Server and may have let to wrong results in certain circumstances. Full collation support has been postponed for a future version.
- xs:dateTime/xs:date/xs:time values now require time zone information during schema validation and XQuery cast expressions. As a result, sql:variable/sql:column calls cannot refer to values of the SQL datetime type anymore. To work around this behavior, cast the SQL datetime value to a string type in T-SQL, add a timezone indicator, call the resulting value from sql:variable() or sql:column(), and then use an XQuery cast to cast the value to xs:datetime. This change was made in accordance with customer feedback.
This was the result of the questionnaire that I sent out some time ago. Here is a code sample that shows how to use the SQL datetime with sql:column/sql:variable:
declare
@x xml
declare @t datetime
declare @st nvarchar(30)
set @x = ''
set @t = CONVERT(datetime, '2005-03-01T12:00:00')
set @st = CONVERT(nvarchar(30), @t, 126) + N'Z'
select @x.query('<a>{sql:variable("@st")}</a>')
-
value() method requires a valid xs:datetime value (with or without timezone) when target type is datetime/smalldatetime. [Updated 2005-03-09: This item was added to the original list]
As part of the change to allow xs:datetime values in the value method, we disabled support for lexical representations that are valid SQL datetime values but not valid as xs:datetime. While you were able to say
declare @x xml
set @x = N'<a>2005-03-03 05:03:59.613</a>'
select @x.value('/a[1]', 'datetime')
the February CTP is now raising the error:
Syntax error converting datetime from character string.
Please rewrite your query to
select CAST(@x.value('/a[1]', 'nvarchar(50)') as datetime)
Supporting both would mean that we have to allow the xs:datetime lexical format for general CASTing to datetime. We would appreciate any feedback on whether you want us to support both formats natively when casting to datetime or whether you prefer the current, more conservative approach.
- XQuery semantics alignment:
- Casting xs:float and xs:double to xs:string is now better aligned with XQuery rules regarding when we chose the scientific notation.
- The behavior of the instance of expression, the fn:id() function, and the fn:substring() function has changed as a result of fixing bugs; their behavior is now aligned with XQuery rules. Therefore, some queries may now fail.
The supported subset of instance of should now be aligned with the July 2004 and later working draft. The other functions should be minor bugfixes.
- White space in namespace URIs is now being collapsed to provide consistent semantics. This rule has been added to the latest XQuery Working Draft. Note that you should NOT use white space in URIs anyway!
- We now raises a static error when text() or node() is applied to elements with simple typed content. This is part of our current implementation restriction that we do not unfold typed values back into text nodes. This may change in a future release and feedback is appreciated.
- XQuery syntax alignments:
- Escaped closing curly brackets are now required inside a constructed element's content.
- Default element namespace declaration can no longer include an equal sign ('='). The new syntax for default element declaration is declare default element namespace "uri";.This is probably one of the changes that impact most users (at least those that use namespaces). We already supported the correct syntax above and now removed the old, non-standard syntax.
- The isnot function has been removed from the XQuery standard and hence from the implementation. Use not($x is $y) instead.
- Built-in namespaces for fn: and xdt: prefixes have been updated to match the W3C July 2004 XQuery 1.0 Working Draft. This should not affect anyone.
- The behavior of static namespace bindings has changed as a result of fixing some bugs. Therefore, some queries may now fail.
- Upgrading to the February 2005 CTP release disables XML indexes. These indexes must be rebuilt with the ALTER INDEX… REBUILD command before these indexes are used in queries and maintained during data modification. The primary XML index on an XML column must be rebuilt before the secondary XML indexes on the column. Primary XML indexes are those indexes in the sys.xml_indexes catalog view that have the value NULL in the "using_xml_index_id" column.
This item is probably the most important one for those of you that have existing applications and use XML Indices.
The following is a list of non-breaking changes and extensions for the February 2005 CTP release (including some December 2004 CTP release items, some that my weblog readers may already know about):
- A new CONVERT option 1 has been added to enable whitespace preservation, even when xml:space="preserve" is not specified, when converting from a string or binary type to an XML data type.
- SQL Server now disables all DTD processing by default during casting to XML, and a new CONVERT option 2 has been added to enable limited DTD processing when casting from string types (added in the December CTP release) and from varbinary (added in the February CTP release).
- The value() method casts to SQL type datetime by removing timezone information if the timezone is Z (previously raised an error).
This was an often requested feature. Try the following statements:
declare
@x xml
set @x = N'<a>2005-03-01T12:00:00Z</a>'
select @x.value('xs:dateTime("2005-03-01T12:00:00-01:00")', 'datetime') -- a xs:datetime value
select @x.value('/a[1]', 'datetime') -- an untyped/string value
- The value() method can cast xs:base64Binary encoded values to SQL varbinary (added in December CTP).
- XML Schema alignment: We implemented schema errata E2-25: value space of xs:language.
- You can use SQL Profiler to look at the "TSQL/XQuery Static Typing" event trace that provides the static type of the expression in an XML datatype method invocation (added in December CTP).
- The sqltypes XML schema is now part of the built-in system XML schema collection sys.sys as is the xml namespace.
- XQuery semantics alignments:
- Casting to xs:float/xs:double: positive and negative overflow now produces INF/-INF.
- fn:distinct-values() no longer requires atomization.
The next CTP will contain many more XQuery alignment items, the WITH XMLNAMESPACES() clause to add namespace support, many additional performance enhancements and more.