Posted by AJA on 03/28/07 11:05

Hello
I have problem with reading from XML when XML is to large.
Program delare 1-n variables where is declaration but can no make more
delarations than length 8000 :((

drop table tblBooksEx
CREATE TABLE [tblBooksEx] (
[Row_ID] [int] IDENTITY (1, 1) NOT NULL ,
[BooksData] [text] COLLATE Polish_CI_AS NULL ,
CONSTRAINT [PK__tblBooksEx__17036CC0] PRIMARY KEY CLUSTERED
(
[Row_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into tblBooksEx(booksdata) values('')
exec master..xp_cmdshell 'TextCopy.exe /S serv /U usr /Ppsv /D Northwind /T
tblBooksEx /C BooksData /F c:\SCN.xml /W "WHERE Row_ID=1" /I'


/*PART 1*/
DECLARE @id int
DECLARE @idoc int
SET @id = 1 -- or whatever the id

DECLARE @datalen int
DECLARE @sql varchar(8000)
DECLARE @sql1 varchar(8000)
DECLARE @cnt int
-- get the length
SELECT @datalen = DATALENGTH (booksdata) / 4000 + 1 FROM tblBooksEx WHERE
row_id = @id

-- phase 1 collect into @sql declarations of @str1, @str2,...@strn
SET @cnt = 1
SET @sql='DECLARE '
SET @sql1 = ''
WHILE (@cnt <= @datalen)
BEGIN
SELECT
@sql = @sql + CASE @cnt
WHEN 1 THEN ''
ELSE ', ' + CHAR(13)
END
+ ' @str'+CONVERT(varchar(10),@cnt)+' VARCHAR(4000)'
SET @cnt = @cnt + 1
END
-- phase 2 collect into @sql selection of chunks (takng care of length)
SET @cnt = 1
WHILE (@cnt <= @datalen)
BEGIN
IF LEN(@sql) < 7850
SELECT @sql = @sql + CHAR (13) +
'SELECT @str' + CONVERT(VARCHAR(10), @cnt) + ' =
REPLACE(SUBSTRING(booksdata, ' +
CONVERT(VARCHAR(30), (@cnt-1)*4000+1) + ', 4000),CHAR(39),
CHAR(39)+CHAR(39) ) ' +
'FROM tblBooksEx ' +
'WHERE row_id = ''' + cast(@id as varchar) + ''''
ELSE
SELECT @sql1 = @sql1 + CHAR (13) +
'SELECT @str' + CONVERT(VARCHAR(10), @cnt) + ' =
REPLACE(SUBSTRING(booksdata, ' +
CONVERT(VARCHAR(30), (@cnt-1)*4000+1) + ', 4000),CHAR(39),
CHAR(39)+CHAR(39) ) ' +
'FROM tblBooksEx ' +
'WHERE row_id = ''' + cast(@id as varchar) + ''''
SET @cnt = @cnt + 1
END


/*PART 2*/
-- phase 3 preparing the 2nd level dynamic sql
SELECT @sql1 = @sql1 + CHAR(13) + 'EXEC ('+ CHAR(13) + '''DECLARE @idoc
int'+ CHAR(13) +
'EXEC sp_xml_preparedocument @idoc OUT, '''''' + '
SET @cnt = 1
WHILE (@cnt <= @datalen)
BEGIN
SELECT @sql1 = @sql1 + CHAR (13) + '@str' + CONVERT (varchar(10), @cnt) + '
+'
SET @cnt = @cnt + 1
END
SET @sql1 = @sql1 + ' '''''' '
SET @sql1 = @sql1 + CHAR(13) + 'DECLARE idoc_cur CURSOR FOR SELECT @idoc'''
+CHAR(13) + ')'
--debug code
/*
PRINT @sql
PRINT '@sql length=' +convert(varchar(5),datalength(@sql))
PRINT '----------'
PRINT @sql1
PRINT '@sql1 length=' +convert(varchar(5),datalength(@sql1))
*/
EXEC (@sql + @sql1)
OPEN idoc_cur
FETCH NEXT FROM idoc_cur into @idoc
DEALLOCATE idoc_cur




select * from OpenXML(@idoc, '//transfer/body', 2) WITH (ng int, nk int, dw
nvarchar(50))
--When Complete
--/*
exec sp_xml_removedocument @idoc
--*/

How to solve this problem??


Best Regards
AJA

[Back to original message]