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: The String Concatenation XML Trick, Revisited

Database Programming: The String Concatenation XML Trick, Revisited

  • Comments 6
  • Likes

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
  • Thanks for the plug, Ward!  Wish I knew how to do that trackback thing.  Or something...

  • [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..

  • 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

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

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

  • 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 ...

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