XML Functions

To enable you to more easily implement data exchange through XML formatting, Visual FoxPro provides new functions and functionality in the XMLUPDATEGRAM( ), CURSORTOXML( ), and XMLTOCURSOR( ) functions.

Whether you import XML data using XMLTOCURSOR( ) or export data using CURSORTOXML( ), valid documents are well-formed XML.

Well-formed Documents

Any XML document produced by Visual FoxPro is well-formed, which means that it conforms to the basic rules of XML. That is:

  • Each XML document must have a unique root element (an element encompassing the entire document).

  • All start and end tags match. XML tags are case-sensitive.

  • For each start tag, there is a corresponding end tag. Empty elements can be denoted by a special shorthand tag.

  • Elements do not overlap. In other words, start and end tags must be properly nested within other elements.

  • Certain reserve characters are part of the XML syntax and will not be interpreted as themselves if used in the data portion of an element. You need to substitute a special character sequence (called an "entity" by XML) as follows:

    Character Data Type Entity Encoding
    &   (ampersand)
    <   (left angle bracket)
    >   (right angle bracket)
    "   (double quote)
    '   (apostrophe)
    String
    String
    String
    String
    String
    Replace with &amp;
    Replace with &lt;
    Replace with &gt;
    Replace with &quot;
    Replace with &apos;

    For other data types, the encoding follows these rules:

    Data Type Character and Entity Encoding
    Date Must follow the ISO 8601 format
    Numbers Punctuation must use U.S. English rules. For example, you must use a period as a decimal separator. Numbers can include exponents.
    Boolean False = -1, True = 1. (SQL XML returns 1 & 0)
    BLOB Use MIME Base64 encoding

The following is an example of a well-formed XML document:

<?xml version="1.0"?>
<Data>
<ORDER>
 <CUSTOMER>Nancy Davolio</CUSTOMER>
  <ITEM>Mom&apos;s Boston Crab Meat</ITEM>
 <PRICE>$10.00</PRICE>
 <QUANTITY>1 Bottle</QUANTITY>
 </ORDER>
</Data>

where:

  • <?xml version="1.0"?>
    Specifies a declaration that this is an XML document and gives the version number. The declaration is optional, but recommended in any XML document.

  • <ORDER>
    Specifies the root element, which encompasses the entire document.

  • <CUSTOMER> ... </CUSTOMER>
    Specifies a start tag and an end tag set, which together describes an element of data, in this case, the customer's name.

    Note   Each tag set, such as <CUSTOMER> ... </CUSTOMER>, has both start and end tags, is case sensitive, and is properly nested. Also notice the entity &apos; which will be transformed to an apostrophe (') when the data is imported by the receiving application. The apostrophe has a special purpose in an XML document, and can be misinterpreted if used directly in the text. The converted data will be displayed as Mom's Boston Crab Meat.

You can use white space throughout the document to enhance readability.

If you specify a schema while exporting from Visual FoxPro, the exported XML documents are considered to be valid XML. This means that in addition to being well-formed, the documents conform to a defined schema.

When you import XML using XMLTOCURSOR( ), Visual FoxPro uses an external or internal schema to determine the cursor or table structure. When no schema is provided, Visual FoxPro uses a "best guess" method to interpret XML data. This involves two passes through the XML, one to determine data structure and one to perform the actual conversion. Note that the XML, in addition to being well-formed, must generally conform to a format that can be interpreted as a table. Well-formed XML that is not easily deconstructed into a table format will fail to import.

Schemas

Schemas describe the structure of data in a common format that customers, other Web browsers, and any number of XML-enabled software programs can recognize. The description defines the rules of an XML data document including element names and data types, which elements can appear in combination, and which attributes are available for each element. Schemas provide a model for an XML data document which defines the arrangement of tags and text within all documents referencing the schema. Visual FoxPro supports the XML Schema standard (XSD), a basic infrastructure for describing the type and structure of XML documents.

By using a schema you can ensure that any XML document that is used to import or export data contains specific data and conforms to a defined structure. You can also provide the schema to other businesses and applications so that they can structure any data they provide to you and they, in turn, can provide their schema to you.

Visual FoxPro supports the W3C XSD schema format. Following is an example of XSD schema as output by CURSORTOXML( ) function:

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<xsd:schema id="VFPSchema" targetNamespace="http://microsoft.com" xmlns="http://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>

The XSD schema format is based on the W3C 24 Oct 2000 Working Draft issue of the XSD Schema specification: Primer, Structures, and Datatypes. For details, see http://www.w3.org/TR/2000/CR-xmlschema-0-20001024/.

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 subelement of the top-level element.

    Note   The XSD/XML spec (Oct 2000 requires that Boolean be converted to the literal true or false case as shown.

    <?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="http://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 "root" and each field in a resulting or source cursor or table is represented by an attribute of the root element.

    <?xml version="1.0" encoding="Windows-1252" ?>
    <!-- Note targetNamespace in root element -->
    <root xmlns="http://www.microoft.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=" /> 
    </root>
    
  • Row 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 -->
    <root>
       <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"/> 
    </root>
    

Data Type mapping

The following table describes the mapping of Visual FoxPro data to XSD types.

Visual FoxPro Type XSD Type Comments
Character String with restriction
<xsd:maxLength value="128"/>
 
Currency Decimal  
Numeric Decimal with restriction
 <xsd:precision value="10"/>
      <xsd:scale value="4"/>
Precision is the number of digits excluding the decimal point.
Float Decimal with restriction
<xsd:precision value="10"/>
      <xsd:scale value="4"/>
       "
Date date  
DateTime timeInstant  
Double double  
Integer int  
Logical boolean  
Memo String with restriction
<xsd:maxLength value="2147483647"/>
 
General Not supported  
Character (binary) Binary with restriction
<xsd:encoding value="base64"/>
 
Memo (binary) Binary with restriction
<xsd:encoding value="base64"/>
 

Locale attribute

If the System locale is not "us-en", then the locale attribute is written to the XSD schema element as well as the table-name element. This requires the msprop namespace declaration as well.

Locale msdata:Locale
German de
Spanish es
French fr

See Also

CURSORTOXML( ) Function | XMLTOCURSOR( ) Function | XMLUPDATEGRAM( ) Function | VFPXMLProgID Property