NEED HELP TO IMPORT AN XML FILE TO A CURSOR 3

[PatMcLaughlin]

Being a newby to fox pro and programing, I am having a horrendous time trying to figure out how to get an xml file sent to us by a third party into a cursor so I can append another document (dbf) with the imported data. The xml will never have all of the fields of the dbf, but the dbf will have the all of the fields in the xml. I don't understand enough to even get past the syntax errors trying to use 'xmltocursor' (foxprow 7.0).

 

[Mike Lewis]

Hello Pat,

Welcome to the forum - and also to Visual FoxPro.

You're jumping in the deep end. Converting XML to a DBF file isn't particularly difficult, but it pre-supposes quite a lot of VFP knowledge.

Here's an outline of some code that might get you started:

* Read the XML file into a memory variable
lcXML = FILETOSTR("c:\data\SomeXMLFile.xml")

loXML = CREATEOBJECT("XMLAdapter")
	
* Convert XML to one or more cursor
WITH loXML
  .LoadXML(lcXML)
  FOR lnI = 1 TO .Tables.Count
    lcCursorName = "SomeCursor" + TRANSFORM(lnI)
    USE IN SELECT(lcCursorName)
    .Tables(lnI).ToCursor(.F., lcCursorName)
  ENDFOR
ENDWITH

You'll end up with one or more cursor, name SomeCursor1, SomeCursor2, etc.

A cursor is just a temporary table, which is stored in memory, and which disappears when it is closed. So, after you get the above code working, you'll need to come back and ask how to combine the data with your existing DBF.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[TamarGranor]

The solution Mike gave you works in VFP 8 and 9. Your message seems to indicate that you're in VFP 7. Unless you have a compelling reason to stick with that version, I'll strongly recommend you upgrade to the latest, which is VFP 9, and then download and apply SP2 and the various hotfixes that are available.

That way, you'll be working with the most up-to-date version and have the most capability available. Your VFP 7 should be easily compatible with VFP 9.

If you're really stuck in VFP 7, you might be able to use XMLToCursor(), but that depends on the structure of the XML. It's fairly limited; in particular, it doesn't handle nested tables in the XML.


Tamar

 

[Mike Lewis]

Tamar's right. I had taken in that you're using VFP 7.0. I agree with her suggestion to upgrade to VFP 9.0 if at all possible.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

With XMLToCursor one parameterization would be:

XMLToCursor(GetFile("xml"),"curResult",512)

512 at in the third nFlag Parameter means the first one is a file name, which GetFile() will return.

As the others already said XMLToCursor is of limited usage, it can't convert nested data, it will just create one dbf or cursor (like curResult), so the xml can't contain data for more than one table.

Once you've done that and the call does not error, do SET in the command window to show the data session window and see, whether there is a cursor aliased curResult with the data you expected from the xml.

Otherwise besides upgrading, which surely is stringly recommendable, if you pla to do much with foxpro, another way is to make use of WestWinds XML classes, see

http://www.west-wind.com/wwxml.asp

Bye, Olaf.

 

[PatMcLaughlin]

I finally got it to run without error but do not understand the SET instructions. I used BROWSE and can see the last record only, the data is there but not parsed into fields... it is all in one without spaces. There are 6 records in the test xml, but only the last is seen.

 

[Olaf Doschke]

Not "the SET instrictionS", just SET. SET alone shows the data session window.

If you only see one field of one record, the XML obviously is not structered as a single simple table.

See how it should be structured by using CursorToXML with a cursor with some data in it:

create cursor curTest (id I, cText C(10))
insert into curTest Values (1,"hello")
insert into curTest Values (2,"world")

CursorToXML("curTest","lcXML",1,50,0,"1")

This results in

<?xml version = "1.0" encoding="UTF-8" standalone="yes"?>
<VFPData>
	<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="curtest" minOccurs="0" maxOccurs="unbounded">
						<xsd:complexType>
							<xsd:sequence>
								<xsd:element name="id" type="xsd:int"></xsd:element>
								<xsd:element name="ctext">
									<xsd:simpleType>
										<xsd:restriction base="xsd:string">
											<xsd:maxLength value="10"></xsd:maxLength>
										</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:anyAttribute>
			</xsd:complexType>
		</xsd:element>
	</xsd:schema>
	<curtest>
		<id>1</id>
		<ctext>hello</ctext>
	</curtest>
	<curtest>
		<id>2</id>
		<ctext>world</ctext>
	</curtest>
</VFPData>

It also works without the last parameter "1", not creating the xsd inline schema part.

XMLToCursor shurley is no one size fits all command.

Bye, Olaf.

 

[PatMcLaughlin]

According to my boss, my predisessor had taken care of the vfp9 and has emailed him that we create an object and run it like the following:

