Dan Sullivan pointed this out to me a couple of weeks ago. I'd forgotten about it, but it came back to bite me in a demo last week. It's a feature.
If you have an XML column, variable, or UDF return value that's tied to an XML SCHEMA COLLECTION, any XQuery against that XML will be checked for compliance to the schema as part of SQL query parsing. That's right, *XQuery* code will be checked. Here's an example:
I have an XML SCHEMA COLLECTION named invoice_xsd that contains one XML schema that defines the types in an invoice. The schema contains no definition for a "foo" element, just "invoice-like things" like PartNumber and LineItem. And I have a table
CREATE TABLE invoices (xmlinvoice xml(invoice_xsd))
The following SQL statement:
SELECT xmlinvoice.exist('/foo') FROM invoices
produces an error at *query parse time*. Not the answer "false". The error is:
Msg 2260, Level 16, State 1, Line 2
XQuery: There is no element named 'foo'
Msg 9504, Level 16, State 0, Line 2
Errors and/or warnings occurred when processing the XQuery statement for xml data type method 'exist', invoked on column 'invoice', table 'invoices'. See previous error messages for more details.
The SQL query *doesn't even execute*.
Dan's original "proof" of this behavior was that this function doesn't even catalog, ie, CREATE FUNCTION fails:
CREATE FUNCTION DoSomethingWithInvoice(@a xml(invoice_xsd))
RETURNS int
AS
— other code elided
DECLARE @x XML
SET @x = @a.query('/foo')
Now that's what I'd call *early* validation. I guess its the same as the fact that "SELECT foo FROM invoices" also fails with "invalid column name 'foo'" error. Very cool.
One thought on “Early XQuery schema validation”
Glad you like it. This is what is commonly known as static typing. I am writing a series on that on my blog. You can find links to it at http://blogs.msdn.com/mrys/archive/2004/06/13/154539.aspx
(note, I am still delinquent on the SQL Server specifics).
Comments are closed.