I am using for xml explicit clause in sql to send table data as xml from sql to asp.net page. I have created a stored procedure when i run store procedure in sql mgmt studio my xml is same as expected. But when I fetch It in asp.net then Xml returned is broken into two rows if xml exceeds certain character limit. I want result to be in single row. I am unable to figure out why single xml string is broken into two rows.
please help
hi this is store procedure i am using
ALTER proc [dbo].[getXml]
@screenid int
as
begin
declare @filelist varchar(4000) ;
select @filelist=filename from tb_screens where screenid=@screenid
select 1 as tag,null as parent,title as [chapter!1!title],null as [quiz!2],null as[quiz!2!sound!element],null as[quiz!2!sndAnswer!element],null as[quiz!2!questionPic!element],null as[quiz!2!feedbackPic!element]
from tb_screens where screenid=@screenid
union all
select 2 as tag,1,lib.title,'','/tutorials/'+screen.filename,screen.title,'/tutorials/'+screen.ImageQues,'/tutorials/'+screen.ImageAns from tb_screens as lib cross join (select * from tb_screenlib where screenid
in( select * from dbo.fn_Split(@filelist,','))) as screen where lib.screenid=@screenid
for xml explicit
end
this xml I get when store procedure is called
<chapter title="xyz">
<quiz>
<sound>/tutorials/</sound>
<sndAnswer>xyz</sndAnswer>
<questionPic>xyz</questionPic>
<feedbackPic>xyz</feedbackPic>
</quiz>
<quiz>
<sound>/tutorials/</sound>
<sndAnswer>xyz</sndAnswer>
<questionPic>xyz</questionPic>
<feedbackPic>xyz</feedbackPic>
</quiz>
</chapter>