FOR XML Explicit or the "Query from Hell" in Yukon: A less likely encounter
Let me try to start a discussion about FOR XML's most complex, feared, but also useful mode: the EXPLICIT mode.
When we designed the EXPLICIT mode, our goals were:
1. To provide a rowset format that could be mapped into almost any XML shape but also could be formulated by a single SQL query.
2. The rowset format was also supposed to be streamable to allow the generation of large documents.
I think the EXPLICIT mode format is highly successful in achieving these goals. There are only a few things it cannot do (arbitrary recursive part-list trees is one of them). However, the preferred SQL expression to generate the rowset format is a formidable “query from hell”. As one of the designers of the rowset format, I have all the respect for people that write and then maintain multi-thousand line EXPLICIT mode queries. But I also understand the usability, maintainability and complexity issues.
In Yukon starting with Beta1, but even more so in the upcoming Beta2, we are now planning on giving people an alternative. In Beta1, we provide you the ability to nest FOR XML queries. So if you need to generate hierarchies, you now can use nested FOR XML expressions (use the TYPE directive though to get XML). In Beta2, we will provide a new PATH mode that will give you even the ability to map columns in the same selection to attributes, elements, text nodes or even complex properties.
There will still be a few cases, where you may want to use the EXPLICIT mode (in order to generate CDATA sections or to use the !xmltext directive), but the ability to nest FOR XML AUTO, RAW and in Beta2 also PATH modes, should make the “query from hell” a much less frequent encounter.
So if you have the Beta 1 or PDC bits, I would like to encourage you to rewrite some of your EXPLICIT mode queries into nested FOR XML queries and send me some feedback. And also look at the PATH mode once it becomes available. I am especially interested in the following types of feedback: performance improvements or losses, missing functionality (no, we will not add a CDATA section generation to the PATH mode) and any other positive or negative feedback.
And yes, I know that I am writing about not yet fully publicly released software. So as punishment, feel free to send me your EXPLICIT mode issues as well :-). Although, as I mentioned in an earlier Blog entry, such questions may be better posted to SQL Server's XML newsgroup.