If you’re on a new-ish version of SQL Server, using STRING_AGG is your best bet for this. Please use it instead.
Unlike STRING_SPLIT, it’s not compatibility-walled. You don’t need to be on level 130+ to use it.
If you’re on an earlier version, your most reliable bet is using XML. Using the local variable method can have quite unexpected results at times. I’ve seen it go from returning a full list of values to only returning the last value more times than I can count.
Let’s look at the XML version, though. Just in case you’re not on SQL Server 2017.
The purpose of these queries is to show you hot to remove XML elements, and handle XML control characters like &, <, >, etc. All of these results return a single row, just to keep the examples simple.
SELECT x = ( SELECT b.Name FROM dbo.Badges AS b WHERE b.Id = 100564 FOR XML PATH('') );
This will return two things we don’t want:
- XML elements
- An ampersand turned into &
To get rid of the XML elements, we can alias the inner results as
SELECT x = ( SELECT [text()] = b.Name FROM dbo.Badges AS b WHERE b.Id = 100564 FOR XML PATH('') );
That will give us this back, still with the ampersand all mangled up.
To fix that, we need to add a little bit to the XML-ing:
SELECT x = ( SELECT [text()] = b.Name FROM dbo.Badges AS b WHERE b.Id = 100564 FOR XML PATH(''), TYPE ).value ( './text()', 'nvarchar(max)' );
First, we need to add
TYPE to the
XML PATH syntax. That’ll give us an XML typed return type to use the
.value method on.
From there, we can grab the text element, and give it a data type. You don’t specifically need to get
./text() though, but I tend to use it because I’ve seen some very weird performance issues when using less verbose expressions like
. or just
Here are the plan differences, which are negligible for a single row.
Of course, local factors may require deviating from what generally works best.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
2 thoughts on “A Couple Quick Notes On Using XML For String Aggregation In SQL Server”
Wow! I have a STUFF statement using XML for concatenation that I got from a book from a very respectable author. The “/text()” portion was not present in the examples; just “.”. The query has several of these STUFF XML statements in the SELECT list that balloon the cost. I changed it to add in “/text()” and the cost dropped from over 2300 to under 50 (under CTFP). Nothing major in memory grant: 150 MB to 104 MB. Most of it gets left on the table, though, as either form only uses between 2-3 MB. As soon as I can get product management to let me drop support for SQL Server 2016, I’ll move to STRING_AGG.
Anyway, thanks for sharing!
Glad you found it useful!
Comments are closed.