Re: Please have XML mercy on me . . . .
Sorry if my first reply was too stream-of-consciousness... I meant - use the same way you would take a text file and extract data into a table...
You would use FILETOSTR() to bring the XML data into a text string, and then loop through it using OCCURS(), STREXTRACT(), etc. and insert the results into a table or cursor.
You will have to do a loop inside a loop to get the results for Attendees (assuming the other sections are 1-1). Actually, looking again it appears the XML data is a little sloppy - limiting the number of attendees to 10. That being the case, you can plop it all into a single result table if you wish.
You'll need to decide how your attendees table will be related - one to many or many to many. The rest of the data looks like there's one entity per record can be in s single table if you like, or multiple tables if you want to follow the structure of the XML - your call.
IF YOU'RE USING ONE BIG FLAT FILE, HERE'S ANOTHER OPTION:
After a more thorough examination of the structure of the XML you are given (which, I must say, is pretty sloppy work for whomever produced it) I'm thinking you can pull it all into a vfp table by simply removing the children tags and pulling it all into a flat table using XMLTOCURSOR().
To do that, do a mystring=FILETOSTR("filename.xml") of the XML file, then do a series of mystr = strtran(mystr,"<General>","") for <General>, </General>, <Personal>, </Personal>, etc.
Then finally do an XMLTOCURSOR(mystr,myCursorName,nFlags) - you may have to play with the flags and perhaps further tweak mystr to get it cleaned up the way XMLTOCURSOR needs it.
If you need more specifics than that, you should probably hire someone to do it for you.
HTH
Hmmm. Not sure I'm following you 100%.
Have you tried the VFP function XMLTOCURSOR()?
No - I see now that you're dealing with 4 related tables.
My instinct would be to use VFP string concatenation and just treat it as a text file.
Michael
I have stayed away from XML, it has never had much use for us. Now we inherited a project that requires converting an XML file into VFP cursor/tables. The XML layout is always the same. I see I will have to manually parse it, and I was wondering if it is straight forward enough that some kind soul will show me the code to do it. We are swamped and unlikely to ever use XML again.
Thanks, thanks, thanks!
Here is a snippet:
<?xml version="1.0"?>
<MJEFormData>
<Registration>
<General>
<tablename>jos_chronoforms_Donate</tablename>
<recordtime>2009-05-21 - 12:57:41</recordtime>
<notes></notes>
</General>
<Personal>
<name></name>
<username></username>
<Address>
<address1></address1>
<address2></address2>
<city></city>
<state></state>
<zip></zip>
</Address>
<phone1></phone1>
<phone2></phone2>
<email></email>
</Personal>
<Financial>
<ccname></ccname>
<CI_Address>
<CI_BILLADDR1></CI_BILLADDR1>
<CI_BILLADDR2></CI_BILLADDR2>
<CI_BILLCITY></CI_BILLCITY>
<CI_BILLSTATE></CI_BILLSTATE>
<CI_BILLZIP></CI_BILLZIP>
</CI_Address>
<CI_PHONE></CI_PHONE>
<CI_EMAIL></CI_EMAIL>
<AMOUNT></AMOUNT>
</Financial>
<Attendees>
<attendee_1_first_name></attendee_1_first_name>
<attendee_1_last_name></attendee_1_last_name>
<attendee_1_email_address></attendee_1_email_address>
<attendee_1_cell_phone></attendee_1_cell_phone>
<attendee_1_gender></attendee_1_gender>
<attendee_1_birth_date_month></attendee_1_birth_date_month>
<attendee_1_birth_date_day></attendee_1_birth_date_day>
<attendee_1_birth_date_year></attendee_1_birth_date_year>
<attendee_1_age></attendee_1_age>
<attendee_1_affiliation></attendee_1_affiliation>
<attendee_1_firsttime></attendee_1_firsttime>
<attendee_1_hearabout></attendee_1_hearabout>
<attendee_1_otherevents></attendee_1_otherevents>
<attendee_1_normallyattend></attendee_1_normallyattend>
<attendee_2_first_name></attendee_2_first_name>
<attendee_2_last_name></attendee_2_last_name>
<attendee_2_email_address></attendee_2_email_address>
<attendee_2_cell_phone></attendee_2_cell_phone>
<attendee_2_gender></attendee_2_gender>
<attendee_2_birth_date_month></attendee_2_birth_date_month>
<attendee_2_birth_date_day></attendee_2_birth_date_day>
<attendee_2_birth_date_year></attendee_2_birth_date_year>
<attendee_2_age></attendee_2_age>
<attendee_2_affiliation></attendee_2_affiliation>
<attendee_2_firsttime></attendee_2_firsttime>
<attendee_2_hearabout></attendee_2_hearabout>
<attendee_2_otherevents></attendee_2_otherevents>
<attendee_2_normallyattend></attendee_2_normallyattend>
<attendee_3_first_name></attendee_3_first_name>
<attendee_3_last_name></attendee_3_last_name>
<attendee_3_email_address></attendee_3_email_address>
<attendee_3_cell_phone></attendee_3_cell_phone>
<attendee_3_gender></attendee_3_gender>
<attendee_3_birth_date_month></attendee_3_birth_date_month>
<attendee_3_birth_date_day></attendee_3_birth_date_day>
<attendee_3_birth_date_year></attendee_3_birth_date_year>
<attendee_3_age></attendee_3_age>
<attendee_3_affiliation></attendee_3_affiliation>
<attendee_3_firsttime></attendee_3_firsttime>
<attendee_3_hearabout></attendee_3_hearabout>
<attendee_3_otherevents></attendee_3_otherevents>
<attendee_3_normallyattend></attendee_3_normallyattend>
<attendee_4_first_name></attendee_4_first_name>
<attendee_4_last_name></attendee_4_last_name>
<attendee_4_email_address></attendee_4_email_address>
<attendee_4_cell_phone></attendee_4_cell_phone>
<attendee_4_gender></attendee_4_gender>
<attendee_4_birth_date_month></attendee_4_birth_date_month>
<attendee_4_birth_date_day></attendee_4_birth_date_day>
<attendee_4_birth_date_year></attendee_4_birth_date_year>
<attendee_4_age></attendee_4_age>
<attendee_4_affiliation></attendee_4_affiliation>
<attendee_4_firsttime></attendee_4_firsttime>
<attendee_4_hearabout></attendee_4_hearabout>
<attendee_4_otherevents></attendee_4_otherevents>
<attendee_4_normallyattend></attendee_4_normallyattend>
<attendee_5_first_name></attendee_5_first_name>
<attendee_5_last_name></attendee_5_last_name>
<attendee_5_email_address></attendee_5_email_address>
<attendee_5_cell_phone></attendee_5_cell_phone>
<attendee_5_gender></attendee_5_gender>
<attendee_5_birth_date_month></attendee_5_birth_date_month>
<attendee_5_birth_date_day></attendee_5_birth_date_day>
<attendee_5_birth_date_year></attendee_5_birth_date_year>
<attendee_5_age></attendee_5_age>
<attendee_5_affiliation></attendee_5_affiliation>
<attendee_5_firsttime></attendee_5_firsttime>
<attendee_5_hearabout></attendee_5_hearabout>
<attendee_5_otherevents></attendee_5_otherevents>
<attendee_5_normallyattend></attendee_5_normallyattend>
<attendee_6_first_name></attendee_6_first_name>
<attendee_6_last_name></attendee_6_last_name>
<attendee_6_email_address></attendee_6_email_address>
<attendee_6_cell_phone></attendee_6_cell_phone>
<attendee_6_gender></attendee_6_gender>
<attendee_6_birth_date_month></attendee_6_birth_date_month>
<attendee_6_birth_date_day></attendee_6_birth_date_day>
<attendee_6_birth_date_year></attendee_6_birth_date_year>
<attendee_6_age></attendee_6_age>
<attendee_6_affiliation></attendee_6_affiliation>
<attendee_6_firsttime></attendee_6_firsttime>
<attendee_6_hearabout></attendee_6_hearabout>
<attendee_6_otherevents></attendee_6_otherevents>
<attendee_6_normallyattend></attendee_6_normallyattend>
<attendee_7_first_name></attendee_7_first_name>
<attendee_7_last_name></attendee_7_last_name>
<attendee_7_email_address></attendee_7_email_address>
<attendee_7_cell_phone></attendee_7_cell_phone>
<attendee_7_gender></attendee_7_gender>
<attendee_7_birth_date_month></attendee_7_birth_date_month>
<attendee_7_birth_date_day></attendee_7_birth_date_day>
<attendee_7_birth_date_year></attendee_7_birth_date_year>
<attendee_7_age></attendee_7_age>
<attendee_7_affiliation></attendee_7_affiliation>
<attendee_7_firsttime></attendee_7_firsttime>
<attendee_7_hearabout></attendee_7_hearabout>
<attendee_7_otherevents></attendee_7_otherevents>
<attendee_7_normallyattend></attendee_7_normallyattend>
<attendee_8_first_name></attendee_8_first_name>
<attendee_8_last_name></attendee_8_last_name>
<attendee_8_email_address></attendee_8_email_address>
<attendee_8_cell_phone></attendee_8_cell_phone>
<attendee_8_gender></attendee_8_gender>
<attendee_8_birth_date_month></attendee_8_birth_date_month>
<attendee_8_birth_date_day></attendee_8_birth_date_day>
<attendee_8_birth_date_year></attendee_8_birth_date_year>
<attendee_8_age></attendee_8_age>
<attendee_8_affiliation></attendee_8_affiliation>
<attendee_8_firsttime></attendee_8_firsttime>
<attendee_8_hearabout></attendee_8_hearabout>
<attendee_8_otherevents></attendee_8_otherevents>
<attendee_8_normallyattend></attendee_8_normallyattend>
<attendee_9_first_name></attendee_9_first_name>
<attendee_9_last_name></attendee_9_last_name>
<attendee_9_email_address></attendee_9_email_address>
<attendee_9_cell_phone></attendee_9_cell_phone>
<attendee_9_gender></attendee_9_gender>
<attendee_9_birth_date_month></attendee_9_birth_date_month>
<attendee_9_birth_date_day></attendee_9_birth_date_day>
<attendee_9_birth_date_year></attendee_9_birth_date_year>
<attendee_9_age></attendee_9_age>
<attendee_9_affiliation></attendee_9_affiliation>
<attendee_9_firsttime></attendee_9_firsttime>
<attendee_9_hearabout></attendee_9_hearabout>
<attendee_9_otherevents></attendee_9_otherevents>
<attendee_9_normallyattend></attendee_9_normallyattend>
<attendee_10_first_name></attendee_10_first_name>
<attendee_10_last_name></attendee_10_last_name>
<attendee_10_email_address></attendee_10_email_address>
<attendee_10_cell_phone></attendee_10_cell_phone>
<attendee_10_gender></attendee_10_gender>
<attendee_10_birth_date_month></attendee_10_birth_date_month>
<attendee_10_birth_date_day></attendee_10_birth_date_day>
<attendee_10_birth_date_year></attendee_10_birth_date_year>
<attendee_10_age></attendee_10_age>
<attendee_10_affiliation></attendee_10_affiliation>
<attendee_10_firsttime></attendee_10_firsttime>
<attendee_10_hearabout></attendee_10_hearabout>
<attendee_10_otherevents></attendee_10_otherevents>
<attendee_10_normallyattend></attendee_10_normallyattend>
</Attendees>
</Registration>
<Registration>
. . . . . . . .
</MJEFormData>
Thanks!
