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
"AJA" <ajanospam74@.gazeta.plwrote in message
news:eudi64$2c3$1@.inews.gazeta.pl...
Quote: Originally Posted by 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)
Quote: Originally Posted by >
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|||>Hello
Quote: Originally Posted by |
Quote: Originally Posted by >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
Quote: Originally Posted by >
> |
Quote: Originally Posted by >>
>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
>
>|||On Mar 28, 4:05 pm, "AJA" <ajanospa...@.gazeta.plwrote:
Quote: Originally Posted by 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|||
Quote: Originally Posted by If you make an SP(stored Procedure) out of the Code , you can send
xml to SP as a text parameter
> |
Can you tell me clearly because i do not understand ..
AJA|||On Mar 29, 4:05 pm, "AJA" <ajanospa...@.gazeta.plwrote:
Quote: Originally Posted by |
Quote: Originally Posted by If you make an SP(stored Procedure) out of the Code , you can send
xml to SP as a text parameter |
>
Can you tell me clearly because i do not understand ..
>
AJA
Need to do in an SP . You need to send text from client side. Sending
text within sql server not possible for SQL 2000
create proc usp_getxml
@.xmltext TEXT
as
Declare @.hdoc INT
--Create XML document.
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.xmltext
