Prerequisites: Futurama Editor Tutorial 4.
Minimum Futurama Version to be used: 3.2
Learning goal: In this exercise we’ll expand Exercise 4 by introducing XML files. We will not discuss the theory and technics behind XML but refer to the literature instead. In this exercise we will show an example of a XML file containing userdata and see how we can import this data into our pensionscheme we made in exercise 4. We will make use of XPath queries to obtain the information we want. Exercise 4 can be downloaded here. Note that this example is created in version 3.2 of Futurama. Opening this example in a newer version of Futurama will prompt Futurama to update the example to the newer version. Click OK when this message appears.
- In the Windows Explorer create in the Futurama directory a new directory called "XML". This is the directory where our project will be stored.
- Copy in this directory the contents of either the existing directory "Pensionplan" or the downloaded version.
- Open the Futurama Editor.
- Click on Open and browse to calc.xml in the directory "XML". Open this file.
The userdata.xml file
In this tutorial we will not construct a XML file from scratch; that will go beyond the scope. Instead we will make use of an existing file "userdata.xml" which can be downloaded here. Place the file included in this zip-file next to calc.xml in the directory "XML". We will take a short look at this file first. Note that it is only an example of how a typical XML file might look like.
- Open the file in an editor e.g. Visual Studio or jEdit, although also Notepad can be used. Note that the file is not a Futurama document. Trying to open it in the Futurama Editor will result in an error.
A couple of things can be seen in the file:
- All data is contained in the element .
- Within there is the element containing data about the user. In addition an element could be added to represent fund specific data.
- Within we see consecutively the name, birthdate, systemdate and the AOW for this particular user.
- For the salary, current parttimepercentage and benefit computation base there are three elements . Each has an attribute "CODE" representing an unique administrative code and an attribute "DESCRIPTION".
- Every element contains an element which states the value and an element representing the accompanying date. So, for example, the salary at January 1 2009 was 26002,48.
- The element represents the several kinds of liabilities that are in the system. In this example we only have the old age pension, but it is straightforward to include more such as the spouse pension etc. The element also has an attribute "CODE" and "DESCRIPTION".
- The element contains an element which states the accrued entitlements at the system date and an element representing the standard retirement date at which this pension will start to be paid. In this case the standard retirement date is January 1 2015 which is the first of the month in which the user turns 65
Importing the XML file
We will now return to the Futurama Editor and import the information contained in the XML file.
- Create a new Node in the root document and name it 'Import'. It will appear beneath 'Calculation'.
- Select the Document 'Pensionplan' and go to the Design window.
- Drag the Node 'Import' upwards so that 'Calculation' is now beneath 'Import'.
It is always advisable to proper organize objects in the Futurama Editor. From a logical point of view it makes more sense that importing data comes before the calculations so it is for this reason that we have placed 'Import' above 'Calculation'.
Locating the file
First we create a path to our datafile.
- Define in the Node 'Import' a Formula of type string and name it 'Path'.
- Choose Text » Concatenate string.
- Define at Text1 a Formula and choose Document » GetPathToDataFiles string. This Formula returns the data directory i.e. the directory where the calc.xml is located.
- Define at Text2 a Fixed and type here userdata.xml, de name of the datafile. Click on OK.
When evaluating the result of 'Path' you can verify that it indeed is the complete path to userdata.xml.
Importing the file
We will now import the datafile.
- Define a Formula of type string and name it 'Source'.
- Choose Text » ReadFile string. This Formula returns the contents of a file as a text string.
- Define at FileName a Reference to 'Path'. Click on OK.
In the Result Window you can now see the contents of userdata.xml. If a mistake would have been made in 'Path', the file doesn’t exist or is invalid, 'Source' will return a (null) and further action is required. As you can see in the Result Window all data is still in the form of one long string. However, it is valid XML en therefore Futurama can access this by means of XMLNodes and XMLFields.
With a XMLNode all XML or a subselection of the XML can be retrieved from a certain source. Subsequently is it possible to retrieve values of specific elements in the XML with a XMLField as we will now demonstrate.
- Define an object XMLNode in the Node 'Import' and name it 'Data'.
Two properties are important here: Source and XPath. At Source we have to state where the XML comes from that has to be 'read'. At the property XPath we can enter a Xpath which will lead to the desired XML. A detailed description of XPath and it’s technicalities will go beyond the scope of this tutorial. We refer to the literature/internet for more information. See for example http://www.w3schools.com/xpath/ or http://www.liquid-technologies.com/xpath-tutorial.aspx.
- At Source define a Reference to 'Source'.
We want to access all data in the source. The element that contains everything is . We can access by means of the XPath “//DATA”.
- At XPath define a Fixed and type “//DATA”.
In the Result Window you can now see the contents of 'Data' represented by a XML tree instead of a long string such as in 'Source'. By clicking on the plus and minus-signs we can also access all the other elements and view their value. Verify for example that contains “A. Testperson”. We will now add XMLFields to access the individual elements.
- Under 'Data' create a XMLField of type string and name it 'Systemdate (string)'.
The systemdate is located in the element . The XMLNode allready accesses so our XPath “PERSON/SYSTEMDATE” will suffice.
- In the property XPath type “PERSON/SYSTEMDATE”.
When evaluating the XMLField in the Result Window you’ll see 01012009 appearing, just as expected. We will now create the other XMLFields.
- Create a XMLField of type string, name it 'Name' and set the property XPath to “PERSON/NAME”.
- Create a XMLField of type string, name it 'Birthdate (string)' and set the property XPath to “PERSON/BIRTHDATE”.
- Create a XMLField of type double, name it 'AOW' and set the property XPath to “PERSON/AOW”.
Next is to retrieve the salary. As can be seen in 'Data' it is located in the element but there are three elements . In particular we need the element where the CODE attribute is equal to “E110A104900110”. In XPath a “where”-clause can be implemented by placing the condition in brackets behind the name of the element. In our case the XPath would therefore be “PERSON/EXTRA[@CODE='E110A104900110']/VALUE”. Because CODE is an attribute of the element it has to be preceded by a '@'.
- Create a XMLField of type double, name it 'Salary' and set the property XPath to “PERSON/EXTRA[@CODE='E110A104900110']/VALUE”.
- Create a XMLField of type double, name it 'Parttimepercentage' and set the property XPath to “PERSON/EXTRA[@CODE='E111A105300006']/VALUE”. Note that the only difference is the value of CODE.
- Create a XMLField of type double, name it 'Benefit computation base' and set the property XPath to “PERSON/EXTRA[@CODE='E115A109000122']/VALUE”.
For the accrued pension we have to access the element and once again we will make use of a where-clause. Technically it would not be necessary in this case for there is only one element , but regarding eventual expansion of the XML-file it is best practice.
- Create a XMLField of type double, name it 'Accrued old age pension' and set the property XPath to “PERSON/LIABILITIES[@CODE='a1147']/ACCRUED”. Optionally you can verify that the XPath “PERSON/LIABILITIES/ACCRUED” produces the same result.
- Create a XMLField of type string, name it 'Retirement date (string)' and set the property XPath to “PERSON/LIABILITIES[@CODE='a1147']/DATE_START”.
Using the data
Now we have all elements imported. Later we will make references to these XMLFields in places where at the moment we have Fixeds, but first we have to do a conversion. The various dates are still imported as strings and yet have to be converted to dates. In order to do so we will make a conversion formula.
- Define in the Node 'Import' a Formula of type date and name it 'TextToDate'.
- Define an argument of type string and name it 'text'.
- Choose the formula Conversion » ConvertTestToDate date.
- Define at Text our argument 'text'.
- At Pattern we have to specify what the format of the dates is. A quick glance in the file userdate.xml reveals that the dates are in “MMddyyyy” notation (i.e. the birthdate is 01221950). Type in the Pattern the Fixed “MMddyyyy”. Click on OK.
- In the Design window drag the Formula 'TextToDate' to the poition above 'Path'.
Using 'TextToDate' we can now convert the systemdate, birthdate and retirement date.
- In the XMLNode 'Data' define a Reference to 'TextToDate'.
- In the Argument 'text' define a Reference to 'Systemdate (string)'. Click on OK.
- Name the Reference 'Systemdate'.
- Likewise, create to more References to convert 'Birthdate (string)' and 'Retirementdate (string)'. Name the References 'Birthdate' and 'Retirmentdate' respectively.
- To keep things a bit together, drag 'Systemdate' to the position just below 'Systemdate (string)' and do the same for the other two.
The Editor should now look like below.
With all elements in place now, we will change many of the Fixeds made in Exercise 4.
- Select the Fixed 'Birthdate' in the Node 'Dates'.
- Do a right-click and select in the contextmenu Change To | Reference.
- Define a Reference to 'Birthdate' in the XMLNode 'Data'.
Note that all dependencies are still attached to the Reference. Using Change To saves us a lot of work, since we don’t have to create a new Reference first, making sure all dependencies refer to the new Reference instead of the Fixed and finally to remove the Fixed.
- Change the Fixeds 'System date' and 'Standard retirement date' to References and let them refer to 'Systemdate' and 'Retirement date' in the XMLNode 'Data'.
- Change the Fixed 'Current parttimepercentage' to References and let it refer to 'Parttimepercentage' in the XMLNode 'Data'.
In the Node 'Userdata' we can change the Fixeds as well.
- Change the Fixeds 'Accrued Old Age Pension', 'Fulltime salary', 'AOW' and 'Benefit computation base' to References and let them refer to 'Accrued old age pension', 'Salary', 'AOW' and 'Benefit computation base' in the XMLNode 'Data' respectively.
We now have a pensionplanner that is much more flexible than the previous one. We could now make a userdata.xml file for every participant in the scheme and by simply changing the name in 'Path' import the proper userdata in the planner and see the calculations.
A complete example of this exercise can be downloaded here: XML. Note that this example is created in version 3.2 of Futurama. Opening this example in a newer version of Futurama will prompt Futurama to update the example to the newer version. Click OK when this message appears.