In part 1 of this series, I described the XML format of the Database Design Report, or DDR. If you haven’t read that yet, go back and have another look as this section will assume you’ve read that and understand the basics of the XML format. If you understand the concepts of nodes and heirarchy of nodes in the DDR, then you’re ready to move to the next step.
Why can’t I just import the DDR directly into FileMaker?
XML just doesn’t work that way. What XML does instead is allow every single application that supports XML to do so in their own special way – but all still as a standard XML document – and then add a method to move from one XML document to another very simply. So even though each format is different, each one is following certain rules and is consistent, so that I can move from any one format to any other format with relative ease. Once I’ve programmed a step from one format to another, I can apply that process many times for different data sets and it will always work.
XML is actually used in lots of different places, for example you can go directly from a FileMaker export to PDF, or to an SVG graphic. Or to RTF documents, or many other database formats. All from within FileMaker directly. This is the benefit of XML.
The process to move from one XML format to another is called XSLT, but we won’t look at that until part 3.
What does FileMaker’s XML format look like?
This is easy to answer. Take any filemaker file you’ve got with a few records and a few fields, and export it to XML with no XSLT file selected, then open the file in a text editor.
I’m going to go through this in much the same way as I did before, by opening the XML out a few steps at a time.
Take a lok at the code below. First of all we can see that there is a inital comment line. This is actually called the “XML Declaration” but for our purposes for the moment we can ignore it.
Then the second line is the start of the data, and contains the first node ( called the root node ).
<?xml version="1.0"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
snip
</FMPXMLRESULT>
The root node, in this case contains one attribute with the name of xmlns. This is an XML namespace, and although it’s important, it’s beyond the scope of what we’re doing here. For our purposes we’ll just leave these things intact. One of the big benefits of this all being in text format documents is that you can take someone else’s starting point and just modify it to suit your needs and continue on your merry way.
At first glance, and with only a small amount of the data showing, this appears similar to the DDR. Opening it up one step further we get :
<?xml version="1.0"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<ERRORCODE>0</ERRORCODE>
<PRODUCT BUILD="" NAME="" VERSION="" />
<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="23" TIMEFORMAT="h:mm:ss a" />
snip
</FMPXMLRESULT>
So you can see all of these three new nodes are either self closing nodes, or like the first ERRORCODE node, closed already on the same line. They are just all places holders for information about the file itself. This section includes information about what version of FileMaker the file was created with, layout data and even date and time formats. Of course, you may not need this extra information, so it may be ignored, but regardless, FileMaker will generate it when you export from FileMaker, and will expect it when you import into FileMaker.
Expanding again, one step further we see the most important nodes :
<?xml version="1.0"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<ERRORCODE>0</ERRORCODE>
<PRODUCT BUILD="" NAME="" VERSION="" />
<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="23" TIMEFORMAT="h:mm:ss a" />
<METADATA>
snip
</METADATA>
<RESULTSET FOUND="6">
snip
</RESULTSET>
</FMPXMLRESULT>
The METADATA and RESULTSET nodes are the critical part of the XML format. METADATA is a term used in other places outside the FileMaker XML format. Metadata means literally “data about data”. In the case of FileMaker and the XML it’s a node set that describes the data that is going to be exported. The RESULTSET contains the actual data itself.
So lets expand the METADATA node one step further. This time I’ll leave out all of the preceding and RESULTSET nodes so we can concentrate on the METADATA parts.
<?xml version="1.0"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<METADATA>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Last" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="First" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Group" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Score" TYPE="TEXT" />
</METADATA>
</FMPXMLRESULT>
So does the comment about meta data being “data about data” now make sense? This is a set of nodes to describe what the actual exported data is. It’s data that describes the data.
This node set tells us all of the fields that are in the export or import, all of their field types, some validation rules and repetition information – yes, the XML format understands repetitions, but just because they’re there doesn’t mean you should use them!
So now lets expand the RESULTSET node.
<?xml version="1.0"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<METADATA>
snip
</METADATA>
<RESULTSET FOUND="6">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
</RESULTSET>
</FMPXMLRESULT>
I’ve left out from this example all of the closing ROW nodes, but assume everyone has a closing node because other wise it wouldn’t be valid XML.
Two things to note here, the first is that the FOUND attribute in the RESULTSET node matches the number of ROW lines inside the RESULTSET node. The second is that the MODID and RECORDID values are all empty. This is because the data being generated is for importing into FileMaker not exported out of FileMaker. If it was an export, there would be real values in there. The MODID is a count of the number of times the record has been modified, the RECORDID is the internal record id of the records.
RECORDID is different from any field auto-enter serial id you generate within your data set. It’s an internal id that you can retreive from FileMaker using a Get(RecordID) function in a calculation.
Because we’re going to import this data into FileMaker these values don’t need to be included, and I’m showing them here more for reference. FileMaker will generate it’s own RECORDID’s for each record, we can’t set them, and the MODID will be set to zero, as they’re all new records.
So, now lets expand one of the ROW nodes.
<?xml version="1.0"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<METADATA>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Last" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="First" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Group" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Score" TYPE="TEXT" />
</METADATA>
<RESULTSET FOUND="6">
<ROW MODID="0" RECORDID="0">
<COL>
<COL>
<COL>
<COL>
</ROW>
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
</RESULTSET>
</FMPXMLRESULT>
Again, assume there’s a closing COL node for each of the COL nodes there.
This time, the thing to note, and the reason I put back all of the METADATA is that there is a COL node for each FIELD node in the METADATA. What this part of the XML is showing is the actual data. There is one column for each FIELD.
As an aside, the terminology is a bit off from what you’d expect in FileMaker. We’re used to fields and records, but this is showing ROWs and COLumns. Who knows why they chose this terminology, but the concept is the same. So, lets expand the data one step further.
<?xml version="1.0"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<METADATA>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Last" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="First" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Group" TYPE="TEXT" />
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Score" TYPE="TEXT" />
</METADATA>
<RESULTSET FOUND="6">
<ROW MODID="0" RECORDID="0">
<COL>
<DATA>White</DATA>
<COL>
<COL>
<DATA>Mark</DATA>
<COL>
<COL>
<DATA>Red</DATA>
<COL>
<COL>
<DATA>100</DATA>
<COL>
</ROW>
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
<ROW MODID="0" RECORDID="0">
</RESULTSET>
</FMPXMLRESULT>
There’s a lot of information there, but you can probably get the general idea. If you kept expanding the other ROW nodes, you’d find each one of those contains four COL nodes, each with it’s own DATA node containing the relevant data.
We have a result set.
Each result set contains as many rows as there are records in the data.
Each row ( or record ) contains as many columns as there are fields in the metadata ( the header row ).
And then each column contains data.
It may seem like a really convoluted way of containing export data. And it is. In short : that’s XML.
XML’s advantage is that it’s meant to be self describing. So this format is a way of generating a document in such a way that you wouldn’t have to know anything about the origin of the data. The XML itself contains everything that is needed to document the data. All of the columns and rows are ways of describing the real data that the XML document contains.
So how do I use this information?
To use this XML format, you’re either going to be exporting data from FileMaker, or importing it into FileMaker ( the CWP interface also uses XML, but that’s a whole other set of articles ).
If you’re exporting, this is the format that the data is going to be in. If you want to do something with it, you need to be able to read this format, and extract the data from within it.
If you’re importing into FileMaker, you’re going to have to generate this XML format as your data.
Fortunately there is something that can not only read this format and transform it into other formats, or can read another format, and transform the other format into this one. That thing is called XSLT.
Am I going to have to remember all of this?
Not really no. It’s good to understand all of this, but you wont’ have to know this by heart. What you’ll more likely do is setup one XSLT document once that contains all of the parts you need, and then we’ll just duplicate the various parts. At the end of this series I’ll provide some templates that do just that, and you can modify and adapt these to your own purposes.
So what is XSLT?
XLST documents are actually also XML documents, with special commands in them to work with other XML. XSLT is said to “transform” one document to another. We use it in FileMaker to either generate the format above or to take the data out of the format above and produce something else from it.
To explain how this fits into the process in FileMaker, go back into your copy of FileMaker and do an XML import or export. You’ll notice that there is an option for XSLT processing. Select that option and you get to select a document that is your xslt document to transform the XML you’re importing.
Imports first.
In an import, if you’re NOT using any XSLT, it’s going to expect the xml file you choose to match FileMaker’s required setup for XML import. If you’re using XLST, then the file you import won’t be in FileMaker’s format, it will be in some other format, and the XSLT will take that format and generate a new document which FileMaker will understand, and it will import this new document. You never see the intermediate document, it’s all just part of the import processing.
And exports.
In an export, if you’re NOT using XSLT, the output file will match the details above. If you ARE using XSLT, then the output isn’t XML at all, but will be whatever your XSLT generates. This may be an SVG image, or a PDF or RTF document, or any other format. Again, you don’t get to see the intermediate “FileMaker format” xml file, you’ll only see the end result.
Back to the DDR
Getting back now to where we started. We know what the DDR contains, we can get that just by looking through a sample document and looking at all of the node structure and contents. It would be really nice if FileMaker had documented the DDR somewhere in advance, but it’s not too difficult to figure it out once you’ve written a complex enough FileMaker data file.
And we now know what we need to be importing into FileMaker. This is the document format described above. So we’ve just got to write a suitable XSLT file that does the step of going from one to the other. It’s actually not that difficult.
And the other purpose I had for showing you all of this XML and how it works and how to use it is that the XSLT we’re going to write is actually an XML file itself. So it’s better to understand about XML and how it works within FileMaker to make things easier to create and write the XSLT.
Next
In the next and final article I’ll show how to write XSLT, and give some examples of how to get real data out of the DDR using XSLT.
Part 3 is now available here.