Stacky Stacky - 2 months ago 14
SQL Question

Why does using XML Path('') return a blue underlined data and how to remove it?

I am using this to concatenate different strings into one and it works but problem is that it is UNDERLINED and BLUE like a link and I tried removing it but nothing works. Also, column header is something not presentable.

SELECT np.Name+ ', ' AS 'data()'
FROM NewsPaper np
inner join NitNewsPaper nitnp
on nitnp.NewsPaper_ID= np.NewPaperID
where Nit_NO= 1518
FOR XML PATH('')

Answer Source

Try the below query. It should fix your issue.

SELECT STUFF((SELECT ', ' + np.NAME
FROM NewsPaper np
INNER JOIN NitNewsPaper nitnp ON nitnp.NewsPaper_ID = np.NewPaperID
WHERE Nit_NO = 1518
FOR XML PATH('')), 1, 2, '') as 'data'

FOR XML PATH allows you to output the results of the query as XML and hence you are seeing the blue underline. This will output something like: , Name1, Name2. We need to get rid of the leading ', ' from this. So, we use STUFF to remove the first 2 characters.