We have found a solution to this problem from Ken Henderson's book "The Guru's Guide to SQL Server Stored Procedures, XML, and HTML". Ken provided the following SP that can read a text type field in a table, that could be over 8K, and return the document handle generated by sp_xml_preparedocument and use it with OPENXML() and sp_xml_unpreparedocument.
Here is the sp_xml_concat sp:
CREATE PROC up_xml_concat_brian
@hdl int OUT,
@table sysname,
@column sysname
AS
EXEC('
SET TEXTSIZE 4000
DECLARE
@cnt int,
@c nvarchar(4000)
DECLARE
@declare varchar(8000),
@assign varchar(8000),
@concat varchar(8000)
SELECT @c = CONVERT(nvarchar(4000),'+@column+') FROM '+@table+'
SELECT @declare = ''DECLARE'',
@concat = '''''''''''''''',
@assign = '''',
@cnt = 0
WHILE (LEN(@c) > 0) BEGIN
SELECT @declare = @declare + '' @c''+CAST(@cnt as nvarchar(15))+'' nvarchar(4000),'',
@assign = @assign + ''SELECT @c''+CONVERT(nvarchar(15),@cnt)+''= SUBSTRING('+@column+',''+CONVERT(nvarchar(15),1+@cnt*4000)+'',4000) FROM '+@table+' '',
@concat = @concat + ''+@c''+CONVERT(nvarchar(15),@cnt)
SET @cnt = @cnt+1
SELECT @c = CONVERT(nvarchar(4000),SUBSTRING('+@column+',1+@cnt*4000,4000)) FROM '+@table+'
END
IF (@cnt = 0) SET @declare = ''''
ELSE SET @declare = SUBSTRING(@declare,1,LEN(@declare)-1)
SET @concat = @concat + ''+''''''''''''''
EXEC(@declare+'' ''+@assign+'' ''+
''EXEC(
''''DECLARE @hdl_doc int
EXEC sp_xml_preparedocument @hdl_doc OUT, ''+@concat+''
DECLARE hdlcursor CURSOR GLOBAL FOR SELECT @hdl_doc AS DocHandle'''')''
)
')
OPEN hdlcursor
FETCH hdlcursor INTO @hdl
DEALLOCATE hdlcursor
GO
***********************************************************
In the calling proc you can call the sp_xml_cocat like this:
(Declare @iDoc as an int data type)
EXEC up_xml_concat @iDoc OUT, 'SELECT report_details FROM xml_details', 'report_details'
In your statement that uses OPENXML(), use @iDoc as the document handle parameter.
Again, many thanks to Ken Henderson, who actually replied to my email when I couldn't get the example in the book to work.
~Brian