My love for T-SQL's FOR XML PATH ('')

If you read my latest post on RTF-to-PDF conversion, you'll know that our agency, like many, has been working to migrate lots of old emailing processes over to a more modern platform. For us, currently, that means that we're building stored procedures that will take data from our SQL databases and massage it into a more readable form to be emailed out to users (usually with HTML styling). This usually means one thing: Cursors, and LOTS of them! The normal logic goes something like this:

Procedure: Send Order Notifications

1. Create a cursor to loop over all the orders placed
1. Create an inner cursor to grab and list the order details
1. Style details with HTML
2. Create another inner cursor to grab additional details (backorder statuses, exceptions, etc)
1. Style this information with more HTML
3. Create a third cursor to grab all email addresses
1. String these into a To: line
4. Send the email

It's at this point that some of the SQL veterans out these are about to have a coronary and are muttering to themselves about having to look for a new job if they presented that kind of code to their boss. For a process that only runs a few times a day for a small amount of emails, though, this process has worked pretty well for a couple of years now. The problem is maintaining and updating this logic and replicating it for new processes. Quite frankly, my honeymoon with SQL cursors is all but over. Enter my discovery of FOR XML PATH ('') ...

If you wanted, you could drown yourself in MSDN's thorough descriptions of FOR XML and PATH mode, but the key is that its intended usage is to allow you to string together a result set into any XML schema you desire. The gold nugget here is that this same functionality can be user to concatenate a result set together into a string along with any other info you choose! Each of my inner cursor loops could now be replaced with a single SELECT query with the FOR XML PATH ('') clause at the end to smoosh everything together into a single string! Here's a simple example I used for building my list of recipient addresses:

SET @To =(
    SELECT COALESCE(
                    STUFF((
                SELECT DISTINCT ','+EmailAddress
                    FROM UserTable
                    WHERE StoreNumber = @Store
                            FOR XML PATH ('')
                ),1,1,''),
            'Dropbox@ourcompany.com'))
            
The SELECT statement at the core of the code takes all the matching email addresses for the current store number, adds a comma to the front of them, and concatenates them together into one string. The STUFF statement that wraps it then removes the first character of the string, which should be a comma. If, however, there were no results returned, and the string doesn't have a first character, the STUFF statement returns NULL. I catch this case by wrapping it in a COALESCE statement, which will return a default address if no matching ones are found (in this case, our agency's default B2B inbox). I used a COALESCE statement rather than ISNULL. For some reason, using ISNULL in this case returns only the first character of "Dropbox@ourcompany.com" if the result set is NULL, rather than the whole address.

As you can see, this opens a lot of possibilities! What used to take a cursor loop and several lines of string concatenations to build an HTML table for an email body can now be replaced with one statement that strings the field and formatting HTML together. If you find this useful, or have yourself employed FOR XML PATH('') in your own work in some interesting ways, leave me a comment!