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.