Driving up the California coast I encountered a beautiful and exhilarating sight: the rolling road dipped from the brilliant dawn into seemingly impenetrable fog. The sun was nearly blinding; the fog made seeing ahead difficult. So too with XML and FileMaker Pro: Moments of clarity interspersed with dense obscurity. Having taken both roads at speed, let me share some of the highlights found along the XML way, and perhaps disperse some of the fog.
By now, most readers have heard of XML, the new language of data exchange. In this article I hope to convey the following:
1. Why XML matters now
2. Nuts and bolts usage of XML and FileMaker Pro
3. Demystifying XSLT
4. Merging with Word: EZxslt as a case study for automated XSLT tools
WHY XML MATTERS NOW
This is a formative period. Microsoft is investing vast sums in.NET, and using XML as a data exchange mechanism, and has announced that the new Office 11 due in 2003 will support XML as a native data format for MS Office documents. Just this week, Sun released their OpenOffice.org XML file format specification to the standards technical committees, meaning that we might expect most office productivity applications to be able to have an XML flavor file format, suitable for creation or manipulation by other programs. Apple is using XML as the data format for many of the system preferences in Mac OS X, and many industries are beginning to create XML standards and approaches. For example, a new LegalXML standard was just proposed to help streamline electronic court filings. Indeed, XML is proliferating.
FileMaker Pro 6 offers a very nice and simple interface to create and consume XML data to help you leverage these new technologies. I believe that now is a great time to get up to speed technically, and begin to understand what can be done easily to offer better solutions to your users and customers.
In case you are wondering, XML is not just for web projects. Although Web Companion and FileMaker Pro Unlimited supports delivery of query results in XML format, there are many other ways to use XML to add value to FileMaker Pro solutions:
Create real MS Office documents without needing to support ODBC middleware, and without embedded VBA code in the office documents.
Generate static HTML, Flash, SVG and other files with FileMaker Pro data
Generate PDF documents on the fly
Build system update mechanisms for updating FileMaker Pro solutions in the field
Make use of Web Services, for example those available at serviceobjects.com
NUTS AND BOLTS USAGE OF FILEMAKER PRO 6 AND XML
FileMaker Pro 6 treats XML as a file type to be used in export or import. To create an XML file from FileMaker Pro 6, you can select EXPORT from the File menu. You may also script the process. Like any other export, the first task is to set the destination, name the new export file, and select the export type from the pop-up menu - XML in this case. Then, instead of progressing directly to the Specify Field Order for Export dialog like other export types, there is a new dialog called Specify XML and XSL Options. In this window, there is the option to select an XML grammar and optionally select an XSLT Stylesheet to be used.
FileMaker Pro 6 offers two XML grammars, or sets of XML tags used to define FMP data. Like many other database systems, there is an grammar that uses the exact field names called FMPDSORESULT. More interesting, and to its credit, FMP also offers a potentially name-independent grammar called FMPXMLRESULT. As a first step, it is useful to try to export a simple one-record found set from a simple database using both grammars and simply see what FMP produces.
Reading an XML file can be a bit unnerving at first. For simple files, and when just getting started, using Internet Explorer is fairly painless. It presents the XML file as an outline; however, you can't change the data. For that, you need to use a text editor, or an XML Editor. See http://www.filemaker.com/xml for some useful links.
The key to using FileMaker Pro with XML is to focus on small steps. If you have some data already in XML format, somehow you'll have to transform that source file into FMPXMLRESULT grammar, so that FMP will import it. If you want to get XML data out of FMP and into some other program, you will need to figure out how to transform the FileMaker Pro XML into the appropriate XML grammar of the destination program, or into a destination program's supported file format. For example, you might want to write out a text file that conforms to the RTF specifications for Rich Text Format so that you can create a Microsoft Word or other word-processing document that incorporates data from FMP.
The trickery that occurs when transforming XML from one grammer into another is performed using the XSLT language. This is the "big rug" under which much of the potentially messy complexity of XML is swept under.
If we assume we have an XSLT stylesheet that performs our transformation, it is easy to use it within FileMaker Pro 6, simply by referencing it within the Specify XML and XSL Options dialog box during an Export or Import process when using XML File Type. FileMaker Pro 6 supports locating the stylesheet, which is a file, either on local hard drives, on shared file servers, or on http servers. For workgroups, it is generally easiest to deploy on an http server because the reference to the file can be a simple URL and there are no pathing issues to worry about on the various desktops.
To recap, we know that FileMaker Pro 6 can export XML in a couple of different grammars, and import XML using the FMPXMLRESULT grammar.
Now, for import applications we somehow we need to obtain an XSLT Stylesheet that magically converts our source data into FMPXMLRESULT. For export applications we need a stylesheet to transform FMPXMLRESULT or FMPDSORESULT XML data into a target file format. We also know that setting up a process to automate this will be easy by using ScriptMaker and the Import or Export script steps.
But how do we come up with that magic XSLT document? Here is where it becomes a bit murky.
DEMYSTIFYING XSLT
OK. Hold onto your hats! Here comes a dense package of technical information. Then a gentle walk through the fog. Finally, in the last section, I'll talk about how you can avoid having to write all this XSLT by hand and still take advantage of XML by using third party tools that automate the stylesheet creation process.
XML represents data, separate from presentation or specific usage.
Usually there will be multiple XML implementations used when exchanging data. For example, between two FileMaker Pro databases, the field names or orders might be different. Between Access and FileMaker Pro there are different XML grammars. Between FileMaker Pro and Microsoft Word there is XML to RTF-Text conversion needed.
XSL is a language for expressing stylesheets. The primary component of XSL is XSLT, which is a language for transforming XML documents from one type of XML into another.
Another key component of XSL is XPath, which is an expression language used by XSLT to access or refer to various parts of an XML document.
Now, let's look at some code and see concretely what this terminology really means.
Let's start by looking at a simple FileMaker Pro 6 database called Test.fp5 consisting of just three fields: Name, Company and Email. Let's also assume there is one record in this database containing information about your humble author. If we were to export this data using a Tab Separated format, the output file might look like:
Russ Kohn <tab> Chaparral Software <tab> info@chapsoft.com
where the <tab> lable above refers to an ascii 9 tab character.
Notice that the export file has no information about the field source, the attributes about the fields etc; it is simply the data in a known order, but that order is not articulated within the export file itself.
If we perform the same output using XML Output, and select the FMPXMLRESULT grammar, the output file would look something like this:
<?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="Test.fp5" records=1" timeformat="h:mm:ss a" />
<metadata>
<field emptyok="YES" maxrepeat="1" name="Name" type="Text" />
<field emptyok="YES" maxrepeat="1" name="Company" type="Text" />
<field emptyok="YES" maxrepeat="1" name="Email" type="Text" />
</metadata>
<resultset found="1">
<row modid="3" recordid="1">
<col>
<data>
Russ Kohn
</data>
</col>
<col>
<data>
Chaparral Software
</data>
</col>
<col>
<data>
info@chapsoft.com
</data>
</col>
</row>
</resultset>
</fmpxmlresult>
Whoa! That is a lot more information. However, even without plowing through each line of code here, you can probably see that the Metadata section describes the database, and that each row of data is enclosed between a <COL><DATA> set of tags. In fact, if you never saw the original database, you'd probably be able to figure out how to create one to match this data.
Well, FMP can do that too. If you go to FMP6, and select Open... from the File menu, and then select the XML output file created above, FMP6 will automatically create a new database and populate it with the data, and name the columns appropriately.
Now, let's say you have a different version of our test database, where the fields were created in Company, Email, Name order, and where each field name was changed to start with "My". Using XSLT, a stylesheet could be written that allows the data to import smoothly without having to fiddle with the import fields dialog!
Here is a sample of what that stylesheet might look like:
<?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" exclude-result-prefixes="fmp">
<xsl:template match="/*">
<fmpxmlresult xmlns="http://www.filemaker.com/fmpxmlresult">
<errorcode>
0
</errorcode>
<product build="" name="" version="" />
<database dateformat="M/d/yyyy" layout="" name="" records="" timeformat="h:mm:ss a" />
<metadata>
<field maxrepeat="1" emptyok="YES" name="MyCompany" type="TEXT" />
<field maxrepeat="1" emptyok="YES" name="MyEmail" type="TEXT" />
<field maxrepeat="1" emptyok="YES" name="MyName" type="TEXT" />
</metadata>
<resultset>
<xsl:attribute name="FOUND">
1
</xsl:attribute>
<row recordid="0" modid="0">
<xsl:for-each select="fmp:RESULTSET/fmp:ROW">
<col>
<data>
<xsl:value-of select="fmp:COL[2]/fmp:DATA" />
</data>
</col>
<col>
<data>
<xsl:value-of select="fmp:COL[3]/fmp:DATA" />
</data>
</col>
<col>
<data>
<xsl:value-of select="fmp:COL[1]/fmp:DATA" />
</data>
</col>
</xsl:for-each>
</row>
</resultset>
</fmpxmlresult>
</xsl:template>
</xsl:stylesheet>
Here, what we have is an XSLT stylesheet design to convert one FMPXMLRESULT file (in Name, Company, Email order) into another FMPXMLRESULT file (in MyCompany, MyEmail, MyName order). Note that the prefix "fmp" was selected to differentiate tags coming from the FMPXMLRESULT grammer vs tags starting in "xsl" that come from the standard XSL Application grammar. What this stylesheet does, in essence, is create a header section that matches the FMPXMLRESULT format, and then for each column in the row, select the appropriate data.
The tags xsl:for-each, xsl:value-of, xsl:template, xsl:stylesheet etc. are standard language elements for the XSLT language. Let's look at one of the value-of statements:
<xsl:value-of select="fmp:COL[1]/fmp:DATA"/>
This says: "In the source FMP XML document, find the first COL element and then get the contents of its DATA element." If we look at the source XML and the stylesheet carefully, we can see that there is in effect a path to get from the root of the document to the DATA element we are interested in:
FMPXMLRESULT/RESULTSET/ROW/COL[x]/DATA
This XPath notation is the way XSLT allows you to address and access various sets of elements within an XML document. The above path is very useful when working with FMPXMLRESULT XML data.
So, now we've looked at how FMP generates and uses XML, and examined a simple XSLT document briefly. Clearly, even if we understand XSLT, it can be time consuming to create these stylesheets from scratch.
MERGING WITH WORD:
EZxslt AS A CASE STUDY FOR AUTOMATED XSLT TOOLS
EZxslt is the first tool that automates the creation of XSLT Stylesheets for use with FileMaker Pro data. As you can see from the sections above, it can get a bit thick when creating XSLT stylesheets from scrach, even when you understand how it all works.
EZxslt simplifies this process by allowing you to focus on creating a Microsoft Word document that looks good, and on integrating the stylesheet into your FileMaker Pro environment rather than on writing the XSLT.
Here's how it works:
1. Create a template in Microsoft Word
2. Use the highlight tool to mark the placeholders for data coming from FMP
3. Save the document as an RTF file
4. Drag and drop the RTF onto EZxslt, to create an XSLT Stylesheet
5. Use the XSLT Stylesheet from within FileMaker Pro to generate a real MS Word document incorporating data from FMP.
The advantages of this approach over export/import include:
1. Reduced deployment and support costs
2. Easy customer-level modifications, with control
3. Wider scope of application
4. Easier and faster development cycle
5. Results open in any RTF-aware application
There is a comprehensive tutorial, demo files, white papers, and more available to explain how EZxslt works in great detail at www.chapsoft.com/ezxslt
It is my belief that other tools will emerge that simplify the creation of XSLT stylesheets for various domains. I feel many developers will eventually want to know enough to craft their own stylesheets necessary and simultaneously learn about the tools that make the job faster and easier.
Meanwhile, I'm going to keep cruising down the road.
[Editor's Note: EZxslt is by far the most automated and smartest way of creating XSLT Stylesheets for regular use with FileMaker Pro 6 solutions. I highly recommend that you visit www.chapsoft.com/ezxslt
Resources
www.filemaker.com/xml - key resource for all things XML in the FileMaker Pro world
www.w3.org - the real deal for authoritative specifications on XML, XSLT etc.
xml.coverpages.org - Comprehensive collection supporting XML-related technologies
www.chapsoft.com/ezxslt - EZxslt product information, downloads and support
Learning XML, Erik T. Ray, O'Reilly & Associates, Inc.
XSLT, Doug Tidwell, O'Reilly & Associates, Inc.
The XSL Companion, Neil Bradley, Addison-Wesley/Pearson Education
About the Author
Russell Kohn is the president of Chaparral Software & Consulting Services, Inc. (www.chapsoft.com). Established in 1986, Chaparral primarily consults and develops custom data management solutions in the entertainment, higher education, and service business sectors. In addition, Chaparral markets Brushfire (database analysis) and EZxslt (automated XSLT generation) software for FileMaker Pro developers and users. Russ is also the topic committee coordinator for the FMDiSC (FileMaker Developers in Southern California) user group, a member of the FileMaker Solutions Alliance, and various other industry associations. Russ may be reached at mailto:russ@chapsoft.com.