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
 |
<?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>
|
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
|
<?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>
|
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
|
<?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>
|
Listing 8.30: custOrders.xml
|
<?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>
|
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
|
<?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>
|
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
|
<?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>
|
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
|
<?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>
|
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
|
<?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>
|
Listing 8.35: OrdersCustHTML.xsl
|
<?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>
|
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.
|
|
EAN: 2147483647
Pages: 100