FOR XML PATH Changed The Way I Think About T-SQL #TSQL2sday


The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.

And that same thing probably happened the next several times, too.

But eventually, I took a deep breath and read the code more closely to understand what it was doing. I don’t know whose example I originally found, but I’m going to point you to Dave Valentine’s post on how to do it because he’s got a clear example that even has a database diagram! Way to go, Dave.

The basic idea behind FOR XML is that you can take a regular query’s results, and export them to XML format.

Normally, XML looks kinda like this:

With FOR XML PATH, you can define how SQL Server formats the output. For example, if you wanted a list of just the names, and you wanted them in a comma-delimited format, you might want:

Nothing about that is even remotely close to valid XML, but the FOR XML PATH technique lets you abuse the intended usage of the feature and get something completely different out of it.

To learn how to do it, check out Dave’s post, which includes this example:

The a-ha moment for me was realizing that instead of building a result set, FOR XML PATH is building a result string, and it just so happens that in this case, the string doesn’t have line endings. You could build a multi-line result set by appending carriage returns and line feeds at the appropriate grouping points.

For more a-ha moments, check out Erik Darling’s call to action for today’s T-SQL Tuesday, and there will be fun comments in that post.

Previous Post
[Video] Office Hours: DBA & Developer Relationships
Next Post
Free Azure Networking Training This Month!

4 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.