Handling special characters with FOR XML PATH(”)

In sql server using stuff  the string replaced when its  have tag “<” or “>” then  &gt;  when its  “&” then &amp;

Example

select
stuff(
(select ‘, <‘ + name + ‘>’
from sys.databases
where database_id > 4
order by name
for xml path(”)
)
, 1, 2, ”) as namelist;

For avoid replace  &gt; or &amp;

 

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;

 

 

Advertisements

Author: tdmsql

I am a PL-Sql Developer. I am searching some sites sql studies but its bored. i like create studies note my own way. so that i created this site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s