Get XML node as 'text' data type

We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

CREATE PROCEDURE as MyProcedure @.myData xml
BEGIN
INSERT INTO MyTable (FirstName, LastName, Notes)
SELECT
MyNode.value('FirstName[1]','varchar(50)'),
MyNode.value('LastName[1]','varchar(100)'),
MyNode.value('Notes[1]','text')
FROM @.myData.Notes('Person') as R(MyNode)
END

The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.

The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

Any ideas?

Try using 'varchar(max)' instead of 'text'

|||Perfect. Thanks!|||What would be a datatype for the value for an image? Will varchar(max) work for it as well?