Converting Between XML and Visual FoxPro Data

Article
07/09/2007

You can exchange data more easily with other applications by converting Visual FoxPro data to XML format. Visual FoxPro includes the following functions so you can convert data between XML and Visual FoxPro format:

Note

You must have the Microsoft XML Core Services (MSXML) parser installed before you can use the Visual FoxPro XML functions.

The following sections discuss considerations when converting between XML and Visual FoxPro data:

Processing XML Data in Variables or Strings Instead of Files
XML String Conversion
Importing and Exporting XML Using Schemas

You can override the internal functionality of the CURSORTOXML( ), XMLTOCURSOR( ), and XMLUPDATEGRAM( ) functions by referencing a COM component using the VFPXMLProgID property. For more information, see VFPXMLProgID Property.

Visual FoxPro supports hierarchically formatted XML for XML DiffGram and .NET Framework ADO.NET DataSet formats by providing the XMLAdapter, XMLTable, and XMLField classes. For more information, see XML Functionality Using XMLAdapters.

Processing XML Data in Variables or Strings Instead of Files

XMLTOCURSOR( ) processes data differently from a variable or string than from a file. When converting XML to a cursor, the method used to pass XML to the function affects how double-byte character sets (DBCS) characters in the XML are handled. DBCS characters are interpreted correctly when using a file for XMLSource. However, if you need to use a memory variable or string, you can force interpretation to DBCS by using the following code in XMLTOCURSOR( ):

STRCONV(string,11)

The following example opens the Visual FoxPro sample Customer table located in the Visual FoxPro ..\Samples\Data\ directory and converts the data in the Customer table and saves the data in the table to a memory variable, lcXML, as XML:

CLEAR
CLOSE DATABASES ALL
USE HOME()+'samples\data\customer'
CURSORTOXML('customer','lcXML',1,48,5,"","","")

The following line inserts incorrect characters in the resulting cursor:

XMLTOCURSOR(lcXML,"curCustomerList",4)

The following line uses the STRCONV( ) function and converts the XML to DBCS data correctly, opens a browse window for the table:

XMLTOCURSOR(STRCONV(lcXML,11),"curCustomerList",4) 
BROWSE

XML String Conversion

XMLTOCURSOR( ) does not perform automatic conversions of XML strings, for example, from ANSI to UTF-8. This might occur when reading an XML string from a file. To perform a conversion, use the STRCONV( ) function.

The following example demonstrates the parse error that occurs when Visual FoxPro encounters the first ASCII character:

CLOSE DATABASE ALL
USE HOME(2)+"\data\customer"
CURSORTOXML("customer","lcXML",1,32)
STRTOFILE(lcXML,"customer.xml")
XMLTOCURSOR("customer.xml","curCustomer",512)
RETURN

You can prevent the parse error generated by converting the string to UTF-8 when the XML is written or when it is written to the file. The following example converts the string to UTF-8 as it is being written to the file by using the STRCONV( ) function with the STRTOFILE( ) function:

CLOSE DATABASE ALL
USE HOME(2)+"\data\customer"
CURSORTOXML("customer","lcXML",1,32)
STRTOFILE(STRCONV(lcXML,9),"customer.xml")
XMLTOCURSOR("customer.xml","curCustomer",512)
RETURN

In the example, you can use a value of 48 for the nFlag parameter in the STRCONV( ) function to create the XML encoded as UTF-8. However, this does not work for XML provided from an external source or with XML that is not generated with the CURSORTOXML( ) function.

For more information, see STRCONV( ) Function.

Importing and Exporting XML Using Schemas

When you import XML using the XMLTOCURSOR( ) function, Visual FoxPro uses an external or internal schema, if available, to determine the cursor or table structure before making a single pass to convert the data. When no schema is provided, Visual FoxPro makes two passes through the XML data: the first pass determines the data structure in the best possible way and the second to perform the conversion. The XML must generally conform to a format that can be interpreted as a table in addition to being well-formed. Well-formed XML that is not easily deconstructed into a table format will fail to import.

