Because I hate seeing > or & in my results…
Since SQL Server 2005, we’ve been able to use FOR XML PATH('') to do string concatenation. I’ve blogged about it before several times. But I don’t think I’ve blogged about the fact that it all goes a bit wrong if you have special characters in the strings you’re concatenating.
Generally, I don’t even worry about this. I should, but I don’t, particularly when the solution is so easy.
Suppose I want to concatenate the list of user databases on my system, in alphabetical order. Easy: Note the lack of
select
stuff(
(select ', ' + name -- Note the lack of column name
from sys.databases
where database_id > 4
order by name
for xml path('')
)
, 1, 2, '') as namelist;
This is easy, and it just works. The STUFF command lets me strip the leading comma and space, and my list of database names is done.
But if I wanted to list the names of database with triangular brackets around them… then that’s bit more complicated.
select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path('')
)
, 1, 2, '') as namelist;
It still runs, but I my results don’t show the triangular brackets, it shows <databasename>, <databasename2>. It’s not what I want to see. I effectively need to HTMLDecode the results – but T-SQL doesn’t have a function like that.
However, if I actually make my FOR XML call return actual well-formed XML, then I can extract the contents out, and it returns the data to me in its original (correct) form.
select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path(''), root('MyString'), type
).value('/MyString[1]','varchar(max)')
, 1, 2, '') as namelist;
To be valid XML, it needs a ROOT element. I also need to tell it to be XML, using the TYPE directive.
But this lets me hook into the value of /MyString[1], and return that as varchar(max).
And it works, my data comes back as <databasename>, <databasename2>, etc.
It’s a habit I need to use more often.
Edit: I can also skip the ROOT element (but keep TYPE) and use .value('.','varchar(max)') - but for some reason it's always felt strange to me to be returning something which I declare to be XML (using TYPE) without it being well-formed.