Microsoft reaches standardization with the W3C XQuery recommendation with the addition of the LET clause in the next release of SQL Server, SQL Server 2008.

Here is an example of using the LET clause as part of the XQuery syntax.

CREATE TABLE customerData(        ID int IDENTITY(1,1) NOT NULL,        customerDocs xml NOT NULL,        updated datetime NOT NULL DEFAULT (getdate()),  CONSTRAINT [PK_customerData] PRIMARY KEY CLUSTERED (        [ID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO     INSERT INTO customerData(customerDocs)        VALUES(N'<?xml version="1.0"?>        <customers>               <customer FirstName="Bob" LastName="Hayes" Zipcode="91126" status="current">                      <order ID="12221" Date="July 1, 2006">Laptop</order>               </customer>               <customer FirstName="Judy" LastName="Amelia" Zipcode="23235" status="current">                      <order ID="12221" Date="April 6, 2006">Workstation</order>               </customer>               <customer FirstName="Howard" LastName="Golf" Zipcode="20009" status="past due">                      <order ID="3331122" Date="December 8, 2005">Laptop</order>               </customer>               <customer FirstName="Mary" LastName="Smith" Zipcode="12345" status="current">                      <order ID="555555" Date="February 22, 2007">Server</order>               </customer>        </customers>') GO   -- FLWOR with LET operator SELECT customerDocs.query('        <CustomerOrders> {        for $i in //customer        let $name := concat($i/@FirstName, " ", $i/@LastName)        order by $i/@LastName        return               <Customer Name="{$name}">               {               $i/order               }               </Customer>        }        </CustomerOrders> ') FROM customerData

It would be great to create a library of XQuery strings maintained in the database that developers could use and DBAs could manage. Unfortunately, you are going to have to jump through some hoops to make that happen in SQL Server 2005. The problem originates from the fact that the XML datatype "query" method can only take a literal string value.

This also causes problems if you want to use dynamic queries. You can create a SQL statement involving a dynamic query, but what you have to do is piece together the entire query as a string that is called in the dynamic SQL stored procedure "sp_executesql". But, you cannot pass the XQuery as a variable in dynamic queries either.

Below are some examples of what works and what does not.

The first example shows a typical XQuery call, then one that will not work that uses a variable passed to the query method.

-- This works! DECLARE @myResult xml;   SELECT @myResult = Study.query(  '   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ' ) FROM JustXML WHERE ID = 1;   SELECT @myResult; GO   -- This does not (PASSING VARIABLE INSTEAD OF STRING LITERAL)   DECLARE @myResult xml; DECLARE @myXquery nvarchar(500);   SET @myXquery =  '   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ';     SELECT @myResult = Study.query(@myXquery) FROM JustXML WHERE ID = 1;   SELECT @myResult; GO

Here are two examples, one that works and one that does not, using dynamic SQL queries.

-- Using Dynamic Queries with XQuery -- --------------------------------- -- THIS WORKS (dynamic sql) DECLARE @myXquery nvarchar(255);   SET @myXquery =  N'   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ' DECLARE @myDynamicSql nvarchar(500);   SET @myDynamicSql = N'SELECT Study.query(''' + @myXquery +        ''')  AS MYRESULT FROM JustXML WHERE ID = 1'   EXECUTE sp_executesql  @myDynamicSql;   -- But this does not!!! DECLARE @myXquery nvarchar(255);   SET @myXquery =  N'   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ' DECLARE @myDynamicSql nvarchar(500); DECLARE @ParmDefinition NVARCHAR(500);     SET @myDynamicSql = N'SELECT Study.query(@thisXquery)  AS MYRESULT FROM JustXML WHERE ID = 1' SET @ParmDefinition = N'@thisXquery nvarchar(255)';     EXECUTE sp_executesql  @myDynamicSql, @ParmDefinition,                        @thisXquery = @myXquery;

For those who attended my recent presentation, "XQuery for DBAs and Developers", at NovaSQL, you can find the files here.

NovaSQLNov2007.zip (958.63 KB)

NovaSQL is the Northern Virginia SQL Server User Group. I would like to thank Jim Rotan and Jeremy Kadlec for the invitation to speak.

OK, I'm a little slow. But, here are the two presentations I made at the Richmond Code Camp two weeks ago. These are in Microsoft Powerpoint.

XQuery for DBAs and Developers (Powerpoint)

Richmond Code Camp XQuery for DBAs and Developers.pptx (980.39 KB)

XQuery SQL Demonstration File (.SQL)

XQuery Presentation.sql (42.59 KB)

Dr. Jekyll and Mr. Hyde; SQLCLR for DBAs and Developers (Powerpoint)

SQLCLR Richmond Code Camp Oct 6.pptx (620.63 KB)

Well, I have really done it now. I'm giving two presentations tomorrow at the Richmond CodeCamp. Andy Leonard is putting on another show, and if this one is as good as the last, everyone coming should be in for a real treat.

See you all Saturday!

Located below are the SQL, data and VB project files from my presentation at the Richmond Code Camp

RichmondCodeCampXquery.zip (12.6 KB)

Here is my powerpoint presention. It is more than a little light because the focus of CodeCamp is the CODE!

Richmond Code Camp XQuery Tips and Tricks.ppt (2.34 MB)

I would like to thank Andy Leonard, Susan Lennon, and the rest of the Richmond crew for a well run operation.

When working on code developing a SQLCLR stored procedure for my upcoming presentation at the Richmond Code Camp, I ran into this problem with the SqlPipe output.

Here is my code:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub procExtractSubjectDetail ()
'Return a string of Subject IDs/XML seperated by a ";"
Try
'Connect to database, notice the context connection
Dim Command As SqlCommand = New SqlCommand
Command.Connection = New SqlConnection("Context connection=true")
Command.Connection.Open()

'Generate the SQL query
Dim strSQL As String = "SELECT ID, Study FROM StudyDetail"

'Attach query to command object
Command.CommandText = strSQL

'Execute command object and get results back in a SQL reader object
Dim reader As SqlDataReader = Command.ExecuteReader()

'Create an efficient stringbuilder to hold the results
Dim Subjects As StringBuilder = New StringBuilder

'Add an emtpy string
Subjects.Append("")

'Collect the results
While reader.Read
'String output size limit is 4kb
'so let us restrict our output for this demo
Subjects.Append(reader.Item(0).ToString() + "/")
Dim sXml As SqlXml = reader.GetSqlXml(1)

Subjects.Append(sXml.Value.ToString)
End While

'Close and release the reader
reader.Close()
reader = Nothing

Dim strResult As String = Subjects.ToString()

'Is it too big?
Dim iSize As Integer = strResult.Length

'Send the results
SqlContext.Pipe.Send(strResult)

Command.Connection.Close()

Catch ex As Exception
'Catch the error and resend it. You could add more error code handling here.
Throw New ApplicationException("An error occurred. " + ex.ToString())
End Try

End Sub

--

This was failing when I ran the code. The issue? SqlContext.Pipe.Send(STRING) only allows a string size of 4k bytes. My output was over 7k.

XQuery has now reached standardization by the W3C. The XML Team at Microsoft is now asking for input on whether there should be a standalone XQuery implementation in the .Net Framework in their blog entitled Standalone XQuery Implementation in .NET?.

My posted response is below:

I, also, would like to see an XQuery implementation within the .Net Framework.

If available, I would be more inclinded to use XQuery over XSLT, particularly in a more dynamic setting to extract data, and then to shape the output before consuming within an application.

While the XQuery implementation in SQL Server 2005 is excellent, it is still only a subset of the standard, for example, the ability to query against multiple documents. And, I would prefer to see the implementation as native to the .Net Framework, rather than as a commercial add-on.

The differences between what could be and what is available now are two fold. First, the current implementation resides on server-side, within the SQL Server itself. A .Net Framework (System.XML.XQuery) implementation would allow the developer to choose where the query would be executed. Additionally, you would be able to query across multiple documents, which you cannot do in the SQL Server 2005 version.

I have found the XQuery syntax to be easier to craft to extract XML content and shape the output than using XSLT. I think there is a strong incentive to have the XQuery implementation because it can provide some features that may either be missing or are easier to use than SQL Server XQuery, LINQ or XSLT.

I vote "Yes" to XQuery in the .Net Framework.

I gave a presentation on XQuery at the NovaSQL user group that meets in Vienna, Virginia.

Jeremy Kadlec and Brian Moran run a great program. The turnout was very good, too. Lots of SQL experts.

Do you know how you can tell if you are giving a presentation to people that are SQL experts? You can tell that they are when you try to do a SELECT statement, and you get the dreaded Invalid object name error. Before you can blink you hear twenty-thirty voices saying, "You're in the Master database!"

I had a great deal of fun talking about XQuery with a very savvy crowd.

Attached to this post are my PowerPoint slides from the presentation.

Looking for some basics on XQuery expresssions in SQL Server 2005? Here is a great guide that is right in the books-on-line (BOL).

http://msdn2.microsoft.com/en-us/library/ms189919.aspx

This guide, for example, is useful if you want to find out what the different types of comparison operators there are. According to the guide, there are four.

• General comparison operators
  
• Value comparison operators
  
• Node comparison operators
  
• Node order comparison operators
  

Note that the Value operators ('eq', 'ne', 'lt', etc.) work on singleton atomic values. You can use the General operators instead of just the Value operators for comparison.

So, I didn't make TechEd, but I'm on my way to Microsoft's PDC.

I'm looking forward to seeing what's going on, especially in the XQuery area. Michael Rys is giving the following presentation which I'm hoping to attend:

DAT405  SQL Server 2005: Deep Dive on XML and XQuery

You can find out more about PDC here. Sorry, it is already sold out.

Interesting article in the XML Journal about XQuery.

For those interested in why XQuery is not in .NET Framework 2.0, Arpan Desai's blog has a short summation here. Also, one of the outstanding issues is that XQuery will not reach Recommendation status by W3C until sometime next year while Framework 2.0 is set to be released this year.

Not that I know, but I would venture to guess that we will see XQuery in the .NET Framework with an updated version (2.1?) which will probably be available around 2007.

You can find the files for my presentation here:

Presentation Slides

Demos

Andrew Duthie, Microsoft, put on a great show. There are Code Camps being organized all over, and this one last Saturday was hosted at Microsoft's Reston offices. 

This wasn't a marketing session, it was about developers talking to developers. We had some top-flight folks presenting. Brian Noyes, Sahil Malik, Vishwas Lele, Julie Lerman, Jonathan Cogley, to name a few. We also had many local developers give presentations, so it was a good mix of top flight presenters/MVPs and local or first time presenters.

This whole event was free. Similar type conferences cost about $400 per day, so those that attended in the developer community really made out. There is talk of doing more of these, and also doing them in other locations (like Roanoke).

User group representatives were out in force, too. We had members and representatives from Roanoke, Richmond, Hampton, Baltimore, Vermont, and nearly every .NET group in the DC area (NovaSQL, CAPAREA.Net were well represented). We even had Cold Fusion folks attend. And INETA was well represented with Julie Lerman (congratulations on being named to the INETA board) and Scott Lock, Regional INETA director for the Mid-Atlantic region in attendance. 

I gave a presentation on XQuery for the Data track (we had 5 different tracks). Good group of presenters: Sreedhar Koganti, Carney Clegg, Julie Lerman, Sahil Malik, and Jeff Schoolcraft in our group. Carney and I should have switched the order of our presentations because he showed some good stuff on how to consume XML and make it show up in a grid. Using XQuery, I showed how to extract data out in an XML format. It would have really been interesting to show me pulling data from SQL Server 2005 in XQuery, and Carney's demonstration showing how it could be consumed and presented at the interface level.

I gave away a red-handled crescent wrench to an attendee that could name the two types of XQuery calls that can be made in SQL Server 2005 (XPath and FLOWR) similar to the one in my first blog entry.

Anyway, hats off to Andrew Duthie for putting on the Code Camp. I think he maxed out his expected attendance goals. Over 300 signed up and over 200 attended (not bad for the Saturday before Mother's Day).