Database Programming: Sergey Offers Another FOR XML EXPLICIT Syntax For Steve
If you've worked with databases long enough, you develop a set of truisms that you carry around in your head. One of mine, related to building T-SQL syntax, is, "there are at least 87 ways to skin any particular cat." While we're still 85 methods short of that benchmark in our current discussion, the point still holds.
Sergey Belov blogs on ASP .NET, .Net, and SQL at Coder's Perspective. He left a comment on Friday's post regarding FOR XML EXPLICIT (as well as a post on his own blog) offering the following alternative syntax:
SELECT
1 AS tag,
0 AS parent,
NULL AS [Product!1!],
NULL AS [Category!2!rank],
NULL AS [Category!2!]
UNION ALL
SELECT
2,
1,
NULL,
'0',
'Category Name'
FOR XML EXPLICIT
This syntax produces the same result as the recommendation offered in Friday's post, so the Element directive is not necessary, as Sergey's comments state. However, I can't endorse Sergey's statement on his blog that the use of the "Element" directive in my first recommendation is not "proper".
It seems to me that any syntax that produces the desired result with a close-to-optimal query plan is "proper", and that any further fine tuning devolves quickly into the relative merits of personal style. For instance, I don't write ANSI-92 style JOINs anymore (SELECT a.Col1, b.Col2 FROM TableA a, TableB b WHERE..) and if I see any in a system I'm supporting, I'll suggest they be changed. That doesn't make ANSI-92 JOINs incorrect as a technical matter (although they produce inconsistent results in certain OUTER JOIN scenarios), it just means that as a matter of personal style I don't like to code them.
So, while I agree with Sergey's basic premise (that the "Element" directive is unneccessary), it's words such as "incorrect" and "proper" that give me pause. Both syntaxes return the desired results, and I see no difference in the query plans they generate. To my mind, then, this issue comes down to a personal style point.
Given that Pond's Tenth Law states that "your code is a communication with someone else, who will likely come after you are gone," my preference is to be as explicit as possible in my coding and my comments. For that reason and that reason alone, I prefer [Category!2!!Element] to [Category!2!]. The constructs will work identically, but I believe the former provides more insight into the structure of the XML document than the latter to those who might maintain the code in our wake.
So, while I don't agree with the statement that the approaches I've provided are "incorrect", I do agree that there is another syntax available, requiring fewer keystrokes to implement, that will provide the same result. Either way, we get what we're after -- this particular cat is skinned.
Sergey, thanks much for your comment and your research. I'd be interested from hearing from other denizens of this space which alternative they prefer in this scenario. Would you write the most compact possible code, or would you opt for a syntax that offered more insight into the structure of the XML?
-wp