FileMaker Pro 6 Developers Guide to XML/XSL (Wordware Library for FileMaker)

In FileMaker Pro 6, you can export and import using the new application programming interface (API) called, appropriately, "XML." This plug-in is located in the System folder on the Windows operating system and the FileMaker Extensions folder on the Macintosh operating system. Unlike other plug-ins, the XML plug-in does not need to be enabled, as it is always available. The XML plug-in uses the Xerces XML parser and the Xalan processor to import and export XML. You can read more about the parser and processor at http://www.apache.org or through your favorite Internet search engine.

An XML export or import is very similar to other text export or import options in FileMaker Pro. You don't need to know how to web publish to export and import XML data with FileMaker Pro 6. There is a slightly different dialog for specifying the XML format for export and for specifying the use of an optional stylesheet. The following examples show you how the XML exports and imports differ from other text exports and imports in FileMaker Pro 6.

2.1 XML Export

This section will present the FileMaker Pro 6 Export options for XML. The first example uses the FMPDSORESULT grammar. The second example uses the FMPXMLRESULT grammar. FileMaker Pro 6 uses two different grammars for XML Export, and these will be discussed later in this chapter and in Chapter 4, "FileMaker Pro XML Schema or Grammar Formats (DTDs)." Special field export considerations will also be discussed in this section.

2.11 Setting Up XML Export

To set up a manual export with XML, choose File, Export Records. Navigate to a directory and name a file to export. Add the appropriate extension, such as ".xml" for a simple export. You may be using another extension, ".txt" or ".htm" for example, if you transform the exported data by choosing a stylesheet. Select XML for the Type and click the Save button. So far this XML export has been similar to other exports such as tab-separated text or comma-separated text. The XML export is just another type of text export. Figure 2.1 shows where this type of export differs from the other types of exports.

Figure 2.1: Specify XML export options

Choose the FMPDSORESULT grammar, but do not select the Use XSL stylesheet check box. XSL stylesheets will be discussed in Chapter 7, with instructions for exporting and selecting the stylesheet option. After you choose the FMPDSORESULT grammar, click the OK button and you will be presented with the next dialog, shown in Figure 2.2. Again, you are given a dialog for XML export.

Figure 2.2: Select fields to export

In the Specify Field Order for Export dialog, you have several options. Click a field and the Move button to select a field for export. You may, optionally, double-click a field on the left to move it to the right Field Order box. You may rearrange or clear fields in the Field Order list. The number of records depends upon the found set prior to export, and the record order for export depends on any sort performed prior to export.

Another option is chosen by selecting a relationship on the left to export any related fields. Related fields and the other two options, formatting output or summarizing output, will be discussed later in this chapter. To see an example of a simple export, only select fields in the Current File.

Click the Export button and a text file is created with the name you specified. You may view your XML file with any text editor. To see the tree-like structure of your data, some HTML editors will reformat the text with indentations. The Microsoft Internet Explorer browser also has a default stylesheet that will reformat the XML with indentations. While it's convenient to see this structure as a "pretty-print", the XML parsers do not need to have the text reformatted.

2.12 FMPDSORESULT Export

The FMPDSORESULT grammar creates elements with the name of each field as the name of each element. This format more closely resembles other XML schema or grammars that you may have seen. If a field name has a space, the FMPDSORESULT export will convert each space to an underscore character (_). XML element names should not have any spaces. The FileMaker Pro 6 Help topic "XML FMPDSORESULT Grammar" also recommends that you do not name your fields with a leading number. The export will be correct, but element names beginning with numbers may not display properly in a browser or with other XML parsers.

Each record in the found set will be exported with the ROW element. Two attributes for the ROW element are RECORDID and MODID. FileMaker Pro automatically creates a record ID value each time a record is created, duplicated, or imported. The record ID is unique for each record in a single database. The value is not sequential and should not be used as a key match field in relationships, but it may be used to find a unique record. You can see the value of the RECORDID in your database by creating a calculation field = Status(CurrentRecordID). The MODID is the same as the FileMaker Pro function Status(CurrentRecordModificationCount) and is incremented each time a record is changed and committed. You can read more about Status(CurrentRecordID) and Status(CurrentRecordModificationCount) in FileMaker Pro Help.

The FMPDSORESULT grammar also creates some elements to describe the data being exported. ERRORCODE is a special element showing an error, if any. If the found set of records is empty, the menu item Export Records is grayed out and you cannot export an empty set of records. You should not get any error with FileMaker Pro 6 XML export. If you do get an error, you can find a list of the error codes in the Help topic "Status(CurrentError) Function." More information about error codes can also be found in Chapter 5, section 5.5, "Error Codes for XML."

The DATABASE element shows the name of the file that created the export. The LAYOUT element is empty if you don't click the Format output using current layout option in the Specify Field Order for Export dialog. An example FMPDSORESULT export is shown in Listing 2.1. Notice the field names as elements and the underscore for spaces in the field names.

Listing 2.1: Simple XML export with FMPDSORESULT

