Importing XML data into a cursor inside a form

[Filip Brnic]

Hey, im struggling to find a good guide on how i can get the xml data into a cursor, i did saw a thred here im gonna try but i wanna hear you guys out if thats gonna be something different, what im trying to achieve is when i paste the xml url into my textbox and click on commandbutton, it will load the data into a cursro and show me that cursor, after that i would need to save that cursor into a grid, or maybe i need a permanent table instead, but i think the cursor will do for now, anyways i just wanna hear from people much experienced how should i start with this, and this is huge for what im doing, im trying to learn xml to fox relation because my firm needs that the most and i wanna help...

 

[Chris Miller]

You can't load any XML into a cursor, only some XML. And before someone comes in and says you can always store XML in a) a single Memo field or b) represent it in a data structure like xmltag, level, attributes, value, innerxml... The expectance of someone with this idea of XML as data is that you get a list of records like you'd get it from CSV. and that's not the case, most of the time.

The point is, that idea is very naively thinking of something that's more complex than you imagine.

I'd start with giving an overview picture of what you're actually dealing with, if you receive XML (or json or anything) from a webservice. What is actually the idea of a webserivice? It compares to the idea of a class library, with the biggest difference being that you don't instanciate a class and call its methods, you have a list of endpoint URLs you can request from, parameterized, which is like a class already up and running on a remote server, waiting to be called and giving responses aka return values to calls.

So the roughest picture you can have of web services is being a class or several classes, available to you, either publicly or after some login/authentication phase for making requests and getting responses. And for some of the webservices the responses are XML.

So what is XML in that analogy of programming? It's the return value of a call to a class/object, that has some purpose, any purpose. Your question therefore becomes: "How do I work on the return value of a method of a class?" Your question is a bit more specific as you ask about a return value that is XML. Well, the XML is actually just the wrapping of the data, but you think very naive, if you think xml can be converted to dbf. It's not just a file format issue.

So that's not the way to get into the topic.

Why are we even having such remote services running somewhere else and not a class library to add to your project and use much more directly and simpler? The major reasons, and there are too many to list them all, are that a web service is usually provided to give an customized interface and service running where it is approrpiate to run (near the data and further services it serves, simply speaking) and in any programming language as necessary.

So asking how to use web services is a bit like asking how to use a class library. It depends. And it differs, just like it would differ and depend on a class used, any class, for any purpose.

Web services have some things in common, of course. The web, obviously, and being services, servicing clients (your EXE, for example) and there are things you will need to know generally to make use of any web service:
1. HTTP protocol. That doesn't mean you need to dig into HTTP specifications like given at

https://www.rfc-editor.org/rfc/rfc9110.html

What you mainly will use is a http request class that will enable you to make requests and get reponses, for example wht you talked of, get an XML in response to a GET request of some URL.
It helps to know details of the HTTP protocol to know what's the difference of a GET vs a POST request, for example, and what each request type is good for, what it can transport, what headers exist, what belongs into the body of a request, etc.

2. XML. Again, you could learn your whole life about all details of XML, most of the time you would want to make use of libraries like XML parsers to handle the XML and get at the core of it. In the simplest form XML parsing is included in some of the http request classes I mentioned in 1.

3. JSON, not every web service provides XML as medium of transport. You may even come across many more specific formats.

4. Javascript: As this is the programming language of web clients, it pays to learn JS, because usage samples of web services will usually be provided in Javascript and that's what you will need to understand and maybe even use, the less you are able to translate this into VFP.

