2.4 XML Import

2.41 Setting Up for XML Import

Choose File, Import Records. The contextual menu will show four options: File, Folder, XML Source, and ODBC Source. If you select File, you may navigate to an XML file and import, but FileMaker Pro will make a "best guess" as to the file type. Even if you have the ".xml" extension on the file name, the file may be imported as tab-separated text. The exported XML file is not delimited for this kind of import. You must still specify the type of file. When you choose XML Source, you will be presented with the Specify XML and XSL Options dialog. Figure 2.6 shows this to be different from the export XML dialog as seen earlier in Figure 2.1.

Figure 2.6: Import XML dialog

You may import any XML document found on your local disks or any mounted drives on your network. As long as you can see the file on the network, you may choose it for import. The second option to import XML is to specify an HTTP request. You can select any XML file that is available through the Internet, provided you have permission to get the file. HTTP requests for FileMaker Pro web publishing are discussed in Chapter 5. If you don't know how to make an HTTP request, you may find the information in Chapter 5 useful. Usually, if you have permission to get a file, you will be given the URI to enter into the dialog. For now, ignore the stylesheet selection.

Choose File and you will be given the Open File dialog to navigate to your XML file. After you select the file, click the OK button. Remember that only XML using the FMPXMLRESULT grammar will import correctly into FileMaker Pro. If you have the correct grammar for your XML document, you will be presented with the familiar Import Field Mapping dialog as seen in Figure 2.7.

Figure 2.7: Import Field Mapping dialog

You may view the fields by "matching names" or any of the other options. If the XML file has the names of the fields the same as the importing database, the fields will match by name. You may move the fields around and select or deselect the mapping for the fields. As with other FileMaker Pro imports, you may add new records, replace the data in the current found set, or update matching records in the current found set. Click the Import button to bring the data in from the XML document. You can read more about the import options in section 11 of the FileMaker Pro Help topic "Importing data into an existing file." If your field names do not match and you want to import your XML easily, the examples in Exercise 2.5 will show you different ways to do this.

2.42 FMPXMLRESULT Import

The FMPXMLRESULT grammar is the only method of importing XML data into FileMaker Pro. The correct structure of the XML document for importing into FileMaker Pro 6 is necessary. You will get errors when you try to import XML that does not comply with the FMPXMLRESULT grammar. The error dialogs may give you a clue to what is wrong when you import XML. Export a small set of records from any database that you may be using for XML import. Select FMPXMLRESULT and study the structure of the saved XML document. Chapter 4 has more detail about this XML document structure. For many XML documents the structure rules are called Document Type Definitions. Chapter 3 discusses general DTD terms.

Here are a few warnings about importing XML into specific field types in FileMaker Pro: Repeating fields do not import correctly into FileMaker Pro 6 at this time. Only the first repeat will be imported. Related field data should be imported directly into the related child file rather than the parent file. Related fields are not available in the Import Records dialog, so you cannot make any matches for import. Container fields do not import (or export) when using XML. Global fields import once, and calculation or summary fields may be imported into noncalculated fields. Date and time data may import as text and be incorrectly formatted, such as two-digit years instead of four-digit years.

Example 1: Export, Edit the FIELD Elements, and Import

By simply editing the NAME attributes of the FIELD elements, you may be able to import the FMPXMLRESULT format directly into a new data-base. This test uses the example databases Export.FP5 and Import.FP5. The only difference between the two databases is in some of the field names. Export.FP5 has First Name and Last Name. Import.FP5 has FirstName and LastName. You can make the same test with any of your own files. Make a backup copy of any databases used in this test. Make a second copy of any file, rename it, and edit some of the field names in the Define Fields dialog.

Use the example database Export.FP5 and export the fields First Name, Last Name, City, and State. Export as FMPXMLRESULT and save the export as a script. At this time do not specify an XSL stylesheet. Look at the exported XML file in a text editor if you want to see the result. Do not make any changes to the file and close it.

Now import the XML file into Import.FP5 and use the "matching names" option. The field names FirstName and LastName in the new file do not match, as shown in Figure 2.8. If you have used your own databases, import into your second file with the changed field names. You may find the correct fields and move them to match and then import. This task isn't so difficult with just a few fields but can be complex with many fields! Continue the import or select Cancel.

