Trying to use your XML Parser 2

[eric0524]

I am trying to use the code you posted a long time ago that creates multiple cursors from an XML but I am getting an error "OMXL is not found" error. I am trying to run it by highlighting the code in a prg file. Can you tell me what boneheaded mistake I am making here? PLEASE!

* test
Local loXMLRequest, lcXML, loXMLAdapter, loXMLTable
Close Tables All
loXMLRequest = CreateObject("Msxml2.xmlhttp")
loXMLRequest.open("GET","

http://www.treasury.gov/ofac/downloads/sdn.xml",.F.)

loXMLRequest.send(.null.)
lcXML = oXML.responseText // this is where I get my error, I am using VFP 9
loXMLAdapter = CreateObject("XMLAdapter")
loXMLAdapter.LoadXML(lcXML,.F.,.T.)
For Each loXMLTable in loXMLAdapter.Tables
loXMLTable.ToCursor()
EndFor
SET

 

[foxdev2]

it seems to me you are going to a lot of trouble. I use VFP 8.
I just use the function lnRows=XMLTOCURSOR(path+file.xml,cursorname to fill,512). This assumes you have the xml file formatted correctly. It returns the number of rows in the cursor. You can still loop thru an array of xml files to convert to cursors.

 

[Mike Lewis]

Looks like a simple typing error. Instead of oXML.responseText, it looks like you need loXMLRequest.responseText.

But I haven't tested that, so try it and see what difference in makes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[eric0524]

Mike, I tried that. It just blows through. I am stuck with VFP 9 if I had my way it would be using VFP 7 I know the XML file has 10 tables in it. I have installed all the new service packs for VFP and MSXML4.

 

[Olaf Doschke]

First of all, Mike is right, but you also must use Foxpro Syntax for comments after code as && instead of //. Of course otherwise the first / is taken as operator. And this hasn't changed since VFP7.
Seconds, this XML does not contain 10 tables. I can convert it to one cursor via XMLTOCURSOR:

* test
Local loXMLRequest, lcXML, loXMLAdapter, loXMLTable
Close Tables All 
loXMLRequest = CreateObject("Msxml2.xmlhttp")
loXMLRequest.open("GET","[URL unfurl="true"]http://www.treasury.gov/ofac/downloads/sdn.xml",.F.)[/URL]
loXMLRequest.send(.null.)
lcXML = loXMLRequest.responseText && this is where I get my error, I am using VFP 9
XMLToCursor(lcXML,"curTest")
SET

Bye, Olaf.

 

[Mike Lewis]

Eric,

You say you tried my suggestion and it "just blows through". I'm sorry, but could you explain what you mean by that. Do you mean that the program ran to the end, or that it failed in some other way? If the latter, what exactly happens? Did you get past the original error, or what?

Also, I'm not clear why you say you are "stuck with VFP 9". The problem was a simple syntax error - using the wrong object reference to access a certain method. As far as I know, there's nothing version-specific in that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[eric0524]

OlafDoschke, You are right it can create one cursor that way. But I was told that there are several tables within the XML file and they update the XML file every time a new bad guy is found. Sorry about the syntax issue I just put that in after I pasted the data from VFP. Your code now is returning a cursor with 5369 records in it. I was able to get that too when I made the change that Mike suggested. I think where I am having a problems is that, like under AKA it can have several records and it is just putting them in a memo field and each AKA has a record identifier and the same with Address. There might not be a way to bring them in any other way. They give me the structure of the XML file at

http://www.treasury.gov/resource-center/sanctions/SDN-List/Documents/sdn.xsd

is there anything I can use out of that? I really don't understand XML very well and this is the first time I have had to use one. Thanks for your help.
-Eric

 

[Olaf Doschke]

Yes, that schema is helpful. Mainly it shows all that XML has nested data, in about same way as an SQL Join of many tables puts data into just one result tabel or cursor.

I can load the schema into firefox (any browser is capable to show a treeview) identify these "tables":
1. sdnList
2. sdnEntry
3. idList
4. akaList
5. addressList
6. nationalityList
7. citizenshipList
8. dateOfBirthList
9. placeOfBirthList
10. vesselInfo

You can see them from the Treeview, when collapsing detail nodes:

https://docs.google.com/open?id=0B-gN-YdXB9YPemdhek94S05ZWVU

The nature of the xml is to nest joined data into the records xml elements of the main table sndList, this why you can't parse out or convert the single tables, you have to postprocess the one cursor you get to extract the records in a "normalised" way. It is your startpoint for your postprocessing, unless you find empty list fields, where there should be detail data. But I can't judge that for you.

Select publish_date,record_count from curTest where !Empty(publish_date) into cursor curHeadInfo
Select uid,Firstname,Lastname,Title,SdnType,remarks From curTest where !Empty(uid) into cursor curSdnList
...

Bye, Olaf.

 

[eric0524]

Thank You Olaf. You have got me on track now. I am going to use the CSV files and them together as needed.
-Eric