In the past couple of months, I had the opportunity to implement XQuery on 2 separate projects, one using SQL Server 2005 back-end, and the other using Oracle 10g. Having created fairly complex XML queries with multiple joins in both applications, I was able to compare the XQuery implementations of both database providers. Bottom-line... the MSSQL 2005 implementation of the XQuery 1.0 spec is painfully non-standard thereby rendering it utterly useless for even simple joins. Oracle on the other hand, has done a fairly complete and accurate implementation of the spec, making it a pleasure to develop with.
I combed the net looking for accounts and opinions of other people who have worked with both implementations, but couldn't find any. Anyway, here are my top 3 reasons for calling the XQuery support in MSSQL 2005 a massive disaster....
1. Limiting Approach - MSSQL 2005 has implemented the XQuery syntax as an extension method of their XML data type. This approach limits the type of data stores that XML data can be queried from using XQuery (namely, XML type column and XML type variable in TSQL). In Oracle 10g XQueries are written as a separate statements, and can query XML data from the XML type column, XML type variables, but also from XML files within the filesystem, or XML files stored in XMLDB .
2. Where's the XQuery? - Another side-effect of the MSSQL approach to XQuery implementation is that joining disparate XML columns from different tables cannot be done exclusively using XQuery syntax. The only way to achieve a join is by using the "CROSS APPLY" clause to first extract the joining values in relational form and then using those to join the two XMLs. That also adds the extra step of having to use the FOR XML clause to convert the retrieved values back into XML form, aside from the fact that the resulting query does not look like XQuery at all. I believe the reason the XML data has to be initially converted to relational form in order to perform the join is because Microsoft has implemented XQuery support on only the initial tiers of their database engine. The core of their engine probably still only understands and processes data in relational form. That explains the need for CROSS APPLY to convert data from XML to Relational form, only to be converted back into XML form after the join is performed. I have not even dared to wonder what kind of performance hits are entailed in this approach.
Here is an example of a simple XQuery join in MSSQL. It looks nothing like an XQuery statement, but I will let you be the judge of that...
select R.i.value('@name', 'varchar(30)') [@name],
S.s.value('@name', 'varchar(30)') [@section],
R.i.value('@result', 'varchar(30)') [text()]
from Results cross apply xml_data.nodes('/results/item') R(i),
Sections cross apply xml_data.nodes('/sections/section') S(s)
where R.i.value('@section-id', 'varchar(30)') = S.s.value('@id', 'varchar(30)')
for xml path('result'), type
On the other hand, Oracle's XQuery implementation is truely native, allowing the developer to join XML data from disparate XML data sources using the XQuery FLWOR syntax. An example of a similar XQuery join in Oracle 10g...
SELECT XMLQuery('for $i in ora:view("REGIONS"), $j in ora:view("COUNTRIES")
where $i/ROW/REGION_ID = $j/ROW/REGION_ID
and $i/ROW/REGION_NAME = "Asia"
return $j'
RETURNING CONTENT) AS asian_countries
FROM DUAL;
3. Don't LET - According to the XQuery 1.0 spec, the FLWOR (pronounced flower) statement comprises of the FOR, LET, WHERE, ORDER BY and RETURN clauses. Microsoft decided not to implement the LET clause in MSSQL 2005. I guess given the above mentioned limitations, the SQL Server team figured leaving out the LET clause would not cause any additional loss of functionality. But I have read that the team has since relented and implemented it in the upcoming SQL Server 2008 release.
I for one am very disappointed in SQL Server's kludgy and non-standard approach to supporting the XQuery 1.0 standard. All thoughts or comments on this topic welcome.
