sp_xml_preparedocument text datatype

MS SQL 2000...

I've got a table in sql server with a column defined as a text datatype. I need to pass the value of the text datatype into sp_xml_preparedocument function. All this is happening in a stored procedure. The issue is that I can't create a local variable defined as a text datatype to hold the value开发者_JAVA百科 of the particular value I need in the table. Initially the proc was designed to pass the value of the text field into a varchar(8000) local variable to store the contents then pass it to the function. However, the value of the text field is now larger than 8000. I've seen some examples of breaking up the text field into two varchar(8000) local variables, but I would rather not have to create cursors. Any other suggestions on doing this?

Thanks in advance.

Take a look at SQL Server: When 8000 Characters Is Not Enough for some examples

If you can as soon as possible upgrade to SQL 2005/2008 where you have varchar(max) :-)