8.6 Import XML into Related Databases

8.5 Export XML from Related Databases

It's fairly easy to pick related fields one relationship away and use them in your XML export. You may use FMPDSORESULT or FMPXMLRESULT to export related fields. See section 2.22, "XML from FileMaker Pro Related Fields," for the structure of each of these types of XML documents.

Walking the XML tree to get the <DATA> in each of the fields is not as easy. The XPath function position() can return a number relating to the child order of an element. The first <DATA> element in the <COL> element of a related field is "position() = 1" when you export with FMPXMLRESULT. The only difference of the <DATA> element in the FMPDSORESULT is that the name of the element is the name of the related field (including the relationship name). The first <DATA> element is still at "position() = 1". We will use this XPath function in our XSLT stylesheet to allow us to get each of the field contents in each of the portal rows.

Creating an XML document with data more than one relationship away is much more difficult. If you have a CUSTOMERS database and related ORDERS database, you may also have a related ITEMS database with all of the order items. A FileMaker Pro export from CUSTOMERS can yield the ORDERS fields in an XML export, but not the ITEMS fields. A FileMaker Pro export from the ORDERS database can get the CUSTOMERS information, but it will be repeated for every record/ROW in the found set.

Note 

Indentation has been added for clarity in these examples. The actual export is not formatted this way.

8.51 Export as FMPDSORESULT

Step 1: Simple Export

Use the databases Customers.FP5 and Orders.FP5 for this exercise.

The script ExportCustomers in Customers.FP5 has a simple export of the Customer data, as seen in the listing below.

Listing 8.27: customers.xml

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

The stylesheet customers.xsl is shown in Listing 8.28. It's a simple stylesheet that converts the FMPDSORESULT into a slightly different XML format. The field ID needed to be placed as an attribute for the element <customer>. The other two fields are just placed within literal elements. The names of the fields (names of the elements) could have been used with <xsl:element> to create the element.

Listing 8.28: customers.xsl

start example
 <?xml version='1.0' encoding='UTF-8' ?> <xsl:stylesheet version='1.0'   xmlns:xsl='http://www.w3.org/1999/XSL/Transform'   xmlns:fm="http://www.filemaker.com/fmpdsoresult"   exclude-result-prefixes="fm">       <xsl:output version='1.0' encoding='UTF-8' indent='yes'         method='xml' />       <xsl:template match="/">       <customers>             <xsl:for-each select="fm:FMPDSORESULT/fm:ROW">                   <customer>                         <xsl:attribute name="ID"><xsl:value-of                           select="./fm:ID" /></xsl:attribute>                         <name><xsl:value-of select="./fm:Name" /></name>                         <city><xsl:value-of select="./fm:City" /></city>                   </customer>             </xsl:for-each>       </customers>       </xsl:template> </xsl:stylesheet> 
end example

Step 2: Export with Related Fields

The simple export in Step 1 does not contain any related fields or data. We revised the stylesheet to include the orders as a list with the order ID as an attribute. Listing 8.29 shows the revised stylesheet and Listing 8.30 shows the new XML document. The script to create the document is ExportCustOrders in Customers.FP5.

Listing 8.29: custOrders.xsl

start example
 <?xml version='1.0' encoding='UTF-8' ?> <xsl:stylesheet version='1.0'   xmlns:xsl='http://www.w3.org/1999/XSL/Transform'   xmlns:fm="http://www.filemaker.com/fmpdsoresult"   exclude-result-prefixes="fm">       <xsl:output version='1.0' encoding='UTF-8' indent='yes'         method='xml' />       <xsl:template match="/">       <customers>             <xsl:for-each select="fm:FMPDSORESULT/fm:ROW">                   <customer>                         <xsl:attribute name="ID"><xsl:value-of                           select="./fm:ID" /></xsl:attribute>                         <name><xsl:value-of select="./fm:Name" /></name>                         <city><xsl:value-of select="./fm:City" /></city>                         <orders>                               <xsl:for-each select="./fm:ID_Orders_                                 CustomerID.OrderID/fm:DATA">                               <order>                                     <xsl:attribute name="ID"><xsl:value-of                                       select="." /></xsl:attribute>                               </order>                               </xsl:for-each>                         </orders>                   </customer>             </xsl:for-each>       </customers>       </xsl:template> </xsl:stylesheet> 
