Posted by AJA on 03/28/07 13:30

>> 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 :((
>
> If this is SQL 2005, try varchar(max)

Sorry i forgot tell its MSSQL 2k :(( any other idea?

AJA


>
>
>>
>> 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
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
>

[Back to original message]