XML Import: Understanding Web Services

In addition to FileMakers capability to export data via XML, either with or without an XSL stylesheet, FileMaker also has the capability to work with remote XML data sources, often referred to under the umbrella term "Web services." This is a capability that was added in FileMaker version 6 and is a significant addition to FileMakers XML strengths. Using this capability, you can bring data from a variety of remote data sources directly into FileMaker, as well discuss in the sections that follow.

FileMakers XML Import Capability

The concept of XML exporting ought to seem fairly straightforward: Take some FileMaker data, pick an XML grammar for the export, and optionally apply an XSL stylesheet to transform the XML data as it heads out. But what about the concept of importing XML? What does this mean, and what is it good for?

Stated simply, FileMaker can import any XML data that conforms to the FMPXML grammar. FileMaker reads the section of the document to determine the field structure, and reads the individual row and column data to figure out the actual data values that should be imported.

To demonstrate this for yourself, find some suitable FileMaker data and export it as XML, using the FMPXMLRESULT grammar, without applying any XSL stylesheet to it. Starting from the same file and table, go back and re-import the file you just exported, treating it as an XML data sourceyoull see that FileMaker correctly reads the field structure and data from the XML document. Or, to test it in a different way, drag the new XML file onto the FileMaker application icon to open it. FileMaker should, without intervention from you (except for choosing a filename for the new file), open the XML file, read its structure, and create a new FileMaker file with a new table containing the correct fields, field types, and data values.

Note

One of the things developers have often wanted from FileMaker is a way to save a files field structure as a text document, and then use that text document to move the field structure somewhere else and re-create it. The capability to open an XML document and have it create a new FileMaker file might seem to make that possible, but there are caveats. The XML export doesn preserve important information about your field structure, such as the definitions of calculation fields and summary fields. In the XML output, these fields are treated simply as their underlying data types, so a calculation that produces a number is treated in the XML metadata as a simple number field, without preserving the calculations definition.

So, FileMaker can import any XML data file that conforms to the FMPXMLRESULT grammar. Additionally, as you might have seen, this XML data stream can come from a local file, or it can come from a file available over HTTPin other words, a file from somewhere on the Web. This is where things get interesting, so lets delve further into the concept of a Web service.

Note

As was the case with using remote stylesheets for XML export, FileMaker is also unable to work with data from an HTTPS data source when importing XML. If the data source from which you want to import is available only over secure HTTP, FileMaker isn able to import it.

Web Services Reviewed

We started this chapter by saying that Web services was a term referring to the sharing of data between computers via the Webs HTTP protocol, and that the data was often exchanged in XML format. Imagine you have two computer systems that need to exchange data. One is a large student information system that resides on a mainframe computer. The other is a system that generates complex forms for each student, to conform to governmental guidelines. Periodically, the forms application needs to consult the mainframe application to see whether any new students have been added so that those students are accounted for in the forms system.

There are many ways to make this kind of sharing happen. The mainframe programmer could export a file of new students every night, in some plain-text format, and the forms programmers could write routines to grab the file and process it in some way. Or the mainframe could be made accessible via a technology such as ODBC, and the forms application could be configured to make ODBC requests to the mainframe.

Another option, though, is to make it possible to send queries to the mainframe via HTTP, and get XML back in response. This is simpler than either of the previous scenarios: It doesn require any complicated processes such as writing and then fetching an actual file, nor does it involve the client-side complexities of ODBC transactions. It uses the widely (almost universally) available HTTP protocol, and requires only that one side be able to generate a form of XML and the other side be able to read it. Don get us wrongWeb services transactions can still be plenty complicated, but standards such as XML and HTTP make them less complex than they might be otherwise. Refer to Figure 22.1 for a sketch of a possible Web services transaction.

Lets say that, in our example, the forms application was written in FileMaker. And lets assume the mainframe student information system was accessible as a Web service, meaning that you could send a request via HTTP and get back a listing of new students in some XML format (that would likely not conform to the FMPXMLRESULT grammar). To import that data into FileMaker, you could perform an XML import, use the URL of the student information system as the data source, and apply an XSL stylesheet that would transform the new student XML into FMPXMLRESULT. The concept is sketched out in Figure 22.7. To retrieve student data from this mainframe, make a request to a URL thats able to produce an XML representation of the student, and then bring that XML back through a stylesheet into FileMaker.

