Getting data from XML file to an array/db

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Newbie - 16 Apr 2007 11:25 GMT

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

Reply to this Message

Bob Barrows [MVP] - 16 Apr 2007 12:20 GMT

> Hi All
>
[quoted text clipped - 4 lines]
> 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
[quoted text clipped - 15 lines]
> 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,
[quoted text clipped - 10 lines]
>
> 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.

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Webmaster Forum / ASP / Database Access / April 2007

Getting data from XML file to an array/db