String Concatenation using XML Path

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:

<EmailAddress>Boss@org.com</EmailAddress><EmailAddress>Deputy@org.com</EmailAddress>

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:

Boss@org.com;Deputy@org.com;

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:

Boss@org.com;Deputy@org.com

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?

Advertisements
This entry was posted in SQLServerPedia Syndication, Uncategorized and tagged , . Bookmark the permalink.

4 Responses to String Concatenation using XML Path

  1. Orlando says:

    I like the technique, and it performs well. How would you propose dealing with data that could contain angle brackets so they are not escaped? For example:

    DECLARE @AddrTable TABLE ( Addressee VARCHAR(MAX), AddresseeType CHAR(3), EmailAddress VARCHAR(MAX))

    INSERT INTO @AddrTable
    SELECT ‘Boss’, ‘To:’, ‘boss <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

    SELECT STUFF((SELECT ‘;’ + EmailAddress
    FROM @AddrTable
    WHERE AddresseeType = ‘To:’
    FOR
    XML PATH(”)
    ), 1, 1, ”) ;

  2. thomasrushton says:

    Good question. As we can see from the above query, the string “boss ” becomes “boss &lt;Boss@org.com&gt;”

    One thing you could do is, if you’re sure of your data, use the REPLACE function to replace instances of “>” with “&gt;”, and “<” with “&lt;”. Of course, now we get to the situation where we have, quite legitimately, the > string within our input…

  3. Pingback: Which databases were backed up in which backup task? | The Lone DBA

  4. thomasrushton says:

    To fix that problem, use

    SELECT STUFF((SELECT … FOR XML PATH (”), type).value(‘.’,’varchar(max)’),1,1,”)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s