A Stylesheet for XML Import

For the sake of argument, assume that we have an XML data stream representing new students. (We use the term "data stream" rather than "file" as a reminder that the data need not come from a file, but can also come from a networked data source over HTTP.) Listing 22.6 shows what that data might look like.

Listing 22.6. Sample XML File Containing Data

Its a simple enough structure, consisting of a filled with one or more elements, where each has a number of fields associated with it. The only wrinkle has to do with parent information: Clearly a student can have more than one parent, so each student contains a

element with one or more

elements inside it. Well have to think about what to do with that.

Thats the XML file that the hypothetical data source can put out. But remember, for FileMaker to import this XML data, it has to be structured in the FMPXMLRESULT format. Such a structure would look Listing 22.7.

Listing 22.7. Data in the Importable FMPXMLRESULT Format

Web services scattered through the ether are unlikely to emit XML that conforms to the FMPXMLRESULT grammar. So before bringing that data into FileMaker, we need to transform it into FMPXMLRESULT. And the tool for doing that is, of course, an XSL stylesheet. This is exactly the reason FileMaker lets you apply a stylesheet to inbound XML (in other words, on import). Odds are that the XML data source does not produce the FMPXMLRESULT grammar directly, so its our job to translate the source XML into the form that FileMaker can read.

We need an XSL stylesheet to make that transformation. The stylesheet needs to make sure to output all the initial information found in an FMPXMLRESULT file, such as database name, and all the metadata describing the field structure. Then, in the context of a , we need to output the actual student data.

Listing 22.8 shows what the stylesheet for transforming student data prior to importing it into FileMaker should look like.

Listing 22.8. An XSL Stylesheet

We won spend as much time dissecting this stylesheet as we did with the last one. The mechanics should be pretty easy to discern. After the usual declarations, we declare a template that matches to the source documents element. (Its the root element, so there will be only one.) Thats the occasion to output all the header-type information particular to the FMPXMLRESULT grammar, including the field structure metadata. We then go on to output a ... tag pair, and do some more work inside that.

Within the tags, we use an to loop over all the elements inside the . For each one, we output the corresponding element. Each , in turn, is a collection of ... tag pairs. We output one of these for each inbound field, and insert the correct data into it, using .

The only thing at all noteworthy is the treatment of the parent information. The inbound student information is not completely "flat." The nested

element almost implies a new table, in relational database terms. We could choose to handle it that way, and bring the parent information into a separate table, but we chose instead to flatten the parent data into a single field. This was more to illustrate a particular technique than because its actually a good idea to do that. Whether it really is a good idea depends on the application.

In any case, the technique here is to loop over the individual

elements, using . For each parent, we output the first name, last name, and the family relationship in parentheses. You might notice that we use the command liberally, to output the spaces between words and the parentheses around the relationship. The reason for this is that XML treats certain characters, such as white space, specially. White space, in particular, XML ignores. Wrapping it in an tag ensures that the processor treats it as real white space and outputs it as such.

The last wrinkle here is that we want the parent list to be comma separated. So we write a little piece of logic that requests that a comma and its following white space be output, but only if the current

element is not the last one in the group. The check is performed with .

As you can see, the stylesheet isn too complicated. The hardest part is getting all the FMPXMLRESULT-specific elements and attributes correctly included.

Tip

Its irritating, if not impossible, to remember all the specifications for the FMPXMLRESULT grammar every time you need to write a new import stylesheet. To save yourself the trouble, first make sure that the FileMaker table you e using to receive the data is correctly built and has the right structure. Then add a sample record or two to the table, and export the table as FMPXMLRESULT. The result is exactly what any inbound XML needs to look like (well, the data itself is likely to be different!). You should be able to copy large chunks of this XML output and paste them into your stylesheet to get yourself started.

After youve written the stylesheet, you would apply it in the course of the import. If everything goes smoothly, the stylesheet is successfully applied, it emits pure FMPXML, and this is cleanly imported into FileMaker.

You might need to do a bit of work to make sure that the fields line up correctly on import. The easiest way to ensure this is to write your XSL stylesheet in such a way that the field names in the resulting section of the XML are exact matches for your FileMaker field names. If thats the case, you need to specify only that fields should import based on matching names. If for any reason theres a discrepancy between the field names that are used in the resultant FMPXMLRESULT and the field names in the target table, you have to specify the import matching by hand.