8.6 Import XML into Related Databases

You may need to import an XML document into FileMaker Pro and the structure dictates the need for multiple databases, multiple stylesheets, and scripting to call the import routines to accomplish this. Go back and review Chapters 3 and 4 for more information about DTDs, schemas, and grammars. By understanding the structure of your document, you will know what elements will be used for import into any one database. Sometimes the data in an element will be imported into more than one database. Any field used as a relationship key may be shown once in the XML document but occurs in several databases. As a general rule, any element that repeats within another element probably is a good candidate for import into a related database. This section uses the Customers, Orders, and Items databases to import a single XML document.

8.61 The XML Source

The following listing is the document Orders.xml.

Listing 8.36: Orders.xml

<?xml version="1.0" encoding="UTF-8"?>
<customers>
      <customer ID="1">
            <city>Monterey</city>
            <name>Herbson's Pices</name>
            <orders>
                  <order ID="ORD2">
                  <num>1</num>
                  <date>12-01-2002</date>
                  <amount>23.54</amount>
                        <items>
                              <item>
                                    <productID>ABC123</productID>
                                    <quantity>1</quantity>
                                    <description>Oregano</description>
                                    <price>23.54</price>
                                    <extended>23.54</extended>
                              </item>
                        </items>
                  </order>
                  <order ID="ORD3">
                        <num>2</num>
                        <date>01-06-2003</date>
                        <amount>15.45</amount>
                        <items>
                              <item>
                                    <productID>23_45d</productID>
                                    <quantity>2</quantity>
                                    <description>Rosemary</description>
                                    <price>5.00</price>
                                    <extended>10.00</extended>
                              </item>
                              <item>
                                    <productID>t456</productID>
                                    <quantity>5</quantity>
                                    <description>Thyme</description>
                                    <price>1.09</price>
                                    <extended>5.45</extended>
                              </item>
                        </items>
                  </order>
            </orders>
      </customer>
      <customer ID="2">
            <city>New York</city>
            <name>A Pealing Desserts</name>
                  <orders>
                  <order ID="ORD4">
                        <num>1</num>
                        <date>11-15-2002</date>
                        <amount>115.00</amount>
                        <items>
                              <item>
                                    <productID>ABC123</productID>
                                    <quantity>5</quantity>
                                    <description>Lemon Zests</description>
                                    <price>23.00</price>
                                    <extended>115.00</extended>
                              </item>
                        </items>
                  </order>
            </orders>
      </customer>
</customers>

8.62 The Databases

The example FileMaker Pro databases we used for exporting in section 8.5 are used here for importing the XML source shown in Listing 8.36. Each of the databases is described here, including field names, relationships, and import scripts. The field names do not match the element names in the XML source. To help create the XSLT stylesheets, you can make a simple FMPXMLRESULT export from each of these databases.

Customers.FP5 fields: ID (number), Name (text), City (text)
Orders.FP5 fields: OrderID (number), TotalAmt (number), OrderDate (date), CustomerID (number)
Items.FP5 fields: CustomerID (number), OrderID (text), ProductID (text), Qty (number), Description (text), Price (number), cExtended (calculation, number = Qty * Price), ItemID (number)

Scripts (File Name, Script Name). These are the import scripts in each database. They are performed by a single script in the Items.FP5 database. The printed scripts don't show that all imports were performed manually with "matching names" and the criteria saved in the scripts. You also don't see that the ImportCustomers script uses the ID field as a match field and the import action uses the Update matching records in the current found set and Add remaining records options. When importing, we can be reasonably sure that the Orders and Items are new records to be created. We might already have the customer record and only need to update or add with the XML import.

Listing 8.37: Scripts

1. Customers.FP5, ImportCustomers
  Show All Records
  Import Records [ XML (from file): "Orders.xml"; XSL (from file):
  "ImportCustomers.xsl"; Import Order: ID (Number), Name (Text),
  City (Text) ] [ Restore import order, No dialog ]
2. Orders.FP5, ImportOrders
  Import Records [ XML (from file): "Orders.xml"; XSL (from file):
  "ImportOrders.xsl"; Import Order: CustomerID (Number), OrderID
      (Number), OrderDate (Date), TotalAmt (Number) ] [ Restore import
      order, No dialog ]
3. Items.FP5, ImportItems
  Import Records [ XML (from file): "Orders.xml"; XSL (from file):
  "ImportOrders.xsl"; Import Order: CustomerID (Number), OrderID (Text),
  ItemID (Number), Qty (Number), ProductID (Text), Description (Text),
    Price (Number) ]
   [ Restore import order, No dialog ]
4. Items.FP5, Imports
  Perform Script [ "ImportItems" ]
   [ Sub-scripts ]
  Perform Script [ Filename: "Orders.FP5", "ImportOrders" ]
   [ Sub-scripts ]
  Perform Script [ Filename: "Customers.FP5", "ImportCustomers" ]
   [ Sub-scripts ]
  Exit Script

Relationships (File Name, Relationship Name, Relationship, Related File). These relationships are not used with FileMaker Pro 6 XML import. You cannot select a related field in the import dialog.

