Michael Rys

Using the XQuery methods in CHECK constraints

In a recent weblog posting, I outlined how you can use XQuery expressions inside CHECK constraints. Unfortunately, we identified a problem that requires us to mandate that any XQuery expressions used in a CHECK constraint are wrapped in T-SQL user-defined functions. This will be enforced starting with an upcoming CTP release (not the next one, but probably in the Beta3 timeframe), but you probably should start rewriting your queries already today. Here are some examples:

The following shows an example, where we have a value-based constraint on an XML column:

create function HasOnlyCheapParts(@x xml)
returns bit
as
begin
  return @x.exist('//part[every $i in ./subcomponent/@price satisfies xs:decimal($i) < 20.0]')
end;
go

create table t (
  item_id int primary key identity(1,1),
  item_assembly xml not null check (dbo.HasOnlyCheapParts(item_assembly) = 1)
);

The following takes one of the two expressions to check for document-ness and rewrites it using the UDF approach:

create function isXMLDocument(@x xml)
returns bit
as
begin
  return 
    case @x.value('count(/*)', 'bigint') 
    when 1 then ~(@x.exist('/text()'))
    else 0
    end
end;
go

create table t (
  xcol xml check (1=dbo.isXMLDocument(xcol))
);

As you can see, you do not loose functionality. The reason for requiring this rewrite is the same as for requiring the use of UDFs for using the XML data type methods in other contexts: SQL's execution engine expects a scalar expression. While the XML data type methods look like a scalar expression, it is actually translated into a more complex expression underneath the covers that violates some of these internal expectations.

posted Friday, April 01, 2005 12:23 PM by mrys with 2 Comments

Next version of SQL Server will start deprecating SQL in favor of XQuery

Based on the recent industry research figures which show that up to 80% of database programmers will use XQuery over the next two years, the SQL Server team has decided to start deprecating support for SQL and to solely focus on top-level XQuery support starting with the next SQL Server version (after 2005 that is) code named “Nirvana”. The normal deprecation schedule will be applied. An undisclosed source inside the programming language community has also heard rumours that XQuery will be extended with procedural features and will be replacing VB and C# as the main .Net language in a future - yet to be named - release of the .Net language framework (given our latest communications, I doubt this however).

Industry observers are not necessarily surprised that SQL Server is making this move, but they are surprised at the speed this occurs, given that SQL Server 2005 is only adding a subset of XQuery and that the W3C standard is not expected to be finalized until Q1 of 2006.

....

I hope you enjoyed this year's April Fool's joke :-). Obviously we are NOT deprecating T-SQL anytime soon. Although top-level XQuery would be nice in addition, if you ask me...

[Update 2005-04-09: Removed link on request. Added resolution as April Fool's joke]

posted Friday, April 01, 2005 10:38 AM by mrys with 1 Comments

Some upcoming events on XML, SQL Server and databases where I will be speaking

Over the next couple of months, I will be speaking/presenting at a couple of occasions.

MSDN SQL Server 2005 WebCasts

MSDN is running a new series of SQL Server 2005 and VS 2005 webcasts. Here are the ones that are interesting from an XML and SQL Server point of view and are being presented by either myself or Shankar. They require registration that can be done by following the links below.

Tuesday, April 5, 2005 3:00 P.M.–4:00 P.M. Pacific Time
MSDN Webcast: Managing XML Data on the Database with SQL Server 2005 and Visual Studio 2005 (Level 300)
I will be presenting information about the XML datatype (not much on XQuery or optimizations though since that will be covered separately), XML Schema Collection, FOR XML PATH mode, how to use XML inside CLR user-defined stored procs (how to use XSLT), and give an update on SQLXML 4.0.

Friday, April 15, 2005 11:00 A.M.–12:00 P.M. Pacific Time
Developing Smart Client Applications Using SQL Server 2005 Native XML Support (Level 200)
Shankar Pal will discuss some of the design considerations for developing applications using XML data type such as the database table design using property promotion, design of XML schemas, writing queries, client-side access using ADO.NET, and XSL transformation using SQL/CLR.

Tuesday, April 26, 2005 10:00 A.M.–11:00 A.M. Pacific Time
Making the Most of XQuery with SQL Server 2005 (Level 300)
I provide an introduction to XQuery and the data modification language as implemented in SQL Server 2005, and show you how to get the most from XQuery. I will provide an introduction to how to optimize XQuery (in preparation to the last presentation) and how to write some common XQuery expressions to get good performance.
 
Thursday, April 28, 2005 10:00 A.M.–11:00 A.M. Pacific Time
Optimizing and Troubleshooting XML Applications at the Server (Level 300)
Shankar will provide a look at techniques for optimizing applications using the XML data type by first examining the XML index mechanism and then showing how to design and use XML schemas in query optimization, property promotion, and writing efficient path expressions. He will show how to troubleshoot XML applications by analyzing semantic correctness of queries, monitoring performance, and using tools such as Showplan.

SIGMOD 2005

I will present a tutorial with presenters from IBM and Oracle and Donald Kossmann as the neutral M.C. on XML and Relational databases which should give an interesting insight into how the “big three” are incorporating XML into their system both at a functional and architectural level. I will put up links to the presentations and papers as soon as they go online.

TechEd USA 2005