Help Getting Started With XML

[JRB-Bldr]

I have a client who is working with a new customer. That new customer is requiring the sending and receiving of data utilizing the XML format.

Unfortunately, due to the customer's high level of bureaucracy, they won't send us Sample data files, only a written paper specification.

I am planning (hoping??) on using VFP7's XMLtoCURSOR and CURSORtoXML to get data into and out of the XML format - unless you suggest a better alternative approach.

Anyway, in the absence of the customer's sample file, I am wondering about a couple of things.

1. Does anyone out there have one or more sample XML data files that they can share with me so that I can use them to begin development/testing?

2. What are some of the "gotchas" that I need to be aware of? Especially differences between XML file types/structures/etc.

Your suggestions and advice are greatly appreciated.

Thanks,
JRB-Bldr

 

[DSummZZZ]

jrbbldr,

How about making a table with their specs, and outputting it with the CURSORTOXML() for your test data?
Beyond that, I haven't worked with XML data to offer up any 'gotchas'.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[JRB-Bldr]

Dave - Thanks for the feedback.

The only client customer information that we received was 2 XSD files describing the Send & Receive structure.

Not knowing what else to try, I used a freeware XML Editor XMLFox (

http://www.xmlfox.com/)

to open the XSD file. I then saved the opened file back out in XML format.
Saved File - C:\Temp\Test.XML

BUT when I attempted to use any of the following:
XMLTOCURSOR("C:\Temp\Test.XML","C:\Temp\XML.dbf",4)
XMLTOCURSOR("C:\Temp\Test.XML","C:\Temp\XML.dbf",512)
XMLTOCURSOR("C:\Temp\Test.XML","C:\Temp\XML.dbf",1024)

Regardless of which 3rd parameter I used I continued to get a Parsing error message.

For 3rd parameters of 4 & 1024 I got:
XML Parse Error: Invalid at top level of document

The most promising 3rd parameter was the 512:
XML Parse Error: Switch from current encoding to specified encoding not supported.

Following the "hint" from the last error message, I went into the XML file itself and saw that its first line was:
<?xml version="1.0" encoding="utf-16"?>

I realize that you "haven't worked with XML data to offer up any 'gotchas'", but perhaps you or someone else can offer some advice on how to move forward from here.

Thanks,
JRB-Bldr

 

[TheRambler]

Hi,

I have not yet worked with XML data either, but I remember reading once about XMLTOCURSOR failing with XML data not built with VFP own CURSORTOXML function.

Check this free download xmlparser.zip ID: 127

Normalizes (deconstructs) data from an xml file into cursors that contain pointers to 'child' records. Unlike the native XMLTOCURSOR function, doesn't fail with xml files not constructed with VFP's CURSORTOXML function. Handles XML files the native XMLAdapter class fails to handle. Retrieves loosely related data into a temporary cursor based on relationships implied by the structure of the xml file.
...
The reason VFP's CURSORTOXML method and XMLAdapter class fail with non-VFP data is simple: both expect flat files with no recursive or missing elements. This comprises possibly < 1% of data contained in xml files imported from other data sources, such as most web services.

The xmlparser.prg can handle most xml input (i.e., produce a useable dataset in a single cursor) for most xml files. The biggest exception are xml files that are both recursive (contain like multiple child elements under several data categories) and/or contain significant amounts of missing data. This kind of file is typical of the output from complex proprietary DB's here the vendor wishes to force use of proprietary software in order to use the output. However, even in this case, data from the input file is normalized and placed into identifiable cursors that can be manipulated by one-off VFP program code.

There are some XML samples too. Good luck.

 

[dbMark]

XMLtoCursor and CursortoXML are generally only suitable for simple, single level XML. When multi-level XML is read, rather than it creating one cursor with one record and many fields (limited to 254 fields of course), you get multiple records. Every time the XML changes levels, a new record is created, giving a stair-step effect. Very messy to decode programatically. Even looking at it visually is awkward and cumbersome.

A better solution is to read XML files with the XmlAdapter object which comes with VFP version 9 and perhaps 8 too. I use hand-written code to create the XML string. Here is a simple example that reads an XML:

PRIVATE oXml, iXml
oXml = CREATEOBJECT("XmlAdapter")
oXml.loadXml(IncomingXML)
iXml = oXml.IXMLDOMElement
IF oXml.isLoaded
   cDate = VAL(iXml.getElementsByTagName("EventDate").item(0).text)
   cInfo = VAL(iXml.getElementsByTagName("EventDetail").item(0).text)
ENDIF
RELEASE oXml, iXml

Although it looks like XML is the "in" way to communicate these days, and is here to stay, viewing an XML is quite slow, as many have noted. With each attribute being repeated over and over(such as "<ThisIsTheFirstAttribute>I love Visual FoxPro</ThisIsTheFirstAttribute>") for each element, resulting XML files are huge. I have one program that takes a 4MB table and converts it to a 18MB XML file. It takes Internet Explorer about a minute to open the XML on a fast computer.

 

[Neil Toulouse]

Has anyone got the xmlparser.zip files working? I get loads of program errors even when using their own sample files.

For example:

=ALINES(axml,cval,1+4,CHR(13)))

