8K row limit issue 2

[rhinok]

Hi,

We're loading xml into a varchar field, but we're running into an 8k row limit. I understand that this is a constraint of SQL Server 2k, but are there any ways around this. I've heard that we may be able to use Blobs or to use the Text/Ntext datatypes.

Can anybody provide some insight?

 

[SQLSister]

Depends on what you want to do. If you have text that is longer than the row size limit load it tinto a text or ntext field.

If you have too many fields, but no one field needs to be longer than the row limit, it is usually best to set up a second table withthe primary key fromthe first table as a foreign key and havea 1-1 relationship between the tables. try to put the most accessed stuff together in one table where possible to reduce the need for joins.

 

[dky1e]

You must use Text data type in order to be able to store over 8Kbytes in a field.

 

[rhinok]

I have a little more clarification. Our issue isn't storing the data, but accessing it.

The dba wrote a proc that calls a variable to pull xml elements out of the field in question. The problem is that the proc blows up whenever there's >8k of data. We're exploring the use of temp tables within the proc.

 

[JamesLean]

SQL 2000 contains native support for XML documents. There's plenty of info in BOL, in particular look up OPENXML and sp_xml_preparedocument.

sp_xml_preparedocument takes the XML string and makes an internal representation of it which can be queried just like a table using OPENXML.

--James

 

[rhinok]

Actually, we are using OPENXML AND sp_xml_preparedocument within the proc. The problem lies in the fact that the variable field called has >8k data. This causes the proc to fail.

According to my conversations with Microsoft, our only other option (keeping it in the proc, that is) is to parse the Variable (v_XML_data) into 8k chunks using READTEXT.

 

[bdreed35]

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