XMLTOCURSOR( ) Function

  • Article

Converts XML text into a Visual FoxPro cursor or table.

XMLTOCURSOR(XMLSource eExpression | cXMLFile [, cCursorName [, nFlags ]])

Parameters

  • eExpression
    Specifies the XML text or an expression that evaluates to valid XML data. This parameter can be a Visual FoxPro memory variable, memo field contents, or the return from an HTTP request. You also can specify the return result from a SOAP method call, XML from the XMLDOM, or an ADO stream. XMLCURSOR( ) generates an error if XMLSource is not found, or if eExpression does not parse to valid XML.

  • cXMLFile
    Specifies the name, and optionally, the path of a physical XML file that resides on your local computer or network. If you do not specify a path, Visual FoxPro searches the FoxPro path for the XML file.

  • cCursorName
    Specifies the name of the cursor for storing the result and creates the cursor in the current work area. If the cursor name already exists or is open, Visual FoxPro closes the cursor and creates a new one in an unused work area. If you omit or pass an empty string ("") for cCursorName, Visual FoxPro creates a cursor named XMLRESULT for returning the result.

    When nFlags is set to 8192, and if cCursorName contains data, Visual FoxPro appends the data being imported from the XML file to the existing table or cursor. The table or cursor must be open or in use. If cCursorName is an empty string (""), Visual FoxPro imports XML into the table or cursor that is open in the current work area.

  • nFlags
    Specifies how XMLSource eExpression is handled in XMLTOCURSOR( ). The following table describes the values for nFlags.

    nFlags Bit Description
    0 0000 Treats the first parameter as a string containing XML data. (Default)
    4 0100 Preserves white space in data and overrides the xml:space attribute of the XML data.
    512 01000000000 Specifies that the first parameter, XMLSourceeExpression or cXMLFile, is a string containing the name and path of an XML data file.
    1024 10000000000 NOCPTRANS – Creates Character and Memo fields in the resultant cursor with the NOCPTRANS option and inserts the text or XML values in the incoming elements into the Character or Memo field on an untranslated, byte-for-byte basis.

    When used with flag 1024, XMLTOCURSOR( ) returns a string padded with trailing spaces equal to the actual string, that is, a string twice the length.
    2048 100000000000 Use when the imported schema has an XML Schema Definition (XSD) schema containing decimal data type with restrictions, or facets, of totalDigits="19" and fractionDigits="4".

    XSD data type values are mapped to the Visual FoxPro Currency data type in the resulting cursor.
    4096 1000000000000    Disables base64 decoding.

    In Visual FoxPro, base64 encoding is intended for encoding only binary data.
    8192 1100000000 Specifies that cCursorName is the name or alias of an existing table or cursor and imports the data from the specified XML file into an existing table or predefined cursor.

    If cCursorName contains data, the data imported from the XML file is appended to the existing data. If cCursorName is an empty string (""), the data from the XML file is imported into the table or cursor in the current work area.

    Setting nFlags to 8192 can be useful when an XML schema is not available or not practical for use.

    When you use flag 8192, be aware of the following:

    • You must make sure the table schema matches the incoming XML elements in the appropriate manner. Visual FoxPro enforces the data types in cCursorName as described in the Data Type Matching section in this topic but makes no other assumptions about data types. Attempting to import incompatible values generates the appropriate message.
    • If the XML contains or references a schema, and the data types in the schema conflict with the data types of the cursor or table, the data types in the cursor or table are used.
    • Element names in the XML file are mapped to the column names in cCursorName. In addition, Visual FoxPro imports only data from the element names in the XML file that match the column names in cCusorName.
    • The cursor or table can have fewer columns than those in the XML file, but it must have at least one matching column. The cursor or table can have additional columns that do not correspond to an element in the XML file.
    • If the table contains autoincrementing fields, XMLTOCURSOR( ) fails if AUTOINCERROR is set to ON. Setting AUTOINCERROR to OFF or turning off autoincrementing in the target table by using CURSORSETPROP( ) allows XMLTOCURSOR( ) to succeed. The target table's autoincrementing field or fields are incremented according to the values specified, and the values in the source table are not copied.

Return Values

Numeric data type. XMLTOCURSOR( ) returns the number of records created.

Remarks

