How to store more than 8000 characters in a variable

[vibs]

This is the stored procedure I wrote but the limitation is if the XML returned to me is more than 8000 characters, the XML gets truncated. Is there a way around this limitation?

Many Thanks

CREATE PROCEDURE DBO.SV_JobDetailAddl4XML
(
@TransID INT
)
AS

BEGIN

declare @xmltext varchar(8000)
declare @FirstGT INT
declare @LastLT INT
declare @CurrLT INT
DECLARE @Root Varchar(50)

Set @Root = 'AdditionalDetail'

select @xmltext = (SELECT Cast(TR.XmlText as varchar(8000)) FROM Transactions TR
WHERE TR.TransactionID = @TransID)
set @firstGT = CHARINDEX('>',@XMLTEXT)
set @CurrLT = CHARINDEX('<',@XMLTEXT,@firstGT)
while (@CurrLT > 0)
Begin
set @LastLT = @CurrLT
set @CurrLT = CHARINDEX('<',@XMLTEXT, @LastLT+1)
End

set @xmltext = '<' + @Root + '>'
+ substring(@xmltext, @firstGT+1,@LastLT - @firstGT-1)
+ '</' + @Root + '>'
select @xmltext

END

 

[jhall156]

Can you use type Text in this situation? JHall

 

[vibs]

Nope. &quot;A variable cannot be of text, ntext, or image data type.&quot;