Commands to Move Data Between a Table and XML

< Day Day Up >

Although you can write your own code to move data between tables and XML, the CursorToXML() and XMLToCursor() functions introduced in FoxPro 7 do a pretty good job in many cases. If that's not enough, the new XMLAdapter and CursorAdapter in Visual FoxPro 8 add many other capabilities, and actually do what you thought you were going to be able to do with XMLUpdateGram() output. I originally assumed that an UpdateGram could be used in some automatic fashion to update the table that the original record came from. I've since been informed that the XMLUpdateGram was never meant to do anything except represent the record before and after changes. We were expected to write our own code to apply the change.

`CursorToXML()`

The official syntax of FoxPro's CursorToXML() function is as follows :

  CursorToXML(  nWorkArea  cTableAlias,  cOutput  [, nOutputFormat  [, nFlags  [, nRecords  [, cSchemaName  [, cSchemaLocation  [, cNameSpace ]]]]]]  )

The first parameter can be a work area number, although I always use an alias. You can also use ALIAS() to use the currently selected table.

The second parameter is a little strange for Visual FoxPro developers, because it can be either a filename or a variable name; however, what it expects is either a filename or variable name in quotes, or a filename or variable name stored in a variable.

The third parameter is the output format of the result, and can be one of three values:

Elements
Attributes
Raw (generic)

For a table containing fields Name, Counter, and Date, this is what the XML would look like for these three formats:

 Elements: <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData>         <test>                 <name>Pinter</name>                 <counter>1234</counter>                 <date>2004-03-12</date>         </test> </VFPData> Attributes: <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData>         <test name="Pinter" counter="1234" date="2004-03-12"/> </VFPData> Raw: <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData>         <row name="Pinter" counter="1234" date="2004-03-12"/> </VFPData>

If we're producing XML for our own use, the first type, which is the default, is okay. External users may need one of the other two formats.

The fourth parameter is the sum of the values of any of the parameters listed in Table 7.1. This is analogous to the way you add values in the MessageBox() function (for example, 4 + 32 + 256 means "Display a question mark"; prompts include Yes and No, and default to No, but you can just use 292).

Table 7.1. Valid Values of the Flags Parameter (the fourth parameter) of the CursorToXML() Function

Value	Description
	UTF-8 formatting (default)
1	Continuous string
2	Use beginning and ending tags for empty elements (that is, `<a></a>` instead of `<a/>` )
4	Conserve white space
8	Enclose memo fields in `CDATA` sections
16	Use cursor's code page
32	Use UTF-8 without character translation (16+32 = do translation)
512	Create a file instead of a memory variable

For the following examples I'll assume a table named Clients containing a single record.

Example 1:

 CursorToXML ( "Clients", "lcClients" )

This code will store the XML shown in Listing 7.11 in the variable lcClients .

Listing 7.11. Generated XML

 <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData>     <clients>         <first>Les</first>         <last>Pinter</last>         <phone>650-344-3969</phone>     </clients> </VFPData>

Example 2:

 CURSORTOXML ("CLIENTES", "CLIENTES.XML", 1, 512, 0, "1")

This code will produce a table containing the results shown in Listing 7.12.

Listing 7.12. Generated XML

 <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData>     <xsd:schema id="VFPData" xmlns:xsd=http://www.w3.org/2001/XMLSchema xmlns:msdata="urn  :schemas-microsoft-com:xml-msdata">         <xsd:element name="VFPData" msdata:IsDataSet="true">             <xsd:complexType>                 <xsd:choice maxOccurs="unbounded">                     <xsd:element name="clients" minOccurs="0" maxOccurs="unbounded">                         <xsd:complexType>                             <xsd:sequence>                                 <xsd:element name="first">                                     <xsd:simpleType>                                         <xsd:restriction base="xsd:string">                                             <xsd:maxLength value="15"/>                                         </xsd:restriction>                                     </xsd:simpleType>                                 </xsd:element>                                 <xsd:element name="last">                                     <xsd:simpleType>                                         <xsd:restriction base="xsd:string">                                             <xsd:maxLength value="15"/>                                         </xsd:restriction>                                     </xsd:simpleType>                                 </xsd:element>                                 <xsd:element name="phone">                                     <xsd:simpleType>                                         <xsd:restriction base="xsd:string">                                             <xsd:maxLength value="13"/>                                         </xsd:restriction>                                     </xsd:simpleType>                                 </xsd:element>                             </xsd:sequence>                         </xsd:complexType>                     </xsd:element>                 </xsd:choice>                 <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace"  processContents="lax"/>             </xsd:complexType>         </xsd:element>     </xsd:schema>     <clients>         <first>Les</first>         <last>Pinter</last>         <phone>650-344-3969</phone>     </clients> </VFPData>

