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

241 views
Skip to first unread message

Kim Ginnerup

unread,
Nov 13, 2006, 2:42:48 PM11/13/06
to openroa...@peerlessit.com

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-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] På vegne af Neil.W...@luminary.co.uk
Sendt: 13. november 2006 19:57
Til: openroa...@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.W...@luminary.co.uk

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

 

_____________________________________________
From:   openroad-us...@peerlessit.com [mailto:openroad-us...@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.b...@psd.defra.gsi.gov.uk
Website: www.pesticides.gov.uk
 << File: ATT115312.txt >>

Bloomfield, Martin (PSD)

unread,
Nov 13, 2006, 10:08:23 AM11/13/06
to Openroad-Users
ATT221621.txt

Kim Ginnerup

unread,
Nov 14, 2006, 6:48:26 AM11/14/06
to Bloomfield, Martin (PSD), openroa...@peerlessit.com

Martin,

 

And here it is:

 

<?xml version="1.0" encoding="ISO-8859-1"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

 <xsl:output method="xml" version="1.0" encoding="ISO-8859-1" indent="yes"/>

 <xsl:strip-space elements="*"/>

 <xsl:template match="*">

  <xsl:copy>

   <xsl:if test="@*">

    <xsl:for-each select="@*">

     <xsl:element name="{name()}">

      <xsl:value-of select="."/>

     </xsl:element>

    </xsl:for-each>

   </xsl:if>

   <xsl:apply-templates/>

  </xsl:copy>

 </xsl:template>

</xsl:stylesheet

 

 

 

Regards,

 

Kim

Fra: Bloomfield, Martin (PSD) [mailto:Martin.B...@psd.defra.gsi.gov.uk]
Sendt: 14. november 2006 11:29
Til: Kim Ginnerup
Emne: RE: [Openroad-users] Importing XML files into Ingres/OpenROAD

 

Kim,

 

As I am new to XML, I would be extremely grateful if you are able to send me the XSL script, as in my attempts to create an XSD, I've discovered that unique rules only seem to work on attributes, so have had to put the unique identifiers into attributes.  Obviously, I need to import these into the database, so will need to convert them to elements.

 

Many thanks,

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

Email: martin.b...@psd.defra.gsi.gov.uk
Website: www.pesticides.gov.uk

-----Original Message-----
From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] On Behalf Of Kim Ginnerup
Sent: Tuesday, November 14, 2006 5:31 AM
To: openroa...@peerlessit.com
Subject: Re: [Openroad-users] Importing XML files into Ingres/OpenROAD

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

 

Department for Environment, Food and Rural Affairs (Defra)
 
This email and any attachments is intended for the named recipient only.
If you have received it in error you have no authority to use, disclose,
store or copy any of its contents and you should destroy it and inform
the sender.
Whilst this email and associated attachments will have been checked
for known viruses whilst within Defra systems we can accept no
responsibility once it has left our systems.
Communications on Defra's computer systems may be monitored and/or
recorded to secure the effective operation of the system and for other
lawful purposes.

Neil.W...@luminary.co.uk

unread,
Nov 13, 2006, 1:57:05 PM11/13/06
to openroa...@peerlessit.com

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.W...@luminary.co.uk

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