Customers.FP5, "Orders", ID = ::CustomerID, Orders.FP5
Orders.FP5, "Customers", CustomerID = ::ID, Customers.FP5
Orders.FP5, "Items", OrderID = ::OrderID, Items.FP5
Items.FP5, "Customers", CustomerID = ::ID, Customers.FP5
Items.FP5, "Orders", OrderID = ::OrderID, Orders.FP5

8.63 The XSLT Stylesheets

The following stylesheets were created from the basic XML imports from each database. The field names and field order were used by placing the XSL elements in the same order. Look at the stylesheet for importing the items and see where the XPath uses the "../" (go to parent) notatation to walk back up the XML source tree to get CustomerID and OrderID information.

Listing 8.38: ImportItems.xsl

<?xml version='1.0' encoding='UTF-8' ?>
<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/
  Transform'>
      <xsl:output version='1.0' encoding='UTF-8' indent='no'
        method='xml' />
      <xsl:template match='/'>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
  <ERRORCODE>0</ERRORCODE><PRODUCT BUILD="11/13/2002" NAME="FileMaker Pro"
  VERSION="6.0v4"/><DATABASE DATEFORMAT="M/d/yyyy" LAYOUT=""
  NAME="Items.FP5" RECORDS="" TIMEFORMAT="h:mm:ss a"/><METADATA><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="CustomerID" TYPE="NUMBER"/><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="OrderID" TYPE="TEXT"/><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="ItemID" TYPE="NUMBER"/><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="Qty" TYPE="NUMBER"/><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="ProductID" TYPE="TEXT"/><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="Description" TYPE="TEXT"/><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="Price"
  TYPE="NUMBER"/></METADATA><RESULTSET FOUND="">
            <xsl:for-each select="./customers/customer/orders/order/
            items/item">
                  <ROW MODID="" RECORDID="">
                        <COL><DATA><xsl:value-of select="../../
                          ../../@ID" /></DATA></COL>
                        <COL><DATA><xsl:value-of select="../../
                          @ID" /></DATA></COL>
                        <COL><DATA></DATA></COL>
                        <COL><DATA><xsl:value-of select="./
                          quantity" /></DATA></COL>
                        <COL><DATA><xsl:value-of select="./
                          productID" /></DATA></COL>
                        <COL><DATA><xsl:value-of select="./
                          description" /></DATA></COL>
                        <COL><DATA><xsl:value-of select="./
                          price" /></DATA></COL>
                  </ROW>
            </xsl:for-each>
</RESULTSET></FMPXMLRESULT>
      </xsl:template>
</xsl:stylesheet>

Listing 8.39: ImportOrders.xsl

<?xml version='1.0' encoding='UTF-8' ?>
<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/
  XSL/Transform'>
      <xsl:output version='1.0' encoding='UTF-8' indent='no'
        method='xml' />
      <xsl:template match='/'>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
  <ERRORCODE>0</ERRORCODE><PRODUCT BUILD="11/13/2002" NAME="FileMaker Pro"
  VERSION="6.0v4"/><DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME=
  "Orders.FP5" RECORDS="" TIMEFORMAT="h:mm:ss a"/><METADATA><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="CustomerID" TYPE="NUMBER"/><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="OrderID" TYPE="NUMBER"/><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="OrderDate" TYPE="DATE"/><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="TotalAmt" TYPE="NUMBER"/></METADATA>
  <RESULTSET FOUND="">
        <xsl:for-each select="./customers/customer/orders/order">
              <ROW MODID="" RECORDID="">
                      <COL><DATA><xsl:value-of select="../../
                         @ID" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="@ID"
                         /></DATA></COL>
                      <COL><DATA><xsl:value-of select=
                         "./date" /></DATA></COL>
                      <COL><DATA><xsl:value-of select=
                         "./amount" /></DATA></COL>
              </ROW>
        </xsl:for-each>
</RESULTSET></FMPXMLRESULT>
      </xsl:template>
</xsl:stylesheet>

Listing 8.40: ImportCustomers.xsl

<?xml version='1.0' encoding='UTF-8' ?>
<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/
  Transform'>
      <xsl:output version='1.0' encoding='UTF-8' indent='no'
        method='xml' />
      <xsl:template match='/'>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
  <ERRORCODE>0</ERRORCODE><PRODUCT BUILD="11/13/2002" NAME="FileMaker Pro"
  VERSION="6.0v4"/><DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME=
  "Customers.FP5" RECORDS="" TIMEFORMAT="h:mm:ss a"/><METADATA><FIELD
  EMPTYOK="YES" MAXREPEAT="1" NAME="ID" TYPE="NUMBER"/><FIELD EMPTYOK="YES"
  MAXREPEAT="1" NAME="Name" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1"
  NAME="City" TYPE="TEXT"/></METADATA><RESULTSET FOUND="">
            <xsl:for-each select="./customers/customer">
                  <ROW MODID="" RECORDID="">
                        <COL><DATA><xsl:value-of select="@ID"
                          /></DATA></COL>
                        <COL><DATA><xsl:value-of select=
                          "./name" /></DATA></COL>
                        <COL><DATA><xsl:value-of select=
                          "./city" /></DATA></COL>
                  </ROW>
            </xsl:for-each>
</RESULTSET></FMPXMLRESULT>
      </xsl:template>
</xsl:stylesheet>