MSSQL new line in XML cannot using char(13)

 

Problem 

Cannot use char(13)

Solved

Becuase of XML

select replace(PositionName, ',', char(13)) from (

Select(

SUBSTRING(

(

SELECT ',' + PositionName  AS [text()]

from RecruitmentPosition

left join Position on Position.PositionId = RecruitmentPosition.PositionId and  (Position.InactiveTime is null or Position.InactiveTime >= getDate())  

where RecruitmentPosition.RecruitmentId = RecruitmentApplicant.RecruitmentId and  (RecruitmentPosition.InactiveTime is null or RecruitmentPosition.InactiveTime >= getDate()) 

ORDER BY PositionName

FOR XML PATH ('')

), 2, 1000)

) as PositionName

 ) as t1