Analyzing the Static Type of your Query
XQuery inside SQL Server 2005 implements a set of rules that enable us to catch potential problems with the user query at compile time, based on static analysis of the query. The complete rules for static analysis of XQuery expressions are detailed in the various XQuery language and implementation documents. Basically, static typing leverages information we know about the query at compile time e.g. types from an associated XSD document, to find and report potential problems with the query before the query is actually executed. Since the compilation phase of for an XQuery expression is usually a small percentage of the overall execution time, we are able to quickly report errors without forcing the user to wait for a potentially long query execution before reporting a problem.
Most of the time, you will only notice the effects of static typing when your query contains an error that it is able to detect. For example, in the following query, we are attempting to perform a ::self axis with a name test of bar, on a set of nodes already filtered with a name test of foo:
declare @x xml
set @x=''
select @x.query('
/foo/self::bar
')
This query can never return a valid set of nodes and a static typing rule exists that says this should be flagged as an error as it has no useful user scenario. Thus, executing this query results in the following error:
XQuery [query()]: There is no element named 'bar' in the type 'element(foo,xdt:untyped) *'.
The string at the end of the error message is the static type that has caused the problem. In this case, the static type for the result of the first step in the path expression is element(foo,xdt:untyped) *. While most dealing with static types will be due to errors, it is often useful to examine the static type of an expression which correctly executes. In order to do this, you need to use the SQL Server Profiler tool. To use the SQL Server Profiler tool to examine the static types of XQuery expressions inside SQL Server 2005, you need to do the following:
- Run the tool and start a new trace (File->New Trace).
- At this point, you will be asked to specify the location of the target SQL Server instance you wish to monitor. The profiler will then query the instance to to determine what profiling events it supports.
- The next step is a dialog box where you specify the name of the trace file that will be used to store your profile data. The information you see while running the profiler will be persisted in this file.
- After that, choose the 'Events Selection' tab. There will be some pre-defined events selected, I suggest you remove these to cut down on the amount of noise in the output.
- Once you have cleared the default events, select the 'Show all events' and 'Show all columns' options. The event we are interested in is under the 'TSQL' section and is called 'XQuery Static Type' – select this.
- The main column we are interested in for this event is the 'TextData' column – select this and the columns for any other data you are interested in e.g. 'StartTime'.
- Click 'Run' and you are now ready to examine any static types generated by the system.
To see how this works, execute the following query against your instance:
declare @x xml
set @x=''
select @x.query('
/foo/bar
')
You should then see an entry in the SQL Server Profiler which tells you that the static type for your query was:
element(bar,xdt:untyped) *
On a final note, there are some caveats to the viewing of static types in the SQL Server Profiler. Firstly, these profiler events are only generated for a successful query execution i.e. if your query results in an error (even a static typing error), NO profiler event is generated. Secondly, these events are only generated at query compile time. This means that if you execute a query that already exists in the query cache i.e. the same query was executed recently, then no new profiler event is generated since the query is not recompiled.