Getting data from XML file to an array/db

[Newbie]

Hi All

Wondered if you could help.

I have created a little backup routine (using classic ASP and ADO) to get my
data from my db into a bespoke XML file, ie I query the data into a
recordset, concat the XML tags with this data and then put it into a text
file using FileSysObj.

This backup proc all works fine, but the problem is when I go to restore
this backup. I'm using the xml dom object to get the dataset by tagname and
then building up the 'insert into' statements by concating it with the
xml-based data, but my ASP scripting is always timing out by the time this
process is complete on any dataset with a large amount of data in it.

PLEASE NOTE: my scripting timeout is set to 15 secs by my host and I cannot
change this or my ISP at this current time.

The backup/restore process all falls into place really nicely, apart from
the fact that I can't get the XML restore bit to work fast enough to get the
data back into the db. For example, I had an xml file consisting of 6000+
rows, which contained 18 cols/fields per row. I did a simple test of
extracting this data using 2 x FOR NEXT loops and writing the field/tag
values to the page and the ASP couldn't complete this before the timeout.
Its as if the XML object/process is too slow to handle this amount of data.
Is this the case?

When I've done the ADO's recordset save option to binary this works like
lightning and it's xml offering isn't too much slower as well, but I don't
like the way the binary/xml file is put together because I can't add the
queries to the same file, ie using the ADO binary/xml method I have about 22
separate files, whereas my bespoke xml file is just 1 file with all the data
in.

Other users are going to be backing up my db via an ASP page, so they only
backup the data I want them to back up (hence the reason why I don't just
use a direct sql backup program), but I seem to be resigned to the fact that
I will only be able to use the ADO version instead of my own.

Anybody had this problem and got round it?

Thanks

 

[Bob Barrows [MVP]]

Hi All

Wondered if you could help.

I have created a little backup routine (using classic ASP and ADO) to
get my data from my db into a bespoke XML file, ie I query the data
into a recordset, concat the XML tags with this data and then put it
into a text file using FileSysObj.

You've gone to a lot of unnecessary trouble. You can use the recordset Save
method to save a recordset to an xml file. Later on, you can use the
recordset open method to open a recordset on that xml file.


Oh! reading on, I now see you know about Save ... Open. I would say that you
do not need FildSysObj to save the xml to a file. Use an xml document object
to create your xml (see the data island example here for an example:
http://www.davidpenton.com/testsite/tips/). Use the Save method to save the
xml document to a file.

This backup proc all works fine, but the problem is when I go to
restore this backup. I'm using the xml dom object to get the dataset
by tagname and then building up the 'insert into' statements by
concating it with the xml-based data, but my ASP scripting is always
timing out by the time this process is complete on any dataset with a
large amount of data in it.

PLEASE NOTE: my scripting timeout is set to 15 secs by my host and I
cannot change this or my ISP at this current time.

The backup/restore process all falls into place really nicely, apart
from the fact that I can't get the XML restore bit to work fast
enough to get the data back into the db. For example, I had an xml
file consisting of 6000+ rows, which contained 18 cols/fields per
row. I did a simple test of extracting this data using 2 x FOR NEXT
loops and writing the field/tag values to the page and the ASP
couldn't complete this before the timeout. Its as if the XML
object/process is too slow to handle this amount of data. Is this the
case?

Yes, it probably is. Nobody ever said parsing an xml file would be fast.

When I've done the ADO's recordset save option to binary this works
like lightning and it's xml offering isn't too much slower as well,
but I don't like the way the binary/xml file is put together because
I can't add the queries to the same file, ie using the ADO binary/xml
method I have about 22 separate files, whereas my bespoke xml file is
just 1 file with all the data in.

Other users are going to be backing up my db via an ASP page, so they
only backup the data I want them to back up (hence the reason why I
don't just use a direct sql backup program), but I seem to be
resigned to the fact that I will only be able to use the ADO version
instead of my own.

Anybody had this problem and got round it?

Nope. It would never have occurred to me to handle 6000+ rows in an xml
document, let alone in an ASP page. This is the wrong technology for the job
IMO. Don't forget that xml is extremely bulky, so not only are you fighting
CPU usage, you are also consuming memory, much more than your host would
like I'm sure. There's a very good reason your host has the timeout set to
15 sec.