If you regularly have to work with XML formatted data in FileMaker Pro®, getting the relevant parts of that data, and putting it into text fields can be a daunting task.
And you would probably say it is impossible to get a node of the XML out in one step as it would need a lot of script steps. But that is not the case if you use a plug-in.
Yes, it can be done!
It can be done if you install Troi Text Plug-in. This plug-in adds a function TrText_XML( ) to your arsenal of tools.
This function allows you to easily parse out a node from XML in one step! Here is the syntax of this function:
TrText_XML ("-GetNode" ; Node_to_get ; XML_data )
The function has three parameters: the first parameter specifies the action to take, in this case to get a node from the XML. The second parameter specifies which node you want to get. Finally the third parameter is the XML data you want to parse.
The result of the TrText_XML function will be the text of the node you are interested in. You can use this function in a script, for example in a Set Variable step.
You may not realize this, but you can also put this function in a calculation field: this will make the function recalculate automatically if one of the parameters changes, always giving the right result.
Defining the calculation field
Let’s make this concrete: assume your database already contains a text field XML_data, in which the XML formatted text is stored. You need to define three extra fields:
- Node_to_get, text field
- Node_data, a calculation field
- Attribute_data, a calculation field (optionally, if you need attribute data)
For Node_data specify this calculation:
= TrText_XML ("-GetNode" ; Node_to_get; XML_data )
For Attribute_data specify this calculation:
= TrText_XML ("-GetAttributes" ; Node_to_get; XML_data )
That’s it. See also below a screenshot of the defined field for this FileMaker database:
Now let’s parse XML!
After you have put the fields on a layout you can parse the XML. Let’s say XML_data contains this text:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE transaction SYSTEM "http://test.ape.com/troi.dtd">
<transaction>
<vendor>Troi</vendor>
<transaction_id>8161</transaction_id>
<date>20000816</date>
<transaction_remarks/>
<product>
<name version=3.5>Super Plug-in</name>
<amountpaid>19.95</amountpaid>
</product>
<more_data>
<bla>vulling</bla>
<jadda>ahum</jadda>
</more_data>
</transaction>
In the field Node_to_get you can now specify the nodes and sub-nodes separated by a slash, like “main node/sub node/subsub node”.
Assume that in this example you are interested in this node path:
transaction/product/name
If you enter this node path in the Node_to_get field the TrText_XML function returns: “Super Plug-in”.
Not all XML data has attributes, but in this case it does, and in the Attribute_data field the attribute data “version=3.5” is returned.
And the advantage of this implementation is that it always works: when the XML_data field changes, the calculation will automatically update and the result will show correctly on the layout.
Play with the example and demo plug-in
The Troi Text Plug-in comes with multiple example files, which demonstrate the functionality of all functions of the plug-in. Below you can see the example file ParseXML.fmp12, with the data of our example:
If you have not done so already, you should download the fully functional demo version of the plug-in and play with it to see how easy it makes parsing XML.
More functions for FileMaker Pro 14
The TrText_XML function is just one of the functions added by Troi Text Plug-in. The plug-in also adds functions for manipulating 2 fields of data (AND/OR/XOR) and more. It is rewritten to support 64-bit and is compatible with FileMaker Pro 12 to FileMaker Pro 14.
On the Troi Text Plug-in page you can download a fully functional demo with all the functions explained here:
www.troi.com/products/textplugin.
We are sure that this plug-in will speed up your work with text data.
