[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export VFP table to XML

Posted on 2011-10-10
16
Medium Priority
?
3,105 Views
Last Modified: 2012-05-12
Hi:
I am updating a VFP-7 app. where keyed data needs to be exported to an XML document. I am completely new to XML and totally ignorant.
My client has provided a sample XML and "SCHEMA" files (there are 81 of them - one for each data-set (field?).
I created a DBF that contains fields named the same as in the sample XML. There are about 250 fields. I tried using CURSORTOXML but my resulting XML does not much look like the sample XML.  This is the statement:
  CURSORTOXML("DbfTable", (M_XMLFil), 1, 512)
As stated, this app. was done with VFP-7 using a framework and would not be too easy to upgrade,  however, if VFP-9 has superior tools for creating the XML, I could create a mini app. using VFP-9 just to create the XML.
Is there a "crash course" out there as I just don't get it - its like trying to learn Greek or something! Please help get me on the right track.
Thanks.
CAM
0
Comment
Question by:ramramcat
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 27

Accepted Solution

by:
CaptainCyril earned 500 total points
ID: 36946613
I usually dish out the whole XML using low level functions. FCREATE FWRITE FCLOSE.

You can also put placeholders for your variables and replace them using STRTRAN.

You can also read an XML file using FILETOSTR, manipulate it and write it out using STRTOFILE.

I played with CURSORTOXML before but never really used it in any application. I can't help you further in that.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 36947250
81 schema files? You'd need 1 xsd schema for one xml file, not 81. Or are you talking about 81 schemas for 81 different datasets, and a dataset is not equivalent to a field, but to a set of data, eg even several tables. Eg those 81 schemas could be for 81 tables of a database or for 81 different compositions of data.

Also, in general an xsd schema is denoting the schema of an xml file, not necessarily of a (single) table or a set of tables.

You can't simply rely on cursortoxml, as that is of course rather rigid and has no parameter to use a schema for the xml generation, you just can export/create a (embedded) schema in the xml output via xmltocursor.

With VFP8 XMLAdapter was introduced and you can do more with that, eg add several tables and created nested xml with it, but in the end it might be best to output the xml via low level functions as Cyril suggests, as you have full control of what is output, then of course.

You can then use the xsd schema to verify your output validates against that schema, in the end and get feedback what is wrong with your xml generation, if the xml doesn't validate.

Bye, Olaf.
0
 
LVL 12

Assisted Solution

by:jrbbldr
jrbbldr earned 1500 total points
ID: 36949806
Some of my older VFP applications (pre VFP8) needed to write XML and it was reasonably easy to do something like the following:

SELECT MyTable
DIMENSION aryFlds(1)
nFields = AFIELDS(aryFlds)

cXMLOverAll = ""
* --- Scan through each record ---
SELECT MyTable
SCAN
   * --- For each record, 'scan' through each field ---
   FOR FldCntr = 1 TO nFields
      cFldName = aryFlds(FldCntr)  && Get Field Name
      cFldVal = EVAL(cFldName)  && Get Field Value

      * --- Now convert value to string to go into XML text document
      DO CASE
            CASE TYPE("cFldVal') = 'C'
                * --- Character Type, No Conversion Necessary ---

            CASE TYPE("cFldVal') $ 'I,N"
                * --- Numeric Type ---
                cFldVal = STR(cFldValm2)

            CASE TYPE("cFldVal') = 'L'
               * --- Logical Type ---
               cFldVal = IIF(cFldVal,'TRUE','FALSE')

            CASE TYPE("cFldVal') = 'D'
                * --- Date Type ---
                cFldVal = DTOC(cFldVal)

            CASE TYPE("cFldVal') = 'T'
                * --- DateTime Type ---
                cFldVal = TTOD(cFldVal)
      ENDCASE
     
      * --- Now Write Out To XML File ---
      cXMLLine = "<" + cFldName + ">" + cFldVal +  "</" + cFldName + ">"
      cXMLOverAll =  cXMLOverAll + cXMLLine
   ENDFOR
ENDSCAN
USE

< do whatever - maybe a STRTOFILE()>

NOTE - I have not tested the code above and it is merely a 'guideline' of one way to approach creating the XML string - not necessarily working code.

Good Luck
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:jrbbldr
ID: 36950226
One thing I just noticed about the code that I posted above is that I missed putting in any record designating tag after the ENDFOR (I told you the code was not tested!!!)

Good Luck
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 36951128
It should be TTOC for 'T'

I would also do ALLTRIM(STR(cFldVal,10,2)) for 'N'
0
 

Author Comment

by:ramramcat
ID: 36951535
Thanks everyone - it looks like low-level functions are the way to go for this. In JRBBLDR's example above, it looks like the XML for each record (or is it the entire table?) would be written to one memvar with no line-feeds and then written out one time. I believe this is also true for FWRITE. Is this true - that the XML is one long string and only appears to have line-feeds, etc. when viewed/printed?
Thanks again.
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 36951648
You can add CHR(13)+CHR(10) to every line to make it look good.

You could also add CHR(9) to tab it out.
0
 
LVL 12

Expert Comment

by:jrbbldr
ID: 36953134
In JRBBLDR's example above, it looks like the XML for each record (or is it the entire table?) would be written to one memvar with no line-feeds and then written out one time.

As I indicated that code is a generalization of an approach, not necessarily 'finished' code.
You can add record separator tag, tabs, CrLF's and anything else, before you write out the string.

Depending on the number of records and field you might be able to get multiple records into the one memory variable before writing the value(s) out.  Or you could write out each individual record as it was completed.

Good Luck

0
 

Author Comment

by:ramramcat
ID: 36954266
OK, I started to create a file with a header and already get this error upon opening the file:

Invalid at the top level of the document. Error processing resource 'file:///J:/FEDPCR/Testfile.xml'. Line 1, Position 39
<?xml version="1.0" encoding="UTF-8"?>

This is the code:

M_Id = FCREATE('Testfile.xml')      &&Assigns FileHandle number to M_Id
cHeader1 = '<?xml version="1.0" encoding="UTF-8"?>' + CHR(13)+ ;
'  - <Header>'  +   CHR(13)+;
'    <D01_01>37-12345</D01_01>' +   CHR(13)+;
'    <D01_03>37</D01_03>' +   CHR(13)+;
'    <D01_04>37183</D01_04>' +   CHR(13)+;
'  - <Record>'
FWRITE((M_Id), cHeader1)
FCLOSE((M_Id))

Pos. 39 is a space (or carriage return) but without it everything is run - on.  What am I doing wrong? This is just the beginning header - have many data items to go......
Thanks.

0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 36954504
First of all I would use this:

#DEFINE TAB CHR(9)
#DEFINE CRLF CHR(13)+CHR(10)

cXMLline = [<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>] + CRLF

I think the problem is that you are using -<. You should do <. The browser adds + or - to the tags.

SPACE is CHR(32).
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 36954508
I also use XML parsers, readers and writers using XMLDOC.

Here is how I start it.

TRY
      oXMLDoc = CREATEOBJECT("MSXML2.DomDocument")
CATCH
      lOK = .F.
      = MESSAGEBOX("Microsoft XML Parser is not installed on this machine. Please check with the administrator to install it from Microsoft Office CD-Pack.", 48, appname)
ENDTRY
IF NOT lOK
      RETURN
ENDIF
oThermometer = CREATEOBJECT('thermometer','Reading Map')
oXMLDoc.ValidateOnParse = .T.
oXMLDoc.Load(cFileMap)
DO WHILE oXMLDoc.readyState <> 4
      DOEVENTS
ENDDO
IF NOT oXMLDoc.Parsed
      = MESSAGEBOX("The XML is corrupted AND NOT parsed successfully!", 48, appname)
      RELEASE oXMLDoc
      lOK = .F.
      RETURN
ENDIF
oNodes = oXMLDoc.SelectNodes("//.")
0
 
LVL 12

Expert Comment

by:jrbbldr
ID: 36955597
The way that I test things is to write into the memory variable and then open some 'scratch' table with a Memo field.   Then I do a REPLACE MemoFld WITH cXMLString.- the memo field contents can then be examined.

After that I can view the XML string as it would be written to a text file.

Depending on how many records you are writing and how many fields are in each record, you MIGHT be able to get the entire XML string into a single memory variable before the Write.

Good Luck


0
 
LVL 12

Assisted Solution

by:jrbbldr
jrbbldr earned 1500 total points
ID: 36955643
Also in your code above I do not see the appropriate XML tags.
For each 'open' tag you need a matching 'close' tag

For your  <Header>   where is the   </Header>  tag?
And for your <Record>  where is the   </Record> tag?

You might want to look on the web for a free XML code editor product into which you could paste the code you generate and it will inform you of the XML errors.

Good Luck
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 36955709
I think Notepad++ does that.
0
 
LVL 12

Assisted Solution

by:jrbbldr
jrbbldr earned 1500 total points
ID: 36956078
The XML Editor tool that I am currently using for checks like this is XML Marker.

Their current version is not free, but the previous version (the one I use) is still free.
     Go here: http://symbolclick.com/download.htm

Good Luck

0
 

Author Closing Comment

by:ramramcat
ID: 36961100
Thank you ALL for your expertise - great solutions and learning experience!!
I have written out the entire XML using low-level functions and my client's "rules" and data using VFP's commands, logic and control of data. Works like a charm!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month19 days, 6 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question