Where do you start? Making a web GET request of a URL and gettting an XML string that turns to a cursor with XMLToCursor. Store this into a PRG (not a form don't base learning omething new in a form, that only leads to unnecessary questions of the type where (into which event, method of which object) to put some code.

Local loHttp

loHttp = CREATEOBJECT("MSXML2.ServerXMLHTTP.6.0")

loHttp.open("GET","[URL unfurl="true"]https://gist.githubusercontent.com/Ram-N/5189462/raw/46db0b43ad7bf9cbd32a8932f3ab981bd4b4da7c/books.xml",.f.)[/URL]
loHttp.send()

XMLToCursor(loHttp.ResponseText,"csrBooks")
Browse

Don't expect this to be that simple for any XML, though.

The MSXML2.ServerXMLHTTP.6.0 has also built an XMLDocument into it including functions to use XPAth queries, knowing how to use that can be useful:

Public loHttp, loNodes

loHttp = CREATEOBJECT("MSXML2.ServerXMLHTTP.6.0")

loHttp.open("GET","[URL unfurl="true"]https://gist.githubusercontent.com/Ram-N/5189462/raw/46db0b43ad7bf9cbd32a8932f3ab981bd4b4da7c/books.xml",.f.)[/URL]
loHttp.send()

Clear 
Local loBookTitles, loBookTitle

? loHttp.getResponseHeader("Content-Type")
* usually not necessary, but as content-type is not XML...
loHTTP.ResponseXML.loadXML(loHTTP.ResponseText)

* using an XPath query to extract only titles of all books
loBookTitles = loHTTP.ResponseXML.selectNodes("//book/title")
For each loBookTitle in loBookTitles 
   ? loBookTitle.text
EndFor

XPath alone is a simple XML querying/parsing language that's useful to make use of the functionalities provided with MSXML2.ServerXMLHTTP.6.0 and other XMLHttpRequest classes, that's just what I mentioned in 2 above.

Chriss

 

[Piotr Czekalski]

XML is a text file. There is very good library of functions for text operations in VFP. I use the following method. I analyze XML files with data, their schemas and write statics code to extract data from a text file for a specific schema using VFP functions for processing text: FileToStr(), AT(), StrTran(), Stuff() etc. XMLToCursor works well but only for simple schemas. Using VFP is easy to export data to XML. When you importing, you need to do a little more work. I work with XML tax files from government systems. Declaration schemas are stable. They do not change often. Maybe that is why my method works. For XML data exchange to work efficiently in company, there must be stabile standardization of XML schemas in company documents processing.

I don't know XML well. I only know idea. Somehow I manage to solve problems with reading data my method.

 

[ggreen61]

If you know the structure of the xml and the structure does not change, then you can write a static procedure/function to parse the xml and load your cursor. However, if the xml structure is unknown at run-time, then you will need a xsd to define the structure. Then you will have to write code to parse the xsd and then use that information to parse the xml.

Greg

 

[Chris Miller]

Everybody should also know

https://west-wind.com/,

when it comes to the web.

For your purpose the two most relevant products are Internet & Client Tools and the West Wind Web Service Proxy Generator for Visual FoxPro. The latter specializes on the consumption of .NET Web services, not any web service, but the proxy idea is making use of the fundamental idea that a web service is like a class exposing some methods and is generating such a class you use more directly, all web related issues are covered by the proxy, which is why it's called a proxy, that's the meaning of being a proxy class.

Chriss

 

[Chris Miller]

You can also learn a bit about the XML which specifically resembles table data, by using the inverse VFP function of CursorToXML and inspecting the XML it generates:

Open Database (_samples+"Northwind\Northwind.dbc")
Use Customers

lcXML=""
CursorToXML("customers","lcXML",1,8,2,"")

That generates this XML (limited to 2 records by the 5th parameter nRecords:

[pre]<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<VFPData>
<customers>
<customerid>ALFKI</customerid>
<companyname>Alfreds Futterkiste</companyname>
<contactname>Maria Anders</contactname>
<contacttitle>Sales Representative</contacttitle>
<address>Obere Str. 57</address>
<city>Berlin</city>
<postalcode>12209</postalcode>
<country>Germany</country>
<phone>030-0074321</phone>
<fax>030-0076545</fax>
</customers>
<customers>
<customerid>ANATR</customerid>
<companyname>Ana Trujillo Emparedados y helados</companyname>
<contactname>Ana Trujillo</contactname>
<contacttitle>Owner</contacttitle>
<address>Avda. de la Constitución 2222</address>
<city>México D.F.</city>
<postalcode>05021</postalcode>
<country>Mexico</country>
<phone>(5) 555-4729</phone>
<fax>(5) 555-3745</fax>
</customers>
</VFPData>[/pre]

It's also not usíng what Greg mentioned: XSD - XML schema definition. Just like the books xml example from

https://gist.githubusercontent.com/...b43ad7bf9cbd32a8932f3ab981bd4b4da7c/books.xml

is not using XSD. Such xml is lax and not specifying exactly what the xml elements contain.

For example the postalcode 12209 of ALFKI does not tell, whether it's the integer number 12209 or the string "12209". The postal code 05021 of ANATR with a leading zero hints at that column being a char data type, not integer or number or float - which it also could be. So such XML without XSD is not specifying what data types the elements have, just like it is with CSV.

So it can be turned into a cursor with the same guessing - infering is the offical term - of the data types. Well, when I'm precise a bit worse than in the CSV scenario, because in case of appending CSV to a cursor you have to do the job of defining the cursor and its columns with specific data types, in this case XMLTOCursor can only guess data type. The advantage is you don't have to predefine a cursor.

But let's see what happens with XMLTOCursor of such XML:

You see, VFPs guess is wrong, the 05021 is turned into 5021, because turning that XML without specific about the elements VFP uses an integer field for Postalcode. It would fail miserable with postalcodes like "WA1 1DP" as it is for a London customer in the Northwind database.

The problem can't be solved by a predefined cursor, XMLTOCursor will dump that and recreate one with the alias as given. So it's best, of course, to have XML with a schema. You can't choose to have that, though, you have to take what's given by the webservice. And that's one of the problems you'll be facing.

The other is, that XSDs, even if given, can be so complex, that VFP can't interpret them correctly with its outdated XML functionalities. Always have in mind VFP is from 2005, the last fixes of VFP were fropm 2009, but not releated to XML. So VFP is always behind the current state of the art XML, even when it uses Microsofts XML libraries, because the VFP XML related functions are based on and always use XML3 and XML4, nothing newer. That said, you get far with even that old functionalities, as data types haven't changed much since they were standardized.

Well, take that aside and see what XML you have to have to be able to turn it into one cursor, really. In the sense of a) having all data in that cursor after the parsing and b) having it in the right data types, ideally, too.

