How FoxPro Implements XML

FoxPro's implementation of XML is a wrapper for MSXML, Microsoft's XML DLLs. There are several versions, but when you install FoxPro 7 or 8, the latest version is automatically installed.

There have been six releases of the XML parser as of this writing:

 MSXML2.DOMDocument.1.0 MSXML2.DOMDocument.2.0 MSXML2.DOMDocument.2.5 MSXML2.DOMDocument.2.6 MSXML2.DOMDocument.3.0 MSXML2.DOMDocument.4.0 

FoxPro 7 installed 3.0, whereas Visual FoxPro 7 SP1 and Visual FoxPro 8 installed 4.0. That's what determines the version used with the CursorToXML() and XMLToCursor() functions. However, each version is a separate DLL, so if you need to instantiate an earlier version, you can do so using CREATEOBJECT() and fully qualify the ProgID, for example:

 loXML = CREATEOBJECT ( "MSXML2.DOMDocument.3.0" ) 

That's not a misprint; the MSXML2 prefix is part of the parser ProgID for all of these XML versions. Each version has new features; for example, the addition of the flag parameter value 8192 in the XMLToCursor() function to preserve the structure of an existing cursor was added in version 4.0.

Using XML to Read Other Types of Data

XML can be useful to read other types of data. For example, you can read other types of data using ADODB ”Paradox, MDB files, and many others. However, the result is returned as a RecordSet , which FoxPro doesn't know how to read. But RecordSet s know how to write their records to disk in XML format, and you can use FoxPro to read the resulting XML file and convert it into a cursor. Try variations on this theme; I'm not going to tell you all the hoops I jumped through to read foreign data before this came along (see Listing 7.15).

Listing 7.15. Reading Foreign Data Sources with the Help of XMLTOCURSOR()

 LOCAL Con AS "ADODB.Connection" LOCAL RS  AS "ADODOB.Recordset" Con = CREATEOBJECT ( "ADODB.Connection") Con.Open ( "Driver={SQL Server};server=(local);UID=sa;PWD=;database=pubs;") RS = Con.Execute ( "SELECT * FROM AUTHORS WHERE STATE='CA'") RS.Save ( "RS.XML", 1) * MODI FILE  RS.XML    && remove asterisk to see the XML XMLTOCURSOR ( "RS.XML", "RSPUBS", 512 ) BROWSE TITLE [Authors table from SQL Server using ADO] 

Receiving XML Files Directly

Sometimes we receive data directly from branches, main offices, vendors , or clients . If you both have FoxPro, you can send DBFs. But what if they don't have FoxPro? For a while, they sent us comma-delimited or fixed-length records with a written description of the fields, so that we could manually re-create them, and then clean up the data and import it into our production files. It was a lot of work.

This is where XML comes in. After Office '97, just about every Microsoft product (and just about everyone else's product) is capable of creating XML output. If they send us an XML file, we can convert it to XML, and then write a little program to move their field X to our field Y and so on. Or we can simply open the table we created from their XML using XMLToCursor() , change the field names one by one until they match ours, and append their DBF into our own. If we can convince them to create the XML document with the correct field names in the first place, data import is trivially easy, as shown in Listing 7.16.

Listing 7.16. Importing an XML File into Our Table

 tempXML = GETFILE("XML") IF NOT EMPTY (tempXML )    XMLTOCURSOR (tempXML, "temp" )    SELECT 0    USE Invoices    APPEND FROM DBF("temp")    USE    USE IN temp ENDIF 

The APPEND FROM DBF("Temp") is necessary because APPEND FROM needs a DBF name , whereas XMLToCursor() creates a cursor, which is usually a temp file with a .tmp extension.