Database Programming: The String Concatenation XML Trick, Revisited


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 (
SELECT n + ‘,’
FROM (
SELECT ‘a<b’ AS n
UNION ALL
SELECT ‘b>a’
UNION ALL
SELECT ‘b&a’
UNION ALL
SELECT ‘b
a’) r
FOR XML PATH(), TYPE
).value(‘.[1]’,‘varchar(max)’)
— =====


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!



-wp





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.

Comments (6)

  1. Anonymous says:

    It’s been quite a while since the LIKE vs ? Puzzle , and I feel like it’s time for another one. Response

  2. Anonymous says:

    Heh. Well I forgot to post the best version of the XML aggregator when I posted the other day. Then Ward Pond tagged in a post on it (here), and pointed out that I was "improving" on an ol …

  3. Anonymous says:

    Never let it be said that Adam Machanic lacks style.. Adam left a comment on yesterday’s revisiting of

  4. Anonymous says:

    Thanks for the plug, Ward!  Wish I knew how to do that trackback thing.  Or something…

  5. Adam Machanic says:

    [test…]Is this thing on?[/test…]

    [wp]: It’s on, but the site is moderating you for some reason this morning, when it hasn’t before..  your "previous post" referenced in your next comment appears to be in the bit bucket, however..

  6. Adam Machanic says:

    Apologies for the test, my previous post didn’t seem to work.

    Just wanted to point readers to a related contest I’m hosting on my blog:

    http://sqlblog.com/blogs/adam_machanic/archive/2009/02/27/t-sql-challenge-grouped-string-concatenation.aspx