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 5 4228
"AJA" <aj*********@gazeta.plwrote in message
news:eu**********@inews.gazeta.pl...
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)
>
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
>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
On Mar 28, 4:05 pm, "AJA" <ajanospa...@gazeta.plwrote:
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
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:
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 This discussion thread is closed Replies have been disabled for this discussion. Similar topics
7 posts
views
Thread by Randell D. |
last post: by
|
4 posts
views
Thread by Burke Atilla |
last post: by
| | | | |
1 post
views
Thread by Dave |
last post: by
| | | | | | | | | | | |