Why is char (13) + char (10) in for xml ('') path not working?
when i use char(13)+char(10)
in my queries it works, but when i use char(13)+char(10)
in a query that has for xml path('')
it doesn't work and instead i get this symbol 
i think it is because of two for xml path('')
, although it may not be. what should I do?
there is a sample of my code:
select d.title+char(13)+char(10)+
(
select c.title+char(13)+char(10)+
(
select a.title+b.title+char(13)+char(10)
from tbl_one a
inner join tbl_two b on a.id=b.one
where a.id=aa.id
for xml path('')
)
from tbl_one aa on
inner join tbl_three c on aa.id=c.one
where aa.id=aaa.id
for xml path('')
)
from tbl_four d
inner join tbl_one aaa on d.one=aaa.id
+3
source to share
1 answer
Specify a type directive for your xml output like this:
select d.title+char(13)+char(10)+
(
select c.title+char(13)+char(10)+
((
select a.title+b.title+char(13)+char(10)
from tbl_one a
inner join tbl_two b on a.id=b.one
where a.id=aa.id
for xml path (''), type).value('(./text())[1]','nvarchar(max)')
)
from tbl_one aa on
inner join tbl_three c on aa.id=c.one
where aa.id=aaa.id
for xml path (''), type).value('(./text())[1]','nvarchar(max)')
from tbl_four d
inner join tbl_one aaa on d.one=aaa.id
Link:
registry: http://rextester.com/ETCRIW69262
select 'd'+char(13)+char(10)+
(
select c+char(13)+char(10)+
(
select a+b+char(13)+char(10)
from (values ('a','b')) x (a,b)
for xml path ('')
)
from (values ('c'))x(c)
for xml path (''))
returns:
d
c
ab
and this version:
select 'd'+char(13)+char(10)+
(
select c+char(13)+char(10)+
((
select a+b+char(13)+char(10)
from (values ('a','b')) x (a,b)
for xml path (''), type).value('(./text())[1]','nvarchar(max)')
)
from (values ('c')) x (c)
for xml path (''), type).value('(./text())[1]','nvarchar(max)')
returns:
d
c
ab
+2
source to share