The XML structure is pretty formatted, here, so it's quite easy to spot that curesor fields are having tags for each fieldname, repeatedly, and are wrapped in a tag <customers>...</customers>, which verall is wrapped in a parent tag, here simply "VFPData" as it came from VFP. That's <catalog> in the books.xml case, so you see XMLToCursor does not look specifically for "VFPData" tags. But this structure has to exist, otherwise XMLToCursor fails, sometimes more miserably than other times and not always by throwing an error, it can fail quite silently.

So you don't get around what Piotr and Greg hinted at: Writing some request related conversion yourself, case by case. If you seek for general parsing of any XML, it's really best to find an XML library that is recent, at best with an active maintainer, which is true for Microsofts XML COM classes, for example.

The XSD VFP can create, when you use CursorToXML with appropriate parameterization can look like this:

	<xsd:schema id="VFPData" xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL] xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
		<xsd:element name="VFPData" msdata:IsDataSet="true">
			<xsd:complexType>
				<xsd:choice maxOccurs="unbounded">
					<xsd:element name="customers" minOccurs="0" maxOccurs="unbounded">
						<xsd:complexType>
							<xsd:sequence>
								<xsd:element name="customerid">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="5"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
								<xsd:element name="companyname">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="40"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
								<xsd:element name="contactname" minOccurs="0">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="30"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
								<xsd:element name="contacttitle" minOccurs="0">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="30"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
								<xsd:element name="address" minOccurs="0">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="60"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
								<xsd:element name="city" minOccurs="0">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="15"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
								<xsd:element name="region" minOccurs="0">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="15"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
