UPDATED 27 February 2009 for spelling
I’ve got to pay more punctual attention to my comment pool..
RBarryYoung’s movingsql.com will be on my blog roll shortly after I get this posted (second attempt; first thwarted by a laptop hang. I have suspended unit testing of SQLRAP 2.5 changes until I’m done.) for two very good reasons. The first involves the comment he left, which I’ll be getting to presently because I want to make sure you see it. The second involves what I found on his blog when I followed his URL: a post and accompanying presentations called There Must Be 15 Ways To Lose Your Cursors. I laughed. I clicked. I now have one more thing to add to my list of things to do..
On to the T-SQL he left.. Back in March of last year, there were a number of posts related to The Technique That Lance Found, a/k/a The String Concatenation XML Trick. Unfortunately, RBarry came into the conversation at the Concatenation link, when the ultimate evolution of the code may be found at the Trick link. This is particularly unfortunate because the Concatenation link was essentially the ugliest alternative of the lot, a worst-case scenario until we found something better.. which we did.
Anyway, that code is intended for rendering an indeterminate number of strings arising from a parent-child relationship. RBarryYoung’s alternative, is, I believe, nearly equivalent to the final evolution of last March’s analysis with the exception that it deals with a single level of data. For that reason it strikes me as a clearer instantiation of the concatenation portion of the concept (as opposed to the parent/child portion):
--Transformed FOR XML String Concatenation:
SELECT n + ','
SELECT 'a<b' AS n
FOR XML PATH(''), TYPE
He’s also in the midst of an analysis supporting our shared contention that this technique is the most efficient available for concatenating strings.
Thanks for your comment, RBarry, and for sharing your code here!
this copyrighted material was originally posted at http://blogs.technet.com/wardpond.
the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.
the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.