Re: [xsl] XML to Database Inserts via XSLT

[Barry Lay <blay@xxxxxxxxxxxxxxxxxxx>]

Stef wrote:

> Hello Everyone,
> 	Well, the subject says it all really. I have
> a few jobs that I -could- write perl to transform the
> xml into inserts, however, I would prefer to use XSLT
> and learn as I go. Another feather in my cap and all
> that.
>  
Stef,

I have done this with an Oracle database.  The latter actually has a 
mechanism for directly updating the database from XML but we found it to 
be very slow.  The process you show will work but if you are inserting a 
lot of rows then the round trips to the database will be a significant 
component of the run time.  Oracle (and some others) provide mechanisms 
for inserting a lot of rows in one bulk operation, although they may not 
be available from an interactive SQL processor - you may need to create 
a program to do it. 

The most efficient way to do this is probably to write your data out to 
a delimited text file and use the database load utility.  This can 
result in order-of-magnitude-level improvements in load speed over 
separate INSERT statements  This approach would require that your data 
rows have the same data elements and be loaded to the same table; I 
assumed from your example that this might be the case

Barry.