Figure 2.8: Import mismatched fields

Open the XML file again in a text editor. Very carefully find the NAME attributes of the FIELD elements. The NAME="First Name" can be edited to be NAME="FirstName", for example. Change any other field names, whether you use your own databases or the examples, and save the XML document without changing anything else. Try the import again and select matching names. If you get any errors when you import, try the export again and carefully change the field names in the resulting XML document.

Example 2: Transform with a Simple Stylesheet

Changing the field names each time you want to export XML and import into a new database with different names can be time consuming if you need to perform the task multiple times. A simple XSL stylesheet can be created and used with the export or the import. The transformation takes place when you export, and the new XML will import directly. Or you can export to a file and use the XSL stylesheet with the import. As with Example 1, make a manual export and create a script to save the export options, especially the fields order. If you export only a record or two, the XML document should open easily in an XML or text editor.

Create the following XSL document in a text editor and save it as NameChange.xsl.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/
  Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult"
  exclud e-result-prefixes="fmp">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="no" />
<xsl:template match="/">
<!-- REPLACE THIS AREA -->
<xsl:copy-of select="./fmp:FMPXMLRESULT/fmp:RESULTSET" />
</FMPXMLRESULT>
</xsl:template>
</xsl:stylesheet>

In the above stylesheet you will need to paste part of your exported XML. Open the XML in a text editor and find the root element "<FMPXMLRESULT>" and the end element "</METADATA>." Copy these two elements and everything in between. Paste into the style-sheet instead of the "<!– REPLACE THIS AREA –>" line. Change the NAME attribute values for every FIELD element that will be different in your new database. Save the XSL document like the example in Listing 2.12.

Listing 2.12: NameChange.xsl

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/
  Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" 
  exclud e-result-prefixes="fmp">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="no" />
<xsl:template match="/">
<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="" TIMEFORMAT="h:mm:ss
  a"/><METADATA><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="FirstName"
  TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="LastName"
  TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="City"
  TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="State"
  TYPE="TEXT"/></METADATA><xsl:copy-of select="./fmp:FMPXMLRESULT/fmp:
  RESULTSET" /></FMPXMLRESULT>
</xsl:template>
</xsl:stylesheet>

Perform the export in the old file again and this time specify the stylesheet in the dialog, as seen in Figure 2.9. You may save the export in a script step and it might be similar to this:

ExportTransformedWithXSL
     Export Records [ Filename: "ExportTransformed.xml"; Grammar:
       "FMPXMLRESULT"; XSL (from file): "NameChange.xsl"; Export Order:
         First Name (Text), Last Name (Text), City (Text), State (Text) ]
           [ Restore export order, No dialog ]

Figure 2.9: Export XML dialog with stylesheet

The exported XML has been changed (transformed) by the XSL stylesheet. If you look at ExportTransformed.xml in a text editor, you may see what appears in Listing 2.13. The field names are correct for matching names in the new file, and the data has been directly copied from the old file.

Listing 2.13: ExportTransformed.xml

<?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=""
  TIMEFORMAT="h:mm:ss a"/><METADATA><FIELD EMPTYOK="YES" MAXREPEAT="1"
  NAME="FirstName" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1"
  NAME="LastName" 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 MODID="50"
  RECORDID="1"><COL><DATA>Beverly</DATA></COL><COL><DATA>Voth</DATA></COL>
  <COL><DATA>London</DATA></COL><COL><DATA>KY</DATA></COL></ROW>
  </RESULTSET></FMPXMLRESULT>

Import the new XML file ExportTransformed.xml into the database Import.FP5. Do not specify the stylesheet because the data has already been changed with the export. Select matching names and all of your names should match.

You can also use the stylesheet with an import. First export your records with FMPXMLRESULT, but use the script you created so that the field order is the same as in the XSL above. Do not use the style-sheet for export. Open the Import.FP5 file and select File, Import Records, XML Source. This time select the same stylesheet, Name-Change.xsl. The XML parser and the XSLT processor in FileMaker Pro 6 will transform the XML as it is imported.

Example 3: Create a Stylesheet with FileMaker Pro

