This is really here partly for my own reference, and partly because it is the answer to quite a few questions on Ask.SQLServerCentral.com. For example, we had recently this query from a chap who was looking to build with one query email address strings and other information.
It sounded to me as though the OP was trying to concatenate email addresses from a table of individual email addresses, something similar to the table created by the following script (very cut down):
DECLARE @AddrTable TABLE ( Addressee VARCHAR(MAX), AddresseeType CHAR(3), EmailAddress VARCHAR(MAX)) INSERT INTO @AddrTable SELECT 'Boss', 'To:', 'Boss@org.com' UNION ALL SELECT 'Deputy', 'To:', 'Deputy@org.com' UNION ALL SELECT 'Auditor', 'BCC', 'Audit@here.com' UNION ALL SELECT 'Supplier', 'CC:', 'Dave@Supplier.com' UNION ALL SELECT 'SupplierSupport', 'CC:', 'Support@Supplier.com' SELECT * FROM @AddrTable
So, the problem is that we need to all the “To:” addresses concatenated and separated by semi-colons, and the CC: and Bcc address similarly. In a single query.
Now, this seemed to me to be an obvious use of the XML Path trick; however, I know the name of the trick, and I know how to find it using Google, but I don’t actually feel comfortable using it without referring to someone else’s work. So…
Using the following query to return the data in an XML format:
SELECT EmailAddress FROM @AddrTable WHERE AddresseeType = 'To:' FOR XML PATH ('')
returns a chunk of “proper” XML data, with angle brackets all over the shop:
However, simply adding the separator we need (a semicolon) to the item we’re selecting causes a very different result:
SELECT EmailAddress + ';' FROM @AddrTable WHERE AddresseeType = 'To:' FOR XML PATH ('')
The results of this look more like something we can use:
We might want to dispose of that final semicolon. We could do this by a combination of two REVERSEs and a STUFF, or we could change the ‘;’ suffix in the select statement to a prefix, use one STUFF, and ditch the two reverses. I know which I would prefer to do (and it’s less code to write, run, maintain and comprehend), but it’s a little bit counter-intuitive. Anyway, we now change the SELECT statement to:
SELECT STUFF ( ( SELECT ';' + EmailAddress FROM @AddrTable WHERE AddresseeType = 'To:' FOR XML PATH ('')) , 1, 1, '')
and that returns:
which looks ideal. So now all we have to do in this particular example is extend the query to provide To:, CC:, Bcc information, and add in the rest of the email information. And what was once implemented by a loop or three in a loop in a client application can now be done in a single query against the server. How’s that for progress?