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 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
>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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
by: krishnasarma |
last post by:
i want to store data more than 2GB... how to overcome the size limitation of MSAccess....thanx
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
| |