*Create vfp9dll object
x = CREATEOBJECT("vfp9dll.vfp9")
XMLTOCURSOR(x.GETFILELIST("h:\Survey\testxml2.xml","XML"),"FILELIST",4)
		
SELECT FILELIST
SCAN
......
ENDSCAN

When this ran there were no error messages but I could find no data in the cursor "Filelist"

I Then tried to apply Mike Lewis' method as follows:

lcXML = FILETOSTR("h:\Survey\testxml2.xml")
*Create vfp9dll object
loXML = CREATEOBJECT("vfp9dll.vfp9")

WITH loXML
	.LoadXML(lcXML)
	FOR lnI = 1 TO .Tables.Count
		lcCursorName = "Test" + TRANSFORM(lnI)
		USE IN SELECT(lcCursorName)
		.Tables(lnI).ToCursor(.F., lcCursorName)
	ENDFOR
ENDWITH
		
SELECT lcCursorName

BROWSE

This throws an error on the '.LoadXML(lcXML)' line stating:

OLE error code 0x80020006: Unknown Name.

This newby is not winning.

 

[Mike Lewis]

I Then tried to apply Mike Lewis' method as follows:

Sorry, but you're way off track.

My code had this line:

loXML = CREATEOBJECT("XMLAdapter")

Your code has this:

loXML = CREATEOBJECT("vfp9dll.vfp9")

There's a world of difference.

Try going back to my original code and see what you get.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[PatMcLaughlin]

It errors out on that line with:

Class definition XMLAdapter is not found.

 

[Mike Lewis]

Pat,

You said:

It errors out on that line with:
Class definition XMLAdapter is not found.

But that doesn't mean you should substitute a different line.

The CREATEBOJECT() function creates an object which is based on the class whose name you pass as a parameter. If it can't find the XMLAdapter class, there's no point in passing the vfp9dll.vfp9 class. There's no connection between the two.

Looking back over this thread, I see that we already discussed this. The reason for the error is that you are using VFP 7.0. The XMLAdapter requires 8.0 or later.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Talk back to the programmer of the vfp9dll.vfp9. I have no clue what it's GETFILELIST method does, but judging from the parameters of XMLTOCURSOR that paramter x.GETFILELIST() must be XML. It may take out a portion of the XML to enable VFP7 to process it with the simple XMLTOCURSOR().

Once again, XMLTOCURSOR() is not a one size fits all solution, meaning not any XML can be parsed into a cursor. And it seems the GETFILELIST() method also is not capable in case of your special XML file to extract an XML portion VFP7s XMLTOCURSOR can cope with.

Next step would be to present the source code of the vfp9dll.vfp9 class and it's GETFILELIST function to see what that actually does and returns.

What you could do as a first step without having that source code is to post the output, that is:

x = CREATEOBJECT("vfp9dll.vfp9")
STRTOFILE(x.GETFILELIST("h:\Survey\testxml2.xml","XML"),PutFile("","partial.xml"))

