Why You May Need More Than FOR XML PATH(”)

Working on a blog post and I came up against a problem that I had heard of before but did not spend much brain-CPU power against.

I know I’m going to run into this again so let’s document this for future me. Oh he’s going to appreciate this so much!

Commas are all the rage nowadays:

There are a fair number of questions nowadays about returning data from a database in a comma separated string. Sure the application should probably do that but hey, database servers are expensive, why not get some bang for your bucks!

Done!-ish…

SQL Server 2017 has this lovely function called STRING_AGG()

Pop in your column and your separator and it takes care of it for you!

…wait not everyone has SQL Server 2017 yet?

…wait I don’t have SQL Server 2017 yet? Oh, I should really fix that…

Pre-SQL Server 2017:

So what can we do if we are not on SQL Server 2017? Take the advice that I was given for most of my life and STUFF it!

The STUFFing:

Our playground:

[code language=”SQL”]
USE tempdb;

— Test table
SELECT dt.comments
FROM ( VALUES ( ‘XML is the bomb!’),
( ‘& JSON is cool too…’)
) AS dt (comments);
[/code]

 

Plain Old STUFFing:

I’m not the biggest fan of stuffing if I’m honest…tastes like dirt to me but hey, it works in 99% of situations…

[code language=”SQL”]
SELECT STUFF((SELECT ‘, ‘ + dt.comments
FROM ( VALUES ( ‘XML is the bomb!’),
( ‘& JSON is cool too…’)
) AS dt (comments)
FOR XML PATH(”)
), 1, 1, ”) AS CommentsEnXML;
[/code]

 

Bacon Sausage STUFFing however:

So…SQL Server is trying to be too helpful. What do we do? I normally turn to the SQL community and people like Rob Farley ( blog | twitter ), who has a lovely post about this.

So let’s try it out.

[code language=”SQL”]
SELECT STUFF((SELECT ‘, ‘ + dt.comments
FROM ( VALUES ( ‘XML is the bomb!’),
( ‘& JSON is cool too…’)
) AS dt (comments)
FOR XML PATH(”),
TYPE).value(‘.’, ‘varchar(max)’
), 1, 1, ”) AS CommentsEnXML;
[/code]

 

I just eat the turkey around the STUFFing:

Little hiccup in preparing for my next post. Thankfully I learn from my mistakes and failures (there’d be no help for me otherwise!).

I’ll leave this post with a quote from the blog of the main man himself:

It’s a habit I need to use more often.

Yeah, me too Rob, me too…