<?xml version="1.0" encoding="UTF-8" ?> <FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult"> <ERRORCODE>0</ERRORCODE> <DATABASE>Export.FP5</DATABASE> <LAYOUT></LAYOUT> <ROW MOD RECORD> <First_Name>Beverly</First_Name> <Last_Name>Voth</Last_Name> <City>London</City> <State>KY</State> </ROW> </FMPDSORESULT>

The order of the fields exported is not of importance when using XML unless you need to import the fields in the same order. You will see later that the XML structure is very flexible, as only the required data can be extracted when needed. Using a stylesheet to display the XML as a presentation document, the fields First_Name and Last_Name can be placed in the resulting display as Last_Name, comma, space, and First_Name. If you did not sort prior to export, the stylesheet can loop through the XML elements and extract the data in a sorted fashion.

The next export, with FMPXMLRESULT, is different from the FMPDSORESULT in structure. The found set and sort order can be used prior to any export. Stylesheets can be used to transform FMPDSORESULT and FMPXMLRESULT. Read more about stylesheets in Chapter 7.

2.13 FMPXMLRESULT Export

The FMPXMLRESULT is more similar to a spreadsheet with rows and columns. The field names are enclosed inside the NAME attribute of each FIELD element. Spaces in field names are less important with this grammar because the names are enclosed in double quotes. Each FIELD element is in the METADATA element. The list of field names at the beginning of XML documents is similar to the first row of a spread-sheet with the column names. The content of each field is inside a COL and DATA element. Listing 2.2 shows a simple export with FMPXMLRESULT. The PRODUCT and DATABASE elements may have different attribute values in your export. Compare this export with the export in Listing 2.1.

Listing 2.2: Simple XML export with FMPXMLRESULT

<?xml version="1.0" encoding="UTF-8" ?> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="08/09/2002" NAME="FileMaker Pro" VERSION="6.0v3" /> <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="Export.FP5" RECORDS="1" TIMEFORMAT="h:mm:ss a" /> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="First Name" TYPE="TEXT" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Last Name" TYPE="TEXT" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="City" TYPE="TEXT" /> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="State" TYPE="TEXT" /> </METADATA> <RESULTSET FOUND="1"> <ROW MOD RECORD> <COL> <DATA>Beverly</DATA> </COL> <COL> <DATA>Voth</DATA> </COL> <COL> <DATA>London</DATA> </COL> <COL> <DATA>KY</DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT>

There are some elements included with the FMPXMLRESULT export that are not a part of the FMPDSORESULT export. The name of the database is the value of the attribute NAME in the DATABASE element. Additional attributes are found for the DATABASE element. The DATEFORMAT and TIMEFORMAT attributes specify how these types of fields are formatted. The date and time export may depend upon your computer's date and time control panel settings at the time the data-base was created or cloned. More information about date and time exports is discussed in section 2.2, "Special Export Considerations." The DATABASE element also shows the number of records in the data-base in the RECORDS attribute. This RECORDS value is the same as the FileMaker Pro function Status(CurrentRecordCount). The name of the layout is in the LAYOUT attribute but is empty when using XML export if you didn't choose the Format output using current layout option in the Specify Field Order for Export dialog.

The XML document created with FMPXMLRESULT can be transformed with stylesheets or other XML parsers. The COL and DATA elements are not the names of the fields, so you must understand the order of the fields in the export. The METADATA and FIELD elements are in the same order as the COL and DATA elements, so you can use these as a map of the XML data.

2.14 FMPDSORESULT vs. FMPXMLRESULT

Which grammar is the best for you to use for XML export? It may depend upon what you need to do with the exported data. The field names become the element names with FMPDSORESULT, but the FMPXMLRESULT may be more flexible without the names of the fields. Both grammars may be used for export and transformed with XSL stylesheets. Both formats can be parsed with FileMaker Pro calculations. The FMPDSORESULT will show you the field names and help you understand XML formats. Make a test export of a limited number of records and fields to help you decide whether to use FMPXMLRESULT or FMPDSORESULT.

The size of the text file exported may also determine which grammar to use. Because FMPDSORESULT uses the field names, the size of the export can grow if the field names are lengthy. FMPXMLRESULT uses "<COL><DATA></DATA></COL>" for each field, so if your field names are seven characters or less, the FMPDSORESULT may produce a smaller file size. Table 2.1 illustrates this comparison. Two fields and the same set of 100 records are used for all the exports. Export one uses the field name _col_data, and export two uses the field name serialNumber. You can see how the size of the file with FMPDSORESULT can quickly increase if you use longer field names. More fields and more records of data will increase the file size using the FMPDSORESULT.

A final argument for using FMPXMLRESULT or FMPDSORESULT may depend upon whether you will be importing this data back into a FileMaker Pro 6 database. FMPXMLRESULT is the only grammar used for XML import. If you have data exported with FMPDSORESULT, you can transform it into FMPXMLRESULT to import. The XSL stylesheet option is used to transform the elements. An example of this type of stylesheet is found in Chapter 8, section 8.2, "Transform FMPDSORESULT into FMPXMLRESULT."