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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<person> <name>Brent Ozar</name> <occupation>Night Drinker</occupation> </person> <person> <name>Erik Darling</name> <occupation>Day Drinker</occupation> </person> <person> <name>Dave Valentine</name> <occupation>Designated Driver</occupation> </person> |
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:
|
1 |
Brent Ozar, Erik Darling, Dave Valentine |
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.
4 Comments. Leave new
Nice! Who would have thought it could be so easy,
I’ve used this XML voodoo quite a few times and never really understood what it was doing. I’m so happy Microsoft added STRING_AGG so I don’t have to do this XML trick anymore!
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16
Same here Josh, once I got on the train of new functions in SQL i jumped on that, and never looked back.
We use it all the time and it’s brilliant! You do need to be aware of handling “unusual” characters but it’s all eminently manageable. It’s particularly useful if your putting together a string for dynamic SQL (if you allow that sort of thing).