Posted by M A Srinivas on 03/29/07 09:36

On Mar 28, 4:05 pm, "AJA" <ajanospa...@gazeta.pl> wrote:
> 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

If you make an SP(stored Procedure) out of the Code , you can send
xml to SP as a text parameter

[Back to original message]