8.7 XSLT and Web Publishing

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

start example
 <?xml version="1.0" encoding="UTF-8"?> <customers>       <customer >             <city>Monterey</city>             <name>Herbson's Pices</name>             <orders>                   <order >                   <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 >                         <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 >             <city>New York</city>             <name>A Pealing Desserts</name>                   <orders>                   <order >                         <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> 
end example

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

start example
 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 
end example

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.

  1. Customers.FP5, "Orders", ID = ::CustomerID, Orders.FP5

  2. Orders.FP5, "Customers", CustomerID = ::ID, Customers.FP5

  3. Orders.FP5, "Items", OrderID = ::OrderID, Items.FP5

  4. Items.FP5, "Customers", CustomerID = ::ID, Customers.FP5

  5. 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

start example
 <?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> 
end example

Listing 8.39: ImportOrders.xsl

start example
 <?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> 
end example

Listing 8.40: ImportCustomers.xsl

start example
 <?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> 
end example



Filemaker Pro 6 Developer's Guide to XML(s)XSL
FileMaker Pro 6 Developers Guide to XML/XSL (Wordware Library for FileMaker)
ISBN: 155622043X
EAN: 2147483647
Year: 2003
Pages: 100
Authors: Beverly Voth

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net