_____________________________________________
From:   openroad-us...@peerlessit.com [mailto:openroad-us...@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.b...@psd.defra.gsi.gov.uk
Website: www.pesticides.gov.uk
 << File: ATT115312.txt >>

Kevin Moore

unread,
Nov 13, 2006, 12:37:52 PM11/13/06
to International OpenROAD Users

Hi Martin,

The only thing I can suggest is that you use a 3rd party too to parse the XML filel, or develop a dll which you can call into from openroad. 

It would be a doddle to write in .NET / c# if you have any c# guru's there?

Cheers

Kev


       -----Original Message-----
      From:   openroad-us...@peerlessit.com [mailto:openroad-us...@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

    Kim Ginnerup

    unread,
    Nov 14, 2006, 12:31:14 AM11/14/06
    to openroa...@peerlessit.com

    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

    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.W...@luminary.co.uk

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

     

    _____________________________________________

    From:   openroad-us...@peerlessit.com [mailto:openroad-us...@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.b...@psd.defra.gsi.gov.uk
    Website: www.pesticides.gov.uk
     << File: ATT115312.txt >>

    ATT30380.txt

    Bloomfield, Martin (PSD)

    unread,
    Nov 15, 2006, 9:39:08 AM11/15/06
    to Kim Ginnerup, Openroad-Users
    Kim,
     
    Sorry to bother you again.  Thanks for the XSL transformation code.  I've tested it in XML-spy and it works fine.  However, I'm struggling to work out how to do this in OpenROAD.  I've created myself an external class pointing to the MSXML 4 control.  Unfortunately, not having used external controls before, I'm rather lost as to how to put the XSL code into the control and get it to process the b_so_preprocessxml parameter in the iixmlpreprocess procedure.
     
    Would it be possible for you to send me sample code detailing how to use the external control, or an export of the procedure?  Also, when I create my own "ReadFile" procedure should the class I pass to it map to the root element of the XML file? Also, how should I name array attributes which correspond to repeatable complex XML elements?
     
    for info my XML doc has following structure:
    <Results>                               <-- Do I pass the UserClass "Results" to my procedure at this level? -->
       <sample SampleId="0001/2006">        <-- Should this be an ARRAY of Sample in UserClass "Results"? -->
          <item1>item1</item1>
          <item2>data</item2>
          <residue Code="ABC">              <-- Should this be an ARRAY of Residue in UserClass "Sample"? -->
             <detection>1.2</detection>
             <limit>1.0</limit>
          </residue>
          <residue Code="DEF">
             <detection>1.4</detection>
             <limit>0.5</limit>
          </residue>
       </sample>
       <sample SampleId="0002/2006">
          <item1>item2</item1>
          <item2>data2</item2>
          <residue Code="ABC">
             <detection>1.2</detection>
             <limit>1.0</limit>
          </residue>
          <residue Code="DEF">
             <detection>1.4</detection>
             <limit>0.5</limit>
          </residue>
       </sample>
    </Results>
     
    Many thanks for your assistance,
    ATT244036.txt

    Kim Ginnerup

    unread,
    Nov 15, 2006, 11:55:16 AM11/15/06
    to Bloomfield, Martin (PSD), openroa...@peerlessit.com

    Hi Martin,

     

    I have attached a small frame, that gives you an idea on how to use msxml to make transformations inside OpenROAD.

    Just import the frame into an application that has an msxml external component.

     

    I found the iixmlpreprocess a bit confusing at first myself.

    But I think you can more or less copy paste the code from the frame sample.

     

    How your class hierarchy should be is a little more tricky.

    OpenROAD do not make the xml output as I would have expected at first.

    OpenROAD do not make an xml element around an array, it just dumps the elements.

    So sometimes you will need to have an extra class as a wrapper for an array.

    So the best way to figure out how to do, is to make your classes or some of them from the top down.

    Then put some testdata into them and let our generated xml reader/writer generate the xml output  file.

    OpenROAD will use the procedure name as the root-tag of the xml file.

     

    One thing to be aware of is that OpenROAD do not generate the top Processing Instructtion; setting the correct characterset.

    Also OpenROAD automatically generates an internal dtd.

    Normally I use the iixmlpostprocess() procedure,

    To add the top PI, and remove the dtd.

    I do that just be manipulating the stringObject.

     

    In your xml example below, the Results tag would have been you gscp name in OpenROAD

    Again you can simply remove that in the preprocess, if you do not like it.

    To get the below output your parameter to the procedure would be an “array of sample”

     

    As said the best way is to put some test data into your class hierarchy and then see what comes out of it.

    What comes out is what your system expects to receive.

    transform.exp

    Bodo.B...@t-online.de

    unread,
    Nov 16, 2006, 2:22:12 AM11/16/06
    to openroa...@peerlessit.com
    Just additional info:

    The XML automatically generated by OpenROAD according to your user class
    attributes may be invalid XML, if names of classes or attributes
    contain special characters that are valid within OpenROAD identifiers
    but are invalid within XML tag names, i.e. @, $ and #.

    So, in order to prevent later parsing errors, just don't use these
    characters in OpenROAD class/attribute names.

    Bodo.

    -----Original Message-----
    Date: Wed, 15 Nov 2006 17:55:16 +0100
    Subject: Re: [Openroad-users] Importing XML files into Ingres/OpenROAD

    From: "Kim Ginnerup"
    To: "Bloomfield, Martin \(PSD\)"

       Message      

    Hi Martin,

    Regards,

    Kim

     
    -------------------------

    FRA: Bloomfield, Martin (PSD)
    [mailto:Martin.B...@psd.defra.gsi.gov.uk]
    SENDT: 15. november 2006 15:39
    TIL: Kim Ginnerup
    CC: Openroad-Users
    EMNE: RE: [Openroad-users] Importing XML files into Ingres/OpenROAD


     

    Kim,  

     

    Sorry to bother you again.  Thanks for the XSL transformation code.
    I've tested it in XML-spy and it works fine.  However, I'm struggling
    to work out how to do this in OpenROAD.  I've created myself an
    external class pointing to the MSXML 4 control.  Unfortunately, not
    having used external controls before, I'm rather lost as to how to
    put the XSL code into the control and get it to process the
    b_so_preprocessxml parameter in the iixmlpreprocess procedure.  

    Would it be possible for you to send me sample code detailing how to
    use the external control, or an export of the procedure?  Also, when I
    create my own "ReadFile" procedure should the class I pass to it map
    to the root element of the XML file? Also, how should I name array
    attributes which correspond to repeatable complex XML elements?  

    for info my XML doc has following structure:  

                                   

               

        item1  

        data  

                         

           1.2  

           *1.0  

           

           

           1.4  

           *0.5  

         

         

       

        item2  

        data2  

           

           1.2  

           *1.0  

           

           

           1.4  

           *0.5  

           

       

     

    Many thanks for your assistance,  

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

    Email: martin.b...@psd.defra.gsi.gov.uk [1]
    Website: www.pesticides.gov.uk [2]  

    -----Original Message-----
    FROM: Kim Ginnerup [mailto:k...@bording.dk]
    SENT: Tuesday, November 14, 2006 11:48 AM
    TO: Bloomfield, Martin (PSD)
    CC: openroa...@peerlessit.com
    SUBJECT: SV: [Openroad-users] Importing XML files into
    Ingres/OpenROAD  

    Martin,

    And here it is:

     

     

       

       

         

       

       

     

     

     

    Martin Bloomfield.

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

    Email: martin.b...@psd.defra.gsi.gov.uk [3]
    Website: www.pesticides.gov.uk [4]  

    I forgot to mention

    Kim

     

    the sender.

    lawful purposes.  

    Links:
    ------
    [1] mailto:martin.b...@psd.defra.gsi.gov.uk
    [2] http://www.pesticides.gov.uk/
    [3] mailto:martin.b...@psd.defra.gsi.gov.uk
    [4] http://www.pesticides.gov.uk/

    _______________________________________________
    Openroad-users mailing list Openroa...@peerlessit.com

    To unsubscribe please click on this link
    mailto:openroad-user...@peerlessit.com&subject=unsubscribe

    To subscribe please click on this link
    mailto:openroad-use...@peerlessit.com&subject=subscribe

    Reply all
    Reply to author
    Forward
    0 new messages