The following is an example of XSD schema generated by the Visual FoxPro CURSORTOXML( ) function:

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<xsd:schema id="VFPSchema" targetNamespace="https://microsoft.com" xmlns="https://microsoft.com" xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" elementFormDefault="qualified">
   <xsd:element name="atxm">
      <xsd:complexType>
         <xsd:all>
            <xsd:element name="ikey" minOccurs="0" type="xsd:int"/>
            <xsd:element name="nc00" minOccurs="0">
               <xsd:simpleType>
                  <xsd:restriction base="xsd:decimal">
                     <xsd:precision value="10"/>
                     <xsd:scale value="4"/>
                  </xsd:restriction>
               </xsd:simpleType>
            </xsd:element>
            <xsd:element name="mc03" minOccurs="0">
               <xsd:simpleType>
                  <xsd:restriction base="xsd:string">
                     <xsd:maxLength value="2147483647"/>
                  </xsd:restriction>
               </xsd:simpleType>
            </xsd:element>
            <xsd:element name="cc04" minOccurs="0">
               <xsd:simpleType>
                  <xsd:restriction base="xsd:string">
                     <xsd:maxLength value="128"/>
                  </xsd:restriction>
               </xsd:simpleType>
            </xsd:element>
            <xsd:element name="lc05" minOccurs="0" type="xsd:boolean"/>
            <xsd:element name="fc06" minOccurs="0" type="xsd:double"/>
            <xsd:element name="yc07" minOccurs="0" type="xsd:decimal"/>
            <xsd:element name="ic08" minOccurs="0" type="xsd:int"/>
            <xsd:element name="bc09" minOccurs="0" type="xsd:double"/>
            <xsd:element name="dc10" minOccurs="0" type="xsd:date"/>
            <xsd:element name="tc11" minOccurs="0" 
type="xsd:timeInstant"/>
            <xsd:element name="ts12" minOccurs="0">
               <xsd:simpleType>
                  <xsd:restriction base="xsd:binary">
                     <xsd:encoding value="base64"/>
                  </xsd:restriction>
               </xsd:simpleType>
            </xsd:element>
         </xsd:all>
      </xsd:complexType>
   </xsd:element>
   <xsd:element name="VFPData" msdata:lsDataSet="true">
      <xsd:complexType>
         <xsd:choice maxOccurs="unbounded">
            <xsd:element ref="atxm"/>
         </xsd:choice>
      </xsd:complexType>
   </xsd:element>
</xsd:schema>

Currently, Visual FoxPro exports XML in the following formats:

**Element-centric **Each field in a resulting or source cursor or table is represented by a sub-element of the top-level element.

<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>
<!-- Note targetNamespace in root (VfpData) element. If set to default (""), xmlns attrib is not written -->
<VfpData xmlns="https://www.microsoft.com">
   <alltypesxm>
      <ikey>2</ikey> 
      <nc00>1.1111</nc00> 
      <mc03>H1111111111111111111</mc03> 
      <cc04>H111111111111111111</cc04> 
      <lc05>true</lc05> 
      <fc06>-1111000</fc06> 
      <yc07>-111111111.1111</yc07> 
      <ic08>-11111111</ic08> 
      <bc09>-111111111111.1</bc09> 
      <dc10>1999-03-02T08:00:00</dc10> 
      <tc11>1999-03-02T09:01:01</tc11> 
      <ts12>AAAAAAAAAr8=</ts12>
   </alltypesxm>
</VfpData>

Attribute-centric The cursor is identified by the keyword "VFPData" and each field in a resulting or source cursor or table is represented by an attribute of the VFPData element.

<?xml version="1.0" encoding="Windows-1252" ?>
<!-- Note targetNamespace in root element -->
<VFPData xmlns="https://www.microsoft.com">
   <atxm_attr ikey="2" nc00="12345.1111" 
      mc03="H1111111111111111111" cc04="H111111111111111111" lc05="1" 
      fc06="-1111000.0000" yc07="-111111111.1111" ic08="-11111111" 
      bc09="-111111111111.100000" dc10="1999-03-02" 
      tc11="1999-03-02T01:01:01" ts12="AAAAAAAAAr0=" /> 
   <atxm_attr ikey="3" nc00="2.1111" mc03="H2222222222222222222"
      cc04="H222222222222222222" lc05="1" fc06="22220000.0000"
      yc07="2222222222.2222" ic08="222222222"
      bc09="222222222222.2200000" 
      tc11="2000-10-03T02:02:02" ts12="AAAAAAAAAr8=" /> 
</VFPData>

Raw Each row in a resulting or source cursor or table is represented by an XML element with the generic identifier "row", and each column value is mapped to an attribute of the row element where the attribute name is equal to the column name. This format is identical to Attribute-centric except for "row" as the name of the top-level element.

<?xml version="1.0"?>
<!-- Note no targetNamespace in root element -->
<VFPData>
   <row CustomerID="CACTU" CompanyName="Cactus Comidas para llevar"
      ContactName="Patricio Simpson" ContactTitle="Sales Agent"
      Address="Cerrito 333" City="Buenos Aires" PostalCode="1010"
      Country="Argentina" Phone="(1) 135-5555" Fax="(1) 135-4892"/> 
</VFPData>

Converting Between XML and Visual FoxPro Data

Processing XML Data in Variables or Strings Instead of Files

XML String Conversion

Importing and Exporting XML Using Schemas

See Also

Reference

Concepts

Other Resources

Additional resources