Doug Rowe, of Robyte Consulting in Jacksonville, Florida, has taken this transformation concept another step. Using the FileMaker Pro Design functions, he reads the field names into a FileMaker Pro file. His demo file will change the names and save the XSL stylesheet using the Troi-File plug-in. You can use the example XSL_Import.fp5 to read in your field names and manually change the names. Remember that the order of the fields in the export from the old database must be the same as the order of the fields in the created XSL.

Figure 2.10: XLS_ImportA.fp5

2.43 Scripted XML Import

Just like the XML export, you can script the import of XML data. Take a look at the import dialog in Figure 2.11. Compare this to Figure 2.6. The scripted XML import contains one more option that is not available with the manual XML import. With the scripted import, you can specify a field to contain the path to a file for import or the path for an HTTP request to import.

Figure 2.11: Scripted Import XML dialog

If the file or HTTP request returns the FMPXMLRESULT grammar, you can import directly and use the Import Field Mapping dialog, as seen in Figure 2.7. If the file or HTTP request is not the FMPXMLRESULT grammar, you can specify an XSL stylesheet by file, HTTP request, or a field with the file path or HTTP request.

The import script ImportPlain is shown below. The options are shown in the printed script:

ImportPlain
      Import Records [ XML (from file): "ExportPlain.xml"; Import
        Order: First Name (Text), Last Name (Text), City (Text), State
        (Text), Text (Text), Number (Number), Date (Date), Time (Time) ]
          [ Restore import order ]

2.44 FileMaker Pro XML Import and Other XML Schemas

The structure of your XML documents may not match the FMPXMLRESULT grammar. An example XML document is shown here:

Listing 2.14: Sample XML with multiple levels

<?xml version="1.0" encoding="UTF-8" ?>
<customers>
   <customer id="123">
      <name>Joe Brown</name>
      <invoices>
      <invoice id="987">
      <date>11/12/1997</date>
         <total>25.75</total>
         <items>
            <item id="1">
               <qty>3</qty>
               <description>Trucks</description>
               <color>Blue</color>
               <price>5.15</price>
            </item>
            <item id="2">
               <qty>2</qty>
               <description>Trucks</description>
               <color>Red</color>
               <price>5.15</price>
            </item>
         </items>
      </invoice>
      <invoice id="859">
      <date>12/05/1997</date>
         <total>4.00</total>
         <items>
            <item id="3">
               <qty>1</qty>
               <description>Cars</description>
               <color>Blue</color>
               <price>4.00</price>
            </item>
         </items>
      </invoice>
      </invoices>
   </customer>
<customer id="352">
      ....
   </customer>
</customers>

If you study the example in Listing 2.14, you'll see that the root element is <customers>. If you were to design FileMaker Pro databases for this information, you might create the file ORDERS.FP5. You could design the file to be "flat" and contain the smallest piece of information (the element <item>) to be one record per item. Each record might contain these fields: customerID, customerName, invoiceID, invoiceDate, invoiceTotal, itemID, itemQty, itemDescription, itemColor, and itemPrice. You would need to retrieve the information for customerID and customerName for each invoice and for each item in each invoice. The three items ordered by customer name "Joe Brown" would be the three records in this hypothetical flat file:

A flat database file such as the example above may be sufficient for a small set of data. But you can see that data is duplicated unnecessarily. The XML document shows the data in the tree structure. Only the necessary information is available. By design, the child elements inherit the parent's information. Even though the document is "flat", it really contains relational data. The next example places the related XML information where needed.

You could create three related files: CUSTOMERS.FP5, INVOICES.FP5, and ITEMS.FP5. The relationship match field customerID would be in all three files. The relationship match field invoiceID would be in the INVOICES and ITEMS files. Table 2.3 shows the databases and the fields in each file:

The XSL stylesheets for importing the XML shown in Listing 2.14 into FileMaker Pro 6 will be presented in Chapter 7. For now, these examples illustrate the XML that you may encounter and thoughts on designing the databases for importing XML from other sources. Study the structure of XML documents and find the patterns of data. Some data may be in elements and some data may be in attributes, such as customerID, invoiceID, and itemID. Elements that repeat within an XML document may be good candidates for separate databases and individual records.