[highlight #FCE94F]								<xsd:element name="postalcode" minOccurs="0">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="10"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>[/highlight]
								<xsd:element name="country" minOccurs="0">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="15"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
								<xsd:element name="phone" minOccurs="0">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="24"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
								<xsd:element name="fax" minOccurs="0">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="24"/>
										</xsd:restriction>
									</xsd:simpleType>
								</xsd:element>
							</xsd:sequence>
						</xsd:complexType>
					</xsd:element>
				</xsd:choice>
				<xsd:anyAttribute namespace="[URL unfurl="true"]http://www.w3.org/XML/1998/namespace"[/URL] processContents="lax"/>
			</xsd:complexType>
		</xsd:element>
	</xsd:schema>

Here I highlited the portion that defines postalcode as a char(10) field, mainly, with data type definition as XML knows them, not a database, so char compares to xsd:string and <xsd:maxLength value="10"/> actually is comparable to a varchar(10) with up to 10 characters. So even with XSD you don't have a 1:1 representation of data types. I checked and XMLToCursor converts that back to a char(10) field, but don't rely on anything, you always will need to verify results about whether they have the quality you need and correspond to what is intended to come out.

Chriss

 

[Piotr Czekalski]

Alternatively example of extracting company names from XML document.

**************
* load XML Document to Variable
* Alternatively FileToStr()
***************

Set Console OFF

Text To XMLBuf

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<VFPData>
	<customers>
		<customerid>ALFKI</customerid>
		<companyname>Alfreds Futterkiste</companyname>
		<contactname>Maria Anders</contactname>
		<contacttitle>Sales Representative</contacttitle>
		<address>Obere Str. 57</address>
		<city>Berlin</city>
		<postalcode>12209</postalcode>
		<country>Germany</country>
		<phone>030-0074321</phone>
		<fax>030-0076545</fax>
	</customers>
	<customers>
		<customerid>ANATR</customerid>
		<companyname>Ana Trujillo Emparedados y helados</companyname>
		<contactname>Ana Trujillo</contactname>
		<contacttitle>Owner</contacttitle>
		<address>Avda. de la Constitución 2222</address>
		<city>México D.F.</city>
		<postalcode>05021</postalcode>
		<country>Mexico</country>
		<phone>(5) 555-4729</phone>
		<fax>(5) 555-3745</fax>
	</customers>
</VFPData> 


Endtext

Set Console ON

*************
* End loading
**************




***********
* Code
**********

XLen1=Len("<companyname>")
XLen2=Len("</companyname>") 

Xn=0

Do While .T.
  Xn=Xn+1
  XAt1=AT("<companyname>",XMLBuf,Xn)
  XAt2=AT("</companyname>",XMLBuf,Xn)
  IF XAt1>0
    XCompanyName=Substr(XMLBuf,XAt1+XLen1,XAt2-(XAt1+XLen1))
    ***
    Wait  "Company: "+XCompanyName Wind
    ***
  Else
    Exit
  EndIF
EndDo


Retu

 

[tomk3]

Hello,

as mentioned its basically 2 steps
- getting the value from the internet, see chris' post
- parsing and storing into cursor

you may also look on famous CHILKAT - Library.
Its not free but covers many aspects on "Webthings" and has excellent logging and VFP samples
(we never found another way to get data together with X509 certificates)

https://www.example-code.com/foxpro/http_quickgetstr.asp

https://www.example-code.com/foxpro/xml.asp

For parsing you may also have a look on MSDOM

 oxml = Createobject('MSXML2.DomDocument')
  oxml.Async = .F.
  oxml.Load(lcXMLFile)
  orootnode = oxml.documentElement
  onodelist = orootnode.getElementsByTagName("INFO")
  nanznodes = onodelist.Length

  For nPos = 0 To (nanznodes-1) Step 1
    Select c_result
    Scatter Memo Name oziel
    oNode = onodelist.Item(nPos) && INFO
    * lbHasChild = oNode.hasChildNodes()
    oziel.id = oNode.getElementsByTagName("ID").Item[0].nodetypedvalue
    oziel.datum = oNode.getElementsByTagName("SDATUM").Item[0].nodetypedvalue
    * ...
    *  the nodes INFO may have subnodes
    * osub = oNode.getElementsByTagName("SUBDATA")
    * oziel.vxname =  osub.Item[0].getElementsByTagName("VNAME").Item[0].nodetypedvalue 
    INSERT INTO c_result FROM NAME oziel
 NEXT 
 
 SELECT c_result
 BROWSE