`XMLToCursor()`

The official syntax of the XMLToCursor() command is as follows:

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

The first parameter is the name of a memory variable, or the name of a file if the third parameter is 512.

The second parameter is the name of the cursor where the XML will be converted to a table. If the cursor does not already exist, and if the XML contains a schema, the cursor will be created using the schema definition. If no schema definition is included, all fields will be created as type Character , and the length of each field will be the length of the longest string in each data element.

The third parameter is a flag whose value is the sum of any applicable switches, as described in Table 7.2.

Table 7.2. `XMLToCursor()` Flag Values (Third Parameter)

Value	Description
	The first parameter is the name of a variable containing XML.
4	Conserve white space.
512	The first parameter is a filename (fully qualified).
1024	Don't translate the code page. Produces weird results.
2048	Convert numeric(19,4) to Currency.
4096	Special for binary data.
8192	Don't modify the target table's structure (Visual FoxPro 8!).

The last flag is new in Visual FoxPro 8 and is most welcome. Previously, table structures were always changed to reflect the longest string in each field, and data types occasionally were changed as well. As a result, it was generally necessary to write to a temporary cursor, then append the data from it into the original structure, as shown in Listing 7.13.

Listing 7.13. How `XMLTOCURSOR()` Without an XSD Uses Field Lengths to Determine Table Structure

 <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData>     <clients>         <first>Les</first>         <last>Pinter</last>         <phone>650-344-3969</phone>     </clients> </VFPData> XMLToCursor(lcxml,"TEST") DISP STRU Structure for table:   TEST  Field  Field Name      Type                Width    Dec   Index   Collate Nulls     1  FIRST           Character               3                             No     2  LAST            Character               6                             No     3  PHONE           Character              12                             No ** Total **                                   22

If the XSD is included, the table is rendered properly, as shown in Listing 7.14.

Listing 7.14. Generated XML

 <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData>   <xsd:schema id="VFPData" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn  :schemas-microsoft-com:xml-msdata">     <xsd:element name="VFPData" msdata:IsDataSet="true">       <xsd:complexType>         <xsd:choice maxOccurs="unbounded">           <xsd:element name="clients" minOccurs="0" maxOccurs="unbounded">             <xsd:complexType>               <xsd:sequence>                 <xsd:element name="first">                   <xsd:simpleType>                     <xsd:restriction base="xsd:string">                       <xsd:maxLength value="15"/>                     </xsd:restriction>                   </xsd:simpleType>                 </xsd:element>                 <xsd:element name="last">                   <xsd:simpleType>                     <xsd:restriction base="xsd:string">                       <xsd:maxLength value="15"/>                     </xsd:restriction>                   </xsd:simpleType>                 </xsd:element>                 <xsd:element name="phone">                   <xsd:simpleType>                     <xsd:restriction base="xsd:string">                       <xsd:maxLength value="13"/>                     </xsd:restriction>                   </xsd:simpleType>                 </xsd:element>               </xsd:sequence>             </xsd:complexType>           </xsd:element>         </xsd:choice>         <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace"  processContents="lax"/>       </xsd:complexType>     </xsd:element>   </xsd:schema>   <clients>     <first>Les</first>     <last>Pinter</last>     <phone>650-344-3969</phone>   </clients> </VFPData>

You can use the XMLToCursor() function to convert this XML back into a cursor, and DISPLAY Structure to view the resulting table:

Example: The following two lines of code produce the output that follows the code:

 XMLTOCURSOR(lcxml,"TEST") DISPLAY STRUCTURE   && (Output from DISPLAY STRUCTURE follows): Structure for table:  TEST Field  Field Name      Type                Width    Dec   Index   Collate Nulls     1  FIRST           Character              15                             No     2  LAST            Character              15                             No     3  PHONE           Character              13                             No ** Total **                                   44

< Day Day Up >

CursorToXML()