[Openroad-users] Importing XML files into Ingres/OpenROAD

Tue Nov 14 16:31:14 EST 2006

I forgot to mention

OpenROAD only reads elements from an xml-file all attributes are ignored.

But you can make a very simple generic XSL-script that change all attributes to elements.

Put that transformation into iixmlpreprocess using msxml.

I have such a script somewhere if you are interested I think it is less than 10 lines of xsl.

Kim

________________________________

Fra: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] På vegne af Kim Ginnerup
Sendt: 13. november 2006 20:43
Til: openroad-users at peerlessit.com
Emne: Re: [Openroad-users] Importing XML files into Ingres/OpenROAD

Hi Martin,

As Neil mentioned there a lot of different ways you can process the file.

I will just add a little extra.

The automatic OpenROAD xml parsing that is used in the OpenROAD Server, is not just for the OpenROAD Server.

You can use that as well internal in an OpenROAD application.

A way we have used:

Create a set of classes that match or almost match the xmlfile

Create a 4gl procedure and use the top xml class as a parameter.

Then generate a SCP for that procedure.

Now you have a generated xml reader and writer,

that you can use inside an OpenROAD application.

If the xml needs pre processing or post processing, use the iixmlpreprocess and xmlpostprocess 4gl procedures.

OpenROAD 4.1SP3 is pretty fast to read and parse XML.

Pro and cons on the above solution.

PRO:

- When the classes have been made the rest is more or less automatic.

  Changes in the xml-file is easy to implement, it is just a change in a class-attributes, and regenerate the SCP.

- It is fast at runtime.

CON:

- If the XML file is very complex it becomes quite complex to make the corresponding class hierarchy.

- If it is very bigfiles you might consume a lot of memory.

  You will have a full copy of the XML-file and the same data loaded into the OpenROAD defined classes.

  We have used the approach with 25mb. xml-files quite fast.

If you need more complex xml parsing, then use msxml.

Msxml can be used inside the iixmlpreprocess and iixmlpostprocess.

Kind regards

Kim Ginnerup

________________________________

Fra: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] På vegne af Neil.Warnock at luminary.co.uk
Sendt: 13. november 2006 19:57
Til: openroad-users at peerlessit.com
Emne: Re: [Openroad-users] Importing XML files into Ingres/OpenROAD

Hi Martin, 

You have a myriad of approaches to choose from: 

a) See XMLimport in chapter 6 of the Ingres DBA userguide - Loading and Unloading Databases; 
b) See XML_in in the AppServer userguide (I think that's what you are referring to below but you've called it XMLImport which is the thing above!);

c) Use an MSXML3 or 4 activeX control embedded in OpenROAD to read and manipulate the XML doc; 
d) Process the XML doc outside 4GL in C#, VB Java using a native language XML parser then either pass to 4GL for updating the DB or update the DB directly;

e) Um, there's probably more... 

If it's a large doc coming in periodically in one big chunk (ie a large batch of lab results) you might be best with option a) - loading into Ingres then processing further. You may need to transform the original doc before loading.  Maybe experiment with the VDBA interface before putting it into a batch job.

Hope this gets you started, 

Rgds, 

Neil Warnock 
Luminary Solutions 
Tel: +44 (0)870 757 40 90  
Mob: +44 (0)771 265 0291  
Email: Neil.Warnock at luminary.co.uk 

For more information on Luminary go to http://www.luminary.co.uk <http://www.luminary.co.uk>  

_____________________________________________ 
From:   openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com <mailto:openroad-users-bounces at peerlessit.com> ]  On Behalf Of Bloomfield, Martin (PSD)

Sent:   13 November 2006 15:08 
To:     Openroad-Users 
Subject:        Importing XML files into Ingres/OpenROAD 

OpenROAD experts, 

We are starting on a new project that involves receiving analysis results from various laboratories.  Currently these results are sent to us in flat (^ separated) text files.  However, the labs have great difficulty creating these files accurately.  We have been requested to change the format of these files to be XML, for which a schema is currently being developed.

The problem I have is how to process these XML files using OpenROAD to get the information into our Ingres database.  None of us at PSD have any experience of working with XML files and I have not been able to find any information on importing data from XML into Ingres, apart from mention of the xmlimport function provided as part of Application Server.  We are currently looking at creating a standard OpenROAD client-server application as there are no web requirements.

Has anyone any experience of importing XML files into an Ingres database, and if so can you provide any advice / guidance as to the best way of doing this?  Alternatively, can anyone point me in the direction of some guidance documentation on how to go about processing XML files and importing the data into an Ingres database.  

We are currently using Ingres 2006 (r3) on Windows as our back-end and OpenROAD 4.1 SP2 on Windows XP for clients. 

Many thanks for any assistance. 

Martin Bloomfield. 
____________________________________________ 
Application Developer & Database Administrator 
IT Branch, 
Pesticides Safety Directorate 
YORK 

Email: martin.bloomfield at psd.defra.gsi.gov.uk <mailto:martin.bloomfield at psd.defra.gsi.gov.uk>  
Website: www.pesticides.gov.uk <http://www.pesticides.gov.uk>  
 << File: ATT115312.txt >> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://peerlessit.com/pipermail/openroad-users/attachments/20061114/cc8d0a71/attachment.html 
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: ATT30380.txt
Url: http://peerlessit.com/pipermail/openroad-users/attachments/20061114/cc8d0a71/attachment.txt 