Visual FoxPro interprets XML files with or without schema. If no schema is provided, Visual FoxPro makes two passes through the XML data. The structure is determined during the first pass; the conversion is performed during the second pass. Visual FoxPro uses either external or internal schema to determine the cursor or table structure before making a single pass through the XML data.

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. If you need to use a memory variable or string, you can force interpretation to DBCS using the following code in XMLTOCURSOR( ):

STRCONV(string,11)

as shown in the following example:

CLEAR
CLOSE DATABASES ALL
USE HOME()+'samples\data\customer'
CURSORTOXML('customer','lcXML',1,48,5,"","","") 
* XMLTOCURSOR() inserts incorrect characters in the resulting cursor.
XMLTOCURSOR(lcXML,"curCustomerList",4)
* Use STRCONV() in XMLTOCURSOR() to convert to DBCS data correctly.
XMLTOCURSOR(STRCONV(lcXML,11),"curCustomerList",4) 
BROWSE
RETURN

XMLTOCURSOR( ) does not perform automatic conversions of XML strings, for example, from ANSI to UTF-8. This is likely to occur when reading an XML string from a file. You can use the STRCONV( ) function to do a conversion. The following example demonstrates the parse error when Visual FoxPro encouters 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, either 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 above example, users could use a value of 48 for the nFlag paramter 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.

When calling XMLTOCURSOR( ) in append mode, Visual FoxPro adds an empty row if no XML element name matches any table or cursor column.

XMLTOCURSOR( ) generates a syntax error for record lengths greater than approximately 160 columns. The exact number of columns that cause an error depends on the length of the column names.

XML that have external or inline XSD schema as generated by Visual Studio .NET DataSets might contain elements not supported by Visual FoxPro, which are disregarded. For example, some of these elements might include the following:

  • xs:unique name
  • xs:annotation
  • Additional processing instructions and attributes such as the following:
    • Schema prefix support, such as msdata:Prefix, that defines the scope of a group of elements
    • XML prefix support, such as the vfpx attribute, that scopes the prefix to root elements
    • msprop attribute and other attributes that use this namespace
    • Other msdata attributes that reference items not supported by Visual FoxPro, such as msdata:Locale or msdata:Comment

XSD Data Type to Visual FoxPro Data Type Mapping

The following table lists how Visual FoxPro handles XSD data types in XMLTOCURSOR( ).

XSD data type Visual FoxPro data type
xsd:binaryBase64 Memo (Binary)
xsd:boolean Logical
xsd:date Date
xsd:dateTime DateTime
xsd:decimal Numeric
xsd:double Double
xsd:int Integer
xsd:string Character
xsd:string (more than 254 characters in length) Memo

Data Type Matching

The following table describes data type matching restrictions when setting nFlags to 8192.

Visual FoxPro data type Behavior
Character, Character (Binary), Memo, Memo (Binary) Accepts any data. Data exceeding the length of a character column is truncated.
Currency Accepts numeric data within accepted range of the Currency type. Numbers outside this range are truncated.
Date Accepts XML format date and dateTime values. Visual FoxPro converts the XML date and dateTime formats to the corresponding Visual FoxPro date. Visual FoxPro preserves only the M/D/Y portion of the XML date or dateTime value.
DateTime Accepts XML format date and dateTime values. Visual FoxPro converts the XML date and dateTime formats to the corresponding Visual FoxPro date. Precision beyond that which is supported by Visual FoxPro is discarded.
Double Accepts numeric data within accepted range of the Double type. Numbers outside this range are truncated.
Integer Accepts integer data within accepted range of the Integer type. Numbers outside this range are truncated.
Logical Accepts the values True, .T., 1, False, .F., and 0.
Numeric, Float Accepts numeric data with or without decimals. Numbers outside the accepted range for these types are truncated according to Visual FoxPro rules or replaced with the numeric overflow indicator ("*************").

Numeric Overflow Conditions   XMLTOCURSOR( ) imports XML data containing Visual FoxPro-type numeric overflow conditions, for example, *****.** in place of a numeric or integer value. If a schema is provided, Visual FoxPro creates the correct data type in the resulting cursor but changes the overflow from containing asterisk (*) characters to 0.0 or 0 values. Therefore, the XML parser used by XMLTOCURSOR( ) does not error as a result of character values stored in a decimal type element.

See Also

XML Functions | CURSORTOXML( ) Function | XML UPDATEGRAM( ) Function