Get Rid of XML Special Characters in XML at SQL Query

The Below is the example in which the special characters in the database are populated as Extra character in XML, such as for & , it shows &amp.

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


The solution for this is use of
root('MyString'), type
).value('/MyString[1]','varchar(max)') in the code as shown below


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;

Hence the extra characters of the special character are eliminated by this type of usage.

Note: The XML is used in the code, to print the comma separated values.

The original code is given below

select '; '+

case when Gokul_Org.Location IS null then Ltrim(rtrim(Gokul_Quit.Location_Name))

else Ltrim(rtrim(Gokul_Org.Location)) END

+':'+Ltrim(Rtrim(Gokul_Quit.City_Name))+','+Ltrim(rtrim(Gokul_Quit.State))+' '

from local.image.dbo.orders O

join local.image.dbo.Quit Gokul_Quit

On O.Id=Gokul_Quit.order_id and O.Shipper_quit_id <> Gokul_Quit.Id

and Gokul_Quit.quit_type ='PU'and O.Company_id=Gokul_Quit.company_id

Left Join local.image.dbo.edilocation Gokul_Org

on Gokul_Quit.Location_id=Gokul_Org.location_id and Gokul_Quit.Company_id=Gokul_Org.Company_Id

and Gokul_Org.partner_id = 'HTEDRF'and Gokul_Org.Company_Id='TMS2' and Gokul_Org.version = '004030'

and Gokul_Org.Location_Type in ('S','W')

where O.Id=Orders.Id and O.Company_Id=orders.company_id

FOR XML PATH('')),2,200000) AS 'Gokul_Location'

-----------------------------------------------------------------------------------------