end example

Listing 8.30: custOrders.xml

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

Step 3: Adding Other Related Fields

With the help of the XPath function position() we can set a variable to number the orders and also to use when getting the sibling <DATA> values. The ExportOrdersCust script in Customers.FP5 creates the XML in Listing 8.31.

Listing 8.31: OrdersCust.xml

start example
 <?xml version="1.0" encoding="UTF-8"?> <customers>       <customer >             <name>Herbson's Pices</name>             <city>Monterey</city>             <orders>                   <order >                         <num>1</num>                         <date>12-01-2002</date>                         <amount>23.54</amount>                   </order>                   <order >                         <num>2</num>                         <date>01-06-2003</date>                         <amount>15.45</amount>                   </order>             </orders>       </customer>       <customer >             <name>A Pealing Desserts</name>             <city>New York</city>             <orders>                   <order >                         <num>1</num>                         <date>11-15-2002</date>                         <amount>115.00</amount>                   </order>             </orders>       </customer> </customers> 
end example

The stylesheet OrdersCust.xsl has a few changes to get the other related fields, as seen here:

 <order>       <xsl:attribute name="ID"><xsl:value-of select="." /></xsl:attribute>       <num><xsl:value-of select="position()" /></num>       <date><xsl:value-of select="../../fm:ID_Orders_CustomerID.OrderDate/         fm:DATA[position() = $recNum]" /></date>       <amount><xsl:value-of select="../../fm:ID_Orders_CustomerID.TotalAmt/         fm:DATA[position() = $recNum]" /></amount> </order> 

The "../" expression in the code above is the XPath shortcut for "parent::". When you are on the first field in the first portal row, the path to the next field in that row is back up the tree to the grandparent and back down to the related field name and <DATA> element. If we did not specify the predicate for that element, you would get the first field in every portal row! The full XSLT stylesheet is shown in Listing 8.32.

Listing 8.32: OrdersCust.xsl

start example
 <?xml version='1.0' encoding='UTF-8' ?> <xsl:stylesheet version='1.0'   xmlns:xsl='http://www.w3.org/1999/XSL/Transform'   xmlns:fm="http://www.filemaker.com/fmpdsoresult"   exclude-result-prefixes="fm">       <xsl:output version='1.0' encoding='UTF-8' indent='yes'         method='xml' />       <xsl:template match="/">       <customers>             <xsl:for-each select="fm:FMPDSORESULT/fm:ROW">                   <customer>                         <xsl:attribute name="ID"><xsl:value-of select=".                           /fm:ID" /></xsl:attribute>                         <name><xsl:value-of select="./fm:Name" /></name>                         <city><xsl:value-of select="./fm:City" /></city>                         <orders>                               <xsl:for-each select="./fm:ID_Orders_                                 CustomerID.OrderID/fm:DATA">                               <xsl:variable name="recNum"><xsl:value-of                                 select="position()" /></xsl:variable>                               <order>                                     <xsl:attribute name="ID"><xsl:value-of                                         select="." /></xsl:attribute>                                     <num><xsl:value-of select="position()"                                       /></num>                                     <date><xsl:value-of select="../../                                       fm:ID_Orders_CustomerID.OrderDate/                                       fm:DATA[position() = $recNum]"                                       /></date>                                     <amount><xsl:value-of select="../../                                       fm:ID_Orders_CustomerID.TotalAmt/                                       fm:DATA[position() = $recNum]"                                       /></amount>                               </order>                               </xsl:for-each>                         </orders>                   </customer>             </xsl:for-each>       </customers>       </xsl:template> </xsl:stylesheet> 
end example

8.52 Export as FMPXMLRESULT

Similar XSLT can be used to transform related fields when you export as FMPXMLRESULT. The stylesheet OrdersCustXML.xsl is shown here. The results are the same as in Listing 8.31, OrdersCust.xml, but are called OrdersCustXML.xml. Compare the stylesheet in Listing 8.32 with this stylesheet.

Listing 8.33: OrdersCustXML.xsl