...gives a Function, argument, type or count is invalid. I am guessing it's the 1+4 parameter. Lots of these in the code!

I like work. It fascinates me. I can sit and look at it for hours...

 

[dbMark]

ALINES(ArrayName, cExpression [, nFlags] [, cParseChar [, cParseChar2 [, ...]]])

Description: Copies each line in a character expression or memo field to a corresponding row in an array.

The following table describes the values for the third parameter nFlags.

Bit Value(additive) Description
0 1 (Default) Removes leading and trailing spaces from lines,
....or for Varbinary and Blob values, removes trailing zeroes (0) instead of spaces.
1 2 Include the last element in the array even if the element is empty.
2 4 Do not include empty elements in the array.
3 8 Specifies case-insensitive parsing.
4 16 Include the parsing characters in the array.

Probably an issue with the variables aXml or cVal?

 

[TheRambler]

> Has anyone got the xmlparser.zip files working?

It's VFP 9 code I guess.

This is the syntax for VFP 7:
ALINES(ArrayName, cExpression [, lTrim] [cParseChar, , ,cParseChar])

lTrim
Specifies that leading and trailing blanks are removed from the lines copied to the array. If lTrim is true (.T.), leading and trailing blanks are removed from the lines. If lTrim is false (.F.) or is omitted, leading and trailing blanks are not removed.

There is a similar problem with the fifth parameter of the STREXTRACT() function.

nFlag
Specify the type of controls placed on the search. The number you specify in nFlag provides a bit-value that determines options according to the following table:

1 Case-insensitive search.
2 End delimiter not required.
4 Include the delimiters in the returned expression.


VFP 7 only had the first two values. So it has to be modified a little for VFP 7.

 

[TheRambler]

You can download a VFP 7/8 version here.

 

[Neil Toulouse]

Hi dude!

Thanks for the link! It now works on the sample files.

Unfortunately, it doesn't work with the XML file we are trying to import!! (and I though XML was here to make peolple's lives easier...).

Still, one step closer!

Neil



I like work. It fascinates me. I can sit and look at it for hours...

 

[JRB-Bldr]

After a long delay I am now back to looking at getting XML files converted into cursors/tables via a VFP7 application.

The XML file that I am attempting to use is a customer supplied file which was not built with VFP. They did provide me with both the XML and an XSD file (I hope the XSD is current!!!).

However all of my attempts with both the XMLParser.zip files (newest version) and with West Wind's wwXML class have been un-successful.

Since I do not have a Target table structure (being un-certain of the XML file 'fields') I would hope to be able to use a 'Hierarchial XML' approach, which, if I understand it correctly, will build its own target table structure from the XSD file and then populate the table from the XML contents. (Feel free to correct me if I am wrong!)

The

http://wwwXML

class approach looks the most hopeful. Unfortunately the West Wind Demo_Cursor program that is included only converts and launches a browse of its own sample data table . And changing the source XML file reference in the sample program still results in a display of the original sample data table - leading me to believe that my own XML file never got converted at all.

