Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: Sergey Offers Another FOR XML EXPLICIT Syntax For Steve

Database Programming: Sergey Offers Another FOR XML EXPLICIT Syntax For Steve

  • Comments 3
  • Likes

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

Comments
  • I must say I'm sorry about those words. Updated the post with my apologies. <br>

  • Thanks, Sergey. <br> <br>I took no offense; I merely wanted to make sure we differentiated between matters of style vs. matters of right and wrong. <br> <br>One more thing, which I mentioned in a comment I left on your blog: one advantage of the four-part syntax over the two-part syntax is that it matches the syntax for other element declarations. &nbsp;The two-part declaration, while valid, wouldn't match other (named) element declarations and would thus be completely unique in the code base. &nbsp;I believe that this state of affairs renders the four-part construction preferable from a maintainability perspective. <br> <br>The bottom line, though, is that both constructs provide the same result at the same cost, so I see no issue of &quot;right&quot; and &quot;wrong&quot; here. &nbsp;Both constructs work. <br> <br>Thanks for your comments, Sergey!

  • More tidbits from an internal discussion..&amp;amp;nbsp; it'll take us awhile to get to the answer to the titular...

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment