Consume XML from a REST API 1

[cfsjohn]

I am acessing a REST API. It returns XML.

I am trying to put that XML into a VFP9/foxpro cursor. I have never done this before.

I call the service passing it a request in XML format. No problems with that part.
lcXmlResult = loHttp.Post("

http://10.81.1.22:4081/DataExchange/REST",

lcXml, "c:\temp\xmlreturned.xml")

XMLTOCURSOR("c:\temp\xmlreturned.xml","",512)

It creates the cursor named XMLResult.
It creates 1 column in the cursor named Response. The field type is Memo.
It adds 5 records all of which are empty except the 5th record where the memo field contains what looks like all of the XML in that one memo field.

I have attached the xmlreturned.xml file.
It looks like I am getting 1 person everytime you see the node <JailInmateTable>
In a perfect world, when I issue the XMLTOCURSOR, I would get 1 row in the cursor per instance of <JailInmateTable> with every node being a column.
Perhaps it would be easier to just read the file as a text file and parse out what I need but I was hoping that would not be necessary.

Thanks,
John

 

[Chris Miller]

You're expecting too much from XMLToCursor.

It creates a cursor. It tries to infer a table structure which matches the XML and it often fails because
1. Not every XML is encoding a table. Which most often is the reason and leads to results like yours. It's not a failure of VFP nor the webservice. It's XML which needs to be processed in other ways.
2. Even XML that resembles a list of records with same structure per record (aka a table) is in a format that XMLTOCURSOR can't parse.
3. Psst....mostly XMLTOCURSOR is able to process XML you generated with CURSORTOXML, the least number of web services is programmed in VFP and even those would perhaps not just use CURSORTOXML for the API response.

So in short you can usually forget to use XMLTOCURSOR, at least you have to extract or modify the XML to meet the structure for XMLTOCURSOR. Parsing XML is not teached in one forum thread and of course not one response. From what you attached the part of the XML that is resembling records is within the XML tag <Response>, so the XML is two parts: First some information about the request, then a table called JailInmateTable with 6 records.

What you can try is to extract exactly that part with STREXTRACT():

lcXML = FileToStr("xmlreturned.xml")
lcXML = '<xml>'+StrExtract(lcXML,'<Response>','</Response>',1,1)+'</xml>'
? XMLToCursor(lcXML,'JailInmates',0)

Notice, that's working here, generally the first step of parsing XML is looking at the XML yourself. And understanding basic principles you might know when you know HTML or any other tag based system, even this forums simple TGML: "Things" are store between an opening and closing tag like <Response>...</Response>.

Chriss

 

[cfsjohn]

Chris, I am not one to critique people that hang out on these forums and try to help others because I stay so busy that I have almost no time to do so. Having said that I do want to tell everyone that if they pay attention to detail and actually really really read the questions the way that you obviously do, it would be a wonderful world. You have and deserve my utmost respect.

Your answer is spot on! I was afraid that would be the answer but at least I do know what to do. The code you offered, really helps. I got records in a cursor. If I have the data in a cursor, I can figure out how to parse it out!

BTW, Are you available to hire for projects?
Thanks so much,
John

 

[tomk3]

Hello,

you may have a look on great tools in vfpx

https://vfpx.github.io/projects/

exspecially

https://github.com/VFPX/nfXML

Regards
tom

 

[Chris Miller]

Good to hear that, no need to hold back any criticism.

One thing I skipped over with my solution is everything outside the <Response> tag. That's mainly the <Creation>Spillman XML Query Server</Creation> tag and some more empty or without mention worth information. They are the reaons the simple approach of XMLTOCURSOR fails. If you look for a very general solution, there is none really satisfactorial. You can always parse and convert XML to an object, that's always the most successful way to look at XML, as every tag can be made a property or collection of a root object "oXML". toms pointer to gitHub is about such parsers, Rick Strahl also offers a wwXML class in his client tools. Worth looking into.

In this specific case it was very clear the data is within the Response node. It's not always that way, so I didn't hold back on the advice to always first have a look at what you receive from an API and also that hint, you only need this one base knowledge of opening/closing tags to nest a subset of xml nodes. Once you also know STREXTRACT that allows you to extract almost anything without going to other means like the query language that's also at hand whenever it comes to XML: xpath queries. It's abit more powerful but then also as complicated and a steep hurdle to learn just like regular expressions are. So the hint on that and how tags work are just a motivation to get a grip with only shallow knowledge of something, too.

Chriss

 

[cfsjohn]

I use Rick's Web Connection extensively. I am in the process of developing a website (no Foxpro) and using WC to write the foxpro business object so the website can access my VFP database. Basically the website is just the front end. I also read and downloaded the vfpx XML project (Thank you Tom) but as you have said, it looks like it would still be pretty complex but maybe not if I spend more time looking at it.

Thanks again,
John

 

[TamarGranor]

Take a look at the XMLAdapter class. It has more capability than XMLToCursor() and may give you what you need.

Tamar

 

[Chris Miller]

That's right, with the LoadXML() method of the XMLAdapter class you can always a) find out whether it's valid XML, though you could assume the web API shouldn't be botching this that much, there are simple APIs that produce simplified XML but also the basis of XMLAdapter is MSXML4, IIRC, and that's quite outdated.

The other thing XMLAdapter gives you is b) an XML DOM - document object model. Well, in short an object. But one rooted in Microsoft XML libraries that include functionality like XPATH queries and functions like childnodes, getElmementsByTagName, etc. to look into the nodes of an object tree that a DOM is, no matter if XML DOM or HTML DOM, if you're familiar with that.

To get an XML DOM you could also go for Msxml2.DOMDocument.6.0 and thereby use MSXML version 6. What XMLADapter also offers is a collection of XMLTables. In your specific case there's nothing found. There are some options that can be used to parse the same XML in different way, XMLAdapter can be given an XMLSchema, which means the verification is more specific than just general XML validity and then you can let XMLAdapter accpet nesting or not, but some of these things are also settings you would use in the case you wanted to go from several VFP tables or cursors or workareas to one XML file containing all that data.

I played a bit with XMLAdapter and it didn't find the JailInmatesTable.

But generally speaking, yes, the XMLAdapter includes the Msxml2.DOMDocument.4.0 as XML XML parser turning XML strings or files into an XML DOM, that is a treeview of nodes. It could also be used to complete the extraction of informations in the XML nodes outside of <Response>.

Chriss