Early XQuery schema validation

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

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.