Judging from the method name GETFILELIST should return a list of files, not XML. But that may be the meaning of the second parameter "XML". Show the partial.xml file created (if it's lengthy then the first few lines) and we can see.

Bye, Olaf.

 

[PatMcLaughlin]

Using this code...

lcFromDir = "h:\Survey\testxml26.xml"
*Create vfp9dll object
x = CREATEOBJECT("vfp9dll.vfp9")
XMLTOCURSOR(x.GETFILELIST(lcFromDir,"XML"), PUTFILE("","partial.xml"))

Gives me the following error...

XML Parse error: Syntax error. node "XML = <xsd:schema id = "VFPData"
xmlns:xsd="

http://www.w3.org/2001/XMLSchema"

xmlns:msdata="urn:schemas-micr

Do I need to write a separate schema and have them send strictly the data?

 

[Olaf Doschke]

reread, I said STRTOFILE, not XMLTOCURSOR.

Bye, Olaf.

 

[PatMcLaughlin]

SORRY FOR THE MISTAKE! Thank you for your patience. We at least have some success with this! Below you will find the contents of partial.xml.

  <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
- <VFPData xml:space="preserve">
  
- <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="crsdirs" minOccurs="0" maxOccurs="unbounded">
  
- <xsd:complexType>
  
- <xsd:sequence>
  
- <xsd:element name="path">
  
- <xsd:simpleType>
  
- <xsd:restriction base="xsd:string">
  
  <xsd:maxLength value="60" /> 
  
  </xsd:restriction>
  
  </xsd:simpleType>
  
  </xsd:element>
  
- <xsd:element name="filename">
  
- <xsd:simpleType>
  
- <xsd:restriction base="xsd:string">
  
  <xsd:maxLength value="64" /> 
  
  </xsd:restriction>
  
  </xsd:simpleType>
  
  </xsd:element>
  
- <xsd:element name="filesize">
  
- <xsd:simpleType>
  
- <xsd:restriction base="xsd:decimal">
  
  <xsd:totalDigits value="10" /> 
  
  <xsd:fractionDigits value="0" /> 
  
  </xsd:restriction>
  
  </xsd:simpleType>
  
  </xsd:element>
  
  <xsd:element name="filedate" type="xsd:date" /> 
  
- <xsd:element name="filetime">
  
- <xsd:simpleType>
  
- <xsd:restriction base="xsd:string">
  
  <xsd:maxLength value="8" /> 
  
  </xsd:restriction>
  
  </xsd:simpleType>
  
  </xsd:element>
  
- <xsd:element name="fileattr">
  
- <xsd:simpleType>
  
- <xsd:restriction base="xsd:string">
  
  <xsd:maxLength value="5" /> 
  
  </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>
  
  </VFPData>

Attachment is the test xml I have been using.

 

[Olaf Doschke]

I think you still don't understand. I would want to see the output of x.GETFILELIST(lcFromDir,"XML"), not the original XML file.

You XMLTOCURSOR takes the output of the vfp9dll.vfp9 object as it's input, and I'd like to see that. As I don't have the code of that object it's hard to judge what's going on and leading to your error.

Nevertheless I can convert that testxml26.xml download without any preprocessing besides removing the first three bytes, which are a BOM (Byte Order Mark). But I don't think that is the real problem, as you report another xml parse error.

I get 6 records out of that XML in VFP9 via

XMLToCursor(Substr(FileToStr("testxml26.xml"),4),"curTest",4)

I just need the Substr() to remove the BOM.

If the output of the x.GETFILELIST(lcFromDir,"XML") call actually is a filename, then your XMLTOCURSOR has really a very simple error: Look up the meaning of it's last parameter, which you set as 4. This means you have not set the Bit valued 512 in that flag parameter and this means the first parameter of XMLTOCURSO is interpreted as XML and not an XML file name.

So depending on the third parameter of XMLTOCURSOR the first one needs to be an XML file name or the XML itself.

Bye, Olaf.

 

[PatMcLaughlin]

I (Thanks to Olaf and Mike) have some success... I convinced my superiors to upgrade to VFP9.0, I installed all the updates and SP's, and I have gotten the test xml into a cursor both by using Olaf's code and Mike's original post. However, if I "Browse" the cursor, all non numeric fields are (memo) fields instead of data. (Values are correct in the memo fields). I then created a cursor with each field defined and tried again using Mike's code (

 lcCursorName = "SomeCursor" + TRANSFORM(lnI)

became

 lcCursorName = "SomeCursor"

with the same result. After I get this xml into the program, I will be appending a FoxPro .dbf file with the data (the program using this data was written in Foxpro 2.6) where it has been my experience, memo fields are problematic. Am I at least on the right track, being paranoid, or is memo fields all I can get?
One more question... The text xml contains an inline schema where the production xml data will either have no schema or will point to a local .xsd on the first line of the xml. The test xml's in this format error out when I run this type script. (These work in .Net using XMLReadMode.InferSchema) Is there an equivalent in VFP?

 

[Olaf Doschke]

An XSD Schema can help XMLTOCursor to create the right field types. But it's perhaps easier you predefine a cursor with the structure you need and then do XMLToCursor. You only need to set the 8192 valued bit in the nFlag parameter, otherwise VFP closes the cursor and creates the cursor with memo fields again.

A possible way:

Create Cursor curXML (...fields as you need them....)
XMLToCursor(Substr(FileToStr("testxml26.xml"),4),"curXML",8192+4)

Make sure, that the field names of the curXML cursor do exactly match to the ones VFP creates on it's own.

And that does indeed only work in VFP9, as the needed flag 8192 wasn't supported in VFP7. But indeed the XMLToCursor of my earlier posting should also have worked in VFP7.

Bye, Olaf.

 

[PatMcLaughlin]

Your nFlag did the trick!!! VFP9 is still testing but up to now exceeds expectations! Am I understanding you correctly in that if I get the data sent to me without schema, as long as I have created the cursor to match the data I will have success?
Olaf and Mike are VFP Gods who I would offer my first born (except that he is now much bigger than I am and would probably take offense).

 

[Mike Lewis]

Olaf and Mike are VFP Gods who I would offer my first born

No need for anything so extreme. Just buy us each a beer next time we meet, and we'll call it quits.

Seriously, though, Pat, I'm delighted your perseverence paid off. Be sure to come back with any further VFP problems.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy