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'
