tags:

views:

33

answers:

0

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>