Going beyond the sample program and attempting to follow code execution through line-by-line in TRACE mode in order to find out why the code is failing when using my own sample XML file is not uncovering the problem.

Obviously I am missing something.
Hopefully it is something simple that I am over-looking.

Any further help/suggestions out there would be GREATLY appreciated.

Thanks,
JRB-Bldr

 

[rlawrence]

I'm not much more than a novice with XML myself. However, I have succeeded in implementing a fairly extensive capability using VFP's XMLTOCURSOR() and CURSORTOXML() functions.

These functions REALLY only convert a flat table to XML and back. Anything more complex requires your own processing.

I've had great luck, however incorporating multiple/hierarchical tables into a single XML document and back again. It takes developing a routine that can process through your tables, convert each one, and append each XML string (not the whole document) to your final XML string or document. You'll want to add the XML header and trailer yourself.

To convert from XML to your tables is a reversal of the process. You have to read the document (using XMLDOM), navigate to the nodes that represent your tables, wrap the XML string with it's own header and trailer, then submit the XML to XMLTOCURSOR() to get your data in a VFP cursor.

It sounds a bit involved, and I guess it is. The secret is to take one step at a time. Get things to work for a simple table, then use those modules to work on something more complex.

For what it's worth...

Ron

 

[wgcs]

That new customer is requiring the sending and receiving of data utilizing the XML format.

For reading info out of the data file your customer sends you I'd highly recommend the Microsoft XML DOM ActiveX control, which (since MS made it and it's used by many programs) does a good job of parsing XML.

Here's some sample code:

LOCAL loXML as msxml2.DOMDocument, ;
      loXSLT as msxml2.DOMDocument

* Load the customer's xml data:
loXML = CREATEOBJECT('msxml2.DOMDocument')
lcXML = filetostr('data.xml')
llRes = loXML.LoadXML( lcXML )

* Access individual data items like this:
loNode = loXML.documentElement.selectSingleNode("/response/stuff_report/stuff") 
wait window loNode.text

*Convert the XML into a more useful format (like XMLTOCURSOR's)
* Load an XSLT transformation:
loXSLT  = CREATEOBJECT('msxml2.DOMDocument')
lcXSLT  = filetostr('convert.xslt')
llRes   = loXML.LoadXML( lcXSLT )

* Apply the XSLT to the XML:
lcFinalXML = [<?xml version="1.0"?>] + loXML.transformNode( loXSLT )    

* Finally, load the relatively flat XML into a cursor:
XMLTOCURSOR(lcFinalXml,'cMyItems')


* The XSLT could look something like this:
TEXT TO loXSLT
        <?xml version="1.0"?>
        <xsl:stylesheet xmlns:xsl="[URL unfurl="true"]http://www.w3.org/TR/WD-xsl">[/URL]
        <xsl:template match="/">
          <xsl:element name="VFPDATA">
            <xsl:for-each select="/response/stuff_report/stuff">
              <items>
                <item_name><xsl:value-of select="@stuff_name"/></item_name>
                <item_id><xsl:value-of select="@stuff_id"/></item_id>
              </items>
            </xsl:for-each>
          </xsl:element>
        </xsl:template>
        </xsl:stylesheet>
ENDTEXT

Maybe this code'll give you a head start.

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[wgcs]

If you do use MSXML2, be sure to include the wdstddll_wim32.msm, msxml3_wim32.msm and msxml3inf_wim32.msm merge modules in your installshield release.

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[JRB-Bldr]

Bill - Thanks for the alternative approach.

This project keeps going active and then going back on hold, so I have not made much headway with it.

And, as said above, I have not been able to get the other suggestions to work yet for this VFP7 application, so an alternative approach is most welcome.

I did have a question about the code above
Apply the XSLT to the XML:
It appears as though the code is using the XSLT information as a "schema" to convert/transform/parse the XML file.

Does this approach also work for XSD to XML?
This customer is not sending me a XSLT file as the "schema" file, but instead an XSD file.

Thanks,
JRB-Bldr