An introduction to static typing.
XQuery in SQL Server 2005 is a statically typed language. This means type errors may be raised during query compilation when an expression returns a value that has a type or cardinality that is not accepted by a particular function or operator. In short, your query will be checked for possible type inconsistencies before it is even run.
For example, let’s consider a simple schema collection like this one
CREATE XML SCHEMA COLLECTION MyCollection AS '
<element name="a" type="string"/>
<element name="b" type="byte"/>
Let’s create a table with a column of type XML constrained by the schemas contained in MyCollection.
CREATE TABLE myTable (xmlCol XML(MyCollection))
Now let’s run a simple query on that column
The server will return the following error.
XQuery [myTable.xmlCol.query()]: There is no element named 'c' in the type 'element(root,#anonymous) *'.
Given the schema that constraints the XML column, the server was able to conclude that an element named ‘root’ cannot have a child named ‘c’. Therefore the server throws an error. The static type of path expression ‘/root/c’ is the empty sequence.
In order to push this example a little further let’s populate the table with some XML instances.
INSERT INTO myTable VALUES ('<root><a>1</a><b>1</b></root>')
INSERT INTO myTable VALUES ('<root><a>2</a><b>2</b></root>')
INSERT INTO myTable VALUES ('<root><a>1</a><b>1</b></root>
Now let’s run a query that, for each ‘root’ element examines the value of its ‘b’ child and returns <true/> if that value is 1 and <false/> otherwise.
SELECT xmlCol.query(' for $i in /root return if (data($i/b) = 1) then <true/> else <false/>')
This query should run successfully and given the instances we used to populate myTable it should return the following three rows
<true /><false />
Now let’s modify this query. Instead of basing the result on the value of element ‘b’ let’s switch to element ‘a’
SELECT xmlCol.query(' for $i in /root return if (data($i/a) = 1) then <true/> else <false/>')
This query fails to compile and the server returns the following error
XQuery [myTable.xmlCol.query()]: The operator "=" cannot be applied to "xs:string ?" and "xs:integer" operands.
Once again the server examined the schema to determine the static types of the operands on each side of the ‘=’ operator. On the right side there is an xs:integer literal, but the expression on the left side would return either an empty sequence (note the question mark occurrence indicator) or an xs:string value. Those two types are incompatible and therefore the server throws an error.
Please note that the string values I chose for the ‘a’ elements when I populated myTable could all easily be converted to xs:integer. It doesn’t matter! The server doesn’t look at the data when compiling the query. Only the schema is used to determine the static types of the expressions involved.
In the next few posts I’ll go through more situations where static typing might cause compilation to fail and I’ll introduce some schema design strategies that can help you avoid those situations.
In the meantime, you might want to read this article by Mike Rorke, where he discusses how to obtain the static type of an XQuery using SQL Server Profiler.
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.