Handling special characters with FOR XML PATH('')(处理xml输出的特殊字符，如<等)

原文地址：http://sqlblog.com/blogs/rob_farley/archive/2010/04/15/handling-special-characters-with-for-xml-path.aspx

Because I hate seeing &gt; or &amp; 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 &lt;databasename&gt;, &lt;databasename2&gt;. 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;

注意：value一定要是小写才可以，某些sql 第三方插件会自动将关键字变为大写，这时容易出现问题。

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.