Large XML to Dataset

Randy Rice

unread,

Dec 7, 2005, 2:59:04 PM12/7/05

to

Hello,

First time to post to a borland forum! Using Delphi 7PRO

I have a large XML document containing about 40000 records that need to be
inserted into SQL2000. The file size is about 25MB.

I am trying to use the XMLTransformProvider to feed a ClientDataSet. It
works... but takes about 1.5 hours to open! Even more strange, after
processing it takes just about as long to close the dataset. I understand
that it's the DOM model that is causing the entire file to be loaded before
it can be used.

I checked various other options... came across:
* SAX (which seemed to not have a future)
* Various 3rd party components such as NativeXML
* considered the MS-SQLXML bulk load utility - although this required using
OLE and I did not have immediate success getting this to work within a
delphi app.

At this point I'm resigned to either just letting it take hours to run each
day, or trying out the NativeXML components. I've been hesitant to try the
NativeXML because I didn't want to go to the trouble to learn it and then
discover it takes just as long or causes other problems. I'm so tired of
screwing with it... the irony... I thought XML would make this type of data
sharing easier.

Any insights appreciated.
Thanks,
Randy Rice

danny heijl

unread,

Dec 7, 2005, 3:13:18 PM12/7/05

to

Randy Rice schreef:

> I am trying to use the XMLTransformProvider to feed
> a ClientDataSet.

Drop the clientdataset, it is a major source of your performance
problems. Parse the XML, and generate SQL insert statements instead
using a TADoCommand.

For even better performance use a (prepared) stored procedure with
parameters do handle the inserts.

> * SAX (which seemed to not have a future)

Strange statement for someone complaining about XML speed.

Have a look at the Destructor XML parser (free with source, SAX-like,
fast, easy, rock solid, native Object Pascal) at
http://www.destructor.de/xmlparser/index.htm.

You should be able to insert those 40000 records in a couple of minutes.

Danny
---

Randy Rice

unread,

Dec 7, 2005, 5:59:40 PM12/7/05

to

Thank you, I will try it.

"danny heijl" <danny_dot_heijl_at_telenet_dot_be> wrote in message
news:43974222$1...@newsgroups.borland.com...

André Prins

unread,

Dec 7, 2005, 5:09:09 PM12/7/05

to

Randy Rice wrote:

> I've been hesitant to try the
> NativeXML because I didn't want to go to the trouble to learn it and
> then discover it takes just as long or causes other problems.

Go for it! It is really great and doesn't require the whole XML
document to be in memory. Events are fired for every opening element,
closing element and attribute, so it's very easy to gather the data and
write it directly to a table.

--
André Prins

Nils Haeck

unread,

Dec 8, 2005, 7:26:20 AM12/8/05

to

Hi Randy,

I'm the author of NativeXml, and I can tell you that it is much faster than
what you describe for XmlTransformProvider. I am using it in my own
applications, and in one application there are about 80.000 XML elements,
which load in about 10 - 15 seconds. Saving is even faster.

There's currently already a very large number of users (1000+) that uses
NativeXml in situations like this. It is not very complex, but of course
every component has a learning curve. Therefore, I would advise to have a
look at some of the examples to get a feel for it.

Nils

"Randy Rice" <ran...@archtelecom.com> schreef in bericht
news:43973ecb$1...@newsgroups.borland.com...

Randy Rice

unread,

Dec 8, 2005, 3:43:49 PM12/8/05

to

Hello Nils,

I happened to have already downloaded NativeXML so gave it a try. Yes, it
is much much faster. It took me a few minutes to test the events OnNodeNew
and OnNodeLoaded to figure out what happens. I have data in this format:

I had expected OnNodeLoaded to fire on every node, but it skipped the first
two lines and fired on the first <ID> tag. I was looking for the most
practical way to insure that I capture a set of data between the ListingData
tags as a unique record. I noticed that OnNodeNew fires on the opening
<ListingTag> and OnNodeLoad fires on the closing </ListingData> tag, so I
used those as triggers to initialize and save each record. Obviously I
don't know near enough about XML.... Anyway, this appeared to work and
solve my need for now, and hopefully I'll get a chance to get more familiar
with XML.

This morning I purchased the license. Thanks for the product that I needed.

Randy

"Nils Haeck" <b...@bla.com> wrote in message
news:4398...@newsgroups.borland.com...

Nils Haeck

unread,

Dec 8, 2005, 6:04:49 PM12/8/05

to

> <OwnersData>
> <ListingData>
> <ID>1622535</ID>
> <ContactName>JOHN DOE</ContactName>
> <etc...>
> </ListingData>
> </OwnersData>

Yes, OnNodeLoaded can only fire after the complete node is loaded, so at the
</xxx> closing tag. You can use this event to destroy the node and save
memory in case of huge XML documents (see example C in helpfile).

So the sequence of events in above XML snippet would be:

OnNodeNew 'OwnersData'
OnNodeNew 'ListingData'
OnNodeNew 'ID'
OnNodeLoaded 'ID'
OnNodeNew 'ContactName'
OnNodeLoaded 'ContactName'
...etc
OnNodeLoaded 'ListingData'
OnNodeLoaded 'OwnersData'

> This morning I purchased the license. Thanks for the product that I
> needed.

And thank you for your purchase :)

Nils