Looking for the correct way I should be reading a XML file.

[DAG0000]

Re: Looking for the correct way I should be reading a XML file.

Thanks for the help guys. For this file I don't think XML-INTO is going to cut it. From all of the recommendations I've been given and the examples I have looked at I am beginning to think I will have to load the entire work order to get what I need. I've been told to expect thousands of Prospectuses per work order, so I will potentially be blowing out the data structures. I'm only receiving the files and I don't have any say into how they are built. Since I'm on a short time scale to get something working, I've taught a coworker how to read the file off the IFS and he is writing a program to scan for the tags and parse it that way. This would be a temporary fix until we can get a real parser working. I'll most likely look into trying XML-SAX to do this.

[Henrik Rutzou]

Re: Looking for the correct way I should be reading a XML file.

could you please explain what a "real XML parser" is ?

[GLS400]

Re: Looking for the correct way I should be reading a XML file.

Hi Dag:

Just for ha-ha's try to import the xml into xls

If that works put it into csv and cpyfrmimpf

Best of Luck
GLS

[Henrik Rutzou]

Re: Looking for the correct way I should be reading a XML file.

Hi,

Actually I do agree that the powerEXT xmlReader() isn't really a "real xml-reader" it is rather a "markup language reader" that is far more advanced than XML-INTO, XML-SAX and EXPAT.

XML is a markup language in a very strict format, HTML is the same markup language in a very loose format where you are able to have data (such a javascript and CSS) in between elements and elements that isn't closed in the element such as 'p' elements.

powerEXT xmlReader() are constructed to read both and none of the above other XML readers can do the job.

Let me give you an example of reading HTML markup, it's a 72 statements RPGLE program that uses Scott's HTTPAPI to read the HTML from the current tread (about 150K) and search for any references (html "a" tags) to other pages in the current tread and then construct an HTML page with the result.

For the fun I just added 10 statements to extract the XML file posted as code in message 3 (you have to browse down to see the textarea I have imbedded it into):

http://216.109.205.54:8080/pextcgiCOR/testread.pgm

[DAG0000]

Re: Looking for the correct way I should be reading a XML file.

Henrik,

I wasn't implying anything about PowerEXT, I was commenting on the temporary approach we are taking at this time to read the file.


GLS400.

They need to pay me more if they expect me to get up at 3 am everyday to import some of these..

[GLS400]

Re: Looking for the correct way I should be reading a XML file.

Hi DAG0000:
Just an option but it can be placed into a auto-execute macro:

Sub GETXML()
'
' GETXML Macro
'
'
    ActiveWorkbook.XmlImport [URL="http://www.code400.com/forum/="H:\MYXML.XML"]URL:="H:\MYXML.XML[/URL]", ImportMap:=Nothing, _
        Overwrite:=True, Destination:=Range("$A$1")
    ActiveWorkbook.SaveAs Filename:="H:\MYCSV.CSV", FileFormat:=xlCSVMSDOS, _
        CreateBackup:=False
        ActiveWorkbook.Close (SAVECHANGES = False)
End Sub

Don't get me wrong ..... I'm not pushing this method....only suggesting an alternative

Best of Luck
GLS

[MichaelCatalani]

Re: Looking for the correct way I should be reading a XML file.

I've been on the road the past couple of days, so I havent had time to respond.

The approach that I would take with XML -Into is to not attempt to read the entire multi-structure data structure at one time, but to break up each data structure fetch with multiple xml-into's.

For instance:

Procedure 1 - Fetch Work Orders
Procedure 2 - Fetch Prospectuses
Procedure 3 - Fetch Brokers
Procedure 4 - Fetch Accounts


In the first procedure, position the xml to the work order level, and then start fetching. Each time you fetch, call pocedure two.

On the call to procedure 2, position the xml to the prospectus level of the current work order. Start fetching. On each fetch, call procedure 3.

On the call to procedure 3, position the xml to the broker level for the work order / prospectus. On each fetch, call procedure 4.

On the call to procedure 4, position the xml to the account level for the work order/ prospectus broker.


This is essentially nesting the xml-into's into a logical organization of heirachy. Ths also keeps you from having to utilize a data structure that looks like WorkOrders.Prospectus.Broker.Acount.AccountNumber.

[DAG0000]

Re: Looking for the correct way I should be reading a XML file.

I thought about trying something like that but I don't think it would work. Wouldn't XML-INTO try to pull in all of the accounts in the file. At which point I still have no way of associating which account goes to which broker and so forth for each level.

[MichaelCatalani]

Re: Looking for the correct way I should be reading a XML file.

I thought about trying something like that but I don't think it would work. Wouldn't XML-INTO try to pull in all of the accounts in the file. At which point I still have no way of associating which account goes to which broker and so forth for each level.

Not if you use the path statement to direct it to a specific broker first. When I get a moment I'll look to see how I pulled this off. I cant remember if I was allowed to point the path statement directly to a specific starting element, or if I %scanned the document for the starting position of the element, and substringed out the starting position to the end of the document, and xml-into'd with that data.