Continuing our discussion of XML in SQL Server 2008, SQL Server 2005 alowed us to use XQuery to query XML data in an XML data type. You can use the FLWOR statements of for, where, order by, and retun, but not LET. SQL Server 2008 now has support for LET, which is used to assign values to variables in an XQuery expression. For example we are going to take the XML below and count the number of sessions per speaker using the count XQuery expression and assign it to a variable via LET.
declare @xml xmlset @xml='<Speakers> <Speaker name="Stephen Forte"> <classes> <class name="Writing Secure Code for ASP .NET "/> <class name="Using XQuery in SQL Server 2008"/> <class name="SQL Server and Oracle Working Together"/> </classes> </Speaker> <Speaker name="Richard Campbell"> <classes> <class name="SQL Server Profiler"/> <class name="Advanced SQL Querying Techniques"/> <class name="SQL Server and Oracle Working Together"/> <class name="From 1 Server to 2"/> </classes> </Speaker></Speakers>'SELECT @xml.query('<Speakers>{for $Speaker in /Speakers/Speakerlet $count :=count($Speaker/classes/class)order by $count descendingreturn<Speaker>{$Speaker/@name}{$Speaker/count}<SessionCount>{$count}</SessionCount></Speaker>}</Speakers>')
The result is shown here, notice that Richard sorts on top since we used the descending order to our order by and ordered by the expression we created via the LET statement. This is very helpful if you are manipulating simple XML and need to do a mini-transform via XQuery. I used to use the RETURN statement all the time to make slight manipulations to my XML, so I was waiting for LET. While this is the only addition to XQuery, it is a much needed and welcome one.
<Speakers> <Speaker name="Richard Campbell"> <SessionCount>4</SessionCount> </Speaker> <Speaker name="Stephen Forte"> <SessionCount>3</SessionCount> </Speaker></Speakers>