start example
 <?xml version='1.0' encoding='UTF-8' ?> <xsl:stylesheet version='1.0'   xmlns:xsl='http://www.w3.org/1999/XSL/Transform'   xmlns:fm="http://www.filemaker.com/fmpxmlresult"   exclude-result-prefixes="fm">       <xsl:output version='1.0' encoding='UTF-8' indent='yes'         method='xml' />       <xsl:template match="/">       <customers>             <xsl:for-each select="fm:FMPXMLRESULT/fm:RESULTSET/fm:ROW">                   <customer>                         <xsl:attribute name="ID"><xsl:value-of select=                           "./fm:COL[1]/fm:DATA" /></xsl:attribute>                         <name><xsl:value-of select="./fm:COL[2]/fm:DATA"                           /></name>                         <city><xsl:value-of select="./fm:COL[3]/fm:DATA"                           /></city>                         <orders>                               <xsl:for-each select="./fm:COL[4]/fm:DATA">                               <xsl:variable name="recNum"><xsl:value-of                                 select="position()" /></xsl:variable>                               <order>                                     <xsl:attribute name="ID"><xsl:value-of                                       select="." /></xsl:attribute>                                     <num><xsl:value-of select="position()"                                       /></num>                                     <date><xsl:value-of select="../../                                       fm:COL[5]/fm:DATA[position() =                                       $recNum]" /></date>                                     <amount><xsl:value-of select="../../                                       fm:COL[6]/fm:DATA[position() =                                       $recNum]" /></amount>                               </order>                               </xsl:for-each>                         </orders>                   </customer>             </xsl:for-each>       </customers>       </xsl:template> </xsl:stylesheet> 
end example

8.53 Export to HTML

A stylesheet to create an HTML document can use the same principles shown in the previous two sections. Listing 8.34 shows the creation of a simple table. All orders are in a single row along with the customer information.

Listing 8.34: OrdersCustHTML.htm

start example
 <?xml version="1.0" encoding="UTF-8"?> <html> <head> <title>Customers</title> </head> <body> <table border="1"> <tr> <td>1</td> <td>Monterey</td> <td>Herbson's Pices</td> <td>ORD2</td> <td>1</td> <td>12-01-2002</td> <td>23.54</td> <td>ORD3</td> <td>2</td> <td>01-06-2003</td> <td>15.45</td> </tr> <tr> <td>2</td> <td>New York</td> <td>A Pealing Desserts</td> <td>ORD4</td> <td>1</td> <td>11-15-2002</td> <td>115.00</td> </tr> </table> </body> </html> 
end example

Listing 8.35: OrdersCustHTML.xsl

start example
 <?xml version='1.0' encoding='UTF-8' ?> <xsl:stylesheet version='1.0'   xmlns:xsl='http://www.w3.org/1999/XSL/Transform'   xmlns:fm="http://www.filemaker.com/fmpxmlresult"   exclude-result-prefixes="fm">       <xsl:output version='1.0' encoding='UTF-8' indent='yes'         method='xml' />       <xsl:template match="/">       <html>       <head><title>Customers</title></head>       <body>       <table border="1">             <xsl:for-each select="fm:FMPXMLRESULT/fm:RESULTSET/fm:ROW">                   <tr>                         <td><xsl:value-of select="./fm:COL[1]/fm:DATA"                           /></td>                         <td><xsl:value-of select="./fm:COL[2]/fm:DATA"                           /></td>                         <td><xsl:value-of select="./fm:COL[3]/fm:DATA"                           /></td>                               <xsl:for-each select="./fm:COL[4]/fm:DATA">                               <xsl:variable name="recNum"><xsl:value-of select="position()" /></xsl:variable>                               <td><xsl:value-of select="." /></td>                                     <td><xsl:value-of select="position()"                                       /></td>                                     <td><xsl:value-of select="../../                                       fm:COL[5]/fm:DATA[position() =                                       $recNum]" /></td>                                     <td><xsl:value-of select="../../                                       fm:COL[6]/fm:DATA[position() =                                       $recNum]" /></td>                               </xsl:for-each>                   </tr>             </xsl:for-each>       </table>       </body>       </html>       </xsl:template> </xsl:stylesheet> 
end example

Challenge: Using the above XSL and the example "Hidden Portal Trick" found in the XSLT library, http://www.filemaker.com/xml/xslt_library.html, create an HTML page to show the customer information, followed by the related information in tables. Use <xsl:if> to show or not show the table, depending upon a record having related data.



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