472,954 Members | 1,607 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,954 software developers and data experts.

data length more than ...

AJA
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

Mar 28 '07 #1
5 4328


"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
Mar 28 '07 #2
AJA
>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

Mar 28 '07 #3
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

Mar 29 '07 #4
AJA
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
Mar 29 '07 #5
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

Apr 4 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Randell D. | last post by:
Folks, I've heard of buffer overflows being used/abused by hackers and believe one method to reduce this from happening is to check the length of my form data before writing it to my MySQL...
4
by: Burke Atilla | last post by:
While encrypting data with DES through CryptoStream makes encrypted data bigger than original string. if we have 8 byte key and 8 byte of data then the mode is ECB. output encrypted data is 16 bytes...
3
by: imtmub | last post by:
Hi All, I am facing some problem in the field data(Description field in Item table). In that table one field(Description field)data legth is 255 and type: nvarchar. In some operation this field data...
4
by: imtmub | last post by:
Hi All, How to select the records in table1, field1. Bcz in field1 some data is more then 100(Field Length) and some are less than 100. so i want to filter the records the data length is more than...
0
by: krishnasarma | last post by:
i want to store data more than 2GB... how to overcome the size limitation of MSAccess....thanx
1
by: sqldba | last post by:
Hi, I have a table with C1- ID C2-Date C3-Date with time C4-quantity I need to create a daily process that will remove the oldest data(data older than 30 days) on the table and make sure...
1
by: Dave | last post by:
I'm having problems getting the GridView to reliably display a large amount of data (50,000+ rows). I am working my way through the excellent book “Real World ASP.NET Best Practices” by Farhan...
4
by: ghjk | last post by:
I want to get data less than 24 hours from the current date. This is my query. But i get wrong output. It display 2008-07-22 data and 2008-05-15 data also. Could some one help me? SELECT...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
1
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.