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:
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
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 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)
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 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 |
: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" | < Day Day Up > |
EAN: 2147483647
Pages: 130