XMLTOCURSOR( ) Function

  • Article

Converts XML text into a Visual FoxPro cursor or table.

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

Parameters

  • eExpression
    Specifies the XML text or an expression that evaluates to valid XML data. The eExpression parameter can be a Visual FoxPro memory variable, memo field contents, the return from an HTTP request, the return result from a SOAP method call, XML from the XMLDOM, or an ADO stream.

    Note

    XMLTOCURSOR( ) generates an error if eExpression 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 path along the Visual FoxPro directory for the XML file.

  • cCursorName
    Specifies the name of the cursor to store 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.

    If cCursorName contains data and nFlags is set to 8192, 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

    (Default) Treats the first parameter as a string containing XML data.

    4

    0100

    Preserves white space in data and overrides the xml:space attribute of the XML data.

    512

    01000000000

    Specifies that the first parameter, either an eExpression or a 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.

    32768

    0x8000

    Indicates that a code page should be used.

    65536

    0x10000

    Maps XML Char fields to native Fox Varchar fields. If not specified, XML Char fields are mapped to Fox Character fields.

    131072

    0x20000

    Maps XML base64Binary fields to native Fox Varbinary fields if less than 255 bytes or Blob fields if greater than this length. If not specified, XML base64Binary fields are mapped to Fox Memo fields.

    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 table 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.

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 but truncates any data exceeding the length of a Character column.

Currency

Accepts numeric data within accepted range of the Currency type but truncates numbers outside this range.

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 and discards precision beyond the extent supported by Visual FoxPro.

Double

Accepts numeric data within the accepted range of the Double type but truncates numbers outside this range.

Integer

Accepts integer data within the accepted range of the Integer type but truncates numbers outside this range.

Logical

Accepts the values True, .T., 1, False, .F., and 0.

Numeric, Float

Accepts numeric data with or without decimals but truncates numbers outside the accepted range for these types according to Visual FoxPro rules or replaces with the numeric overflow indicator ("*************").

Return Value

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

Remarks

You can use XMLTOCURSOR( ) with the OLE DB Provider for Visual FoxPro. However, the _VFP VFPXMLProgID property is not supported because the _VFP system variable is not supported in the OLE DB Provider.

Note

To use the Visual FoxPro OLE DB Provider with XMLTOCURSOR( ), you must install MSXML 3.0 on the computer with the OLE DB Provider.

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.

XMLTOCURSOR( ) does not use the Varchar type when creating a cursor from an XML document. However, XMLTOCURSOR( ) supports appending data into an existing cursor with Varchar fields.

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

XMLTOCURSOR( ) converts Decimal data type to Numeric 20,19 (width, precision). In versions prior to Visual FoxPro 8.0, Decimal mapped to Numeric 8,0.

Numeric Overflow Conditions   XMLTOCURSOR( ) imports XML data containing Visual FoxPro 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 asterisk (*) characters to 0.0 or 0 values. Therefore, the XML parser used by XMLTOCURSOR( ) does not throw an error as a result of character values stored in a decimal type element.

For more information about converting XML to Visual FoxPro data, see Converting Between XML and Visual FoxPro Data.

See Also

Reference

CURSORTOXML( ) Function
XMLUPDATEGRAM( ) Function

Concepts

XML Data in Visual FoxPro

Other Resources

Functions