Configure Dynamic REST Parameters and XSLT Table Flattening Using Fixer IO

This article uses the Fixer IO currency API to demonstrate how to configure dynamic values using SQL queries in the Jet Analytics REST data source, and how to use XSLT table flattening to restructure nested JSON/XML responses. See the Fixer IO API documentation for full endpoint details.

A free Fixer IO account provides 1,000 API calls per month. Sign up at fixer.io to obtain a free API key.

Configure the data source

Add a new Jet Analytics REST data source, enter a name (for example, Fixer), and set the Base URL to:

http://data.fixer.io/api

Under Advanced > Request Limits, optionally set a daily limit (for example, 33) to spread the 1,000 monthly calls evenly across the month.

Endpoint examples

Latest rates (static endpoint with query parameters)

The /latest endpoint returns today's exchange rates. The base and symbols parameters are optional — if omitted, the API returns all currencies with EUR as the base.

https://data.fixer.io/api/latest ?access_key=API_KEY &base=USD &symbols=GBP,JPY,EUR

Configure the endpoint with Path = latest and Name = latest. Add the following query parameters:

access_key — your API key. Enable Is sensitive to mask it in previews.
base — the base currency (for example, USD).
symbols — a comma-separated list of currencies to return (for example, GBP,JPY,EUR).

Under Advanced, enable Use endpoint name as schema name. This is required because all Fixer endpoints return a table named rates — using the endpoint name as the schema produces a unique name (for example, latest.rates) for each endpoint.

Yesterday's rates (dynamic date from SQL query)

Historical rates for a specific date are returned by using the date as the endpoint path:

https://data.fixer.io/api/2013-12-24 ?access_key=API_KEY

To pass yesterday's date dynamically, use a SQL query in the Dynamic values section:

SELECT CONVERT(varchar(10), GETDATE()-1, 23) AS yesterday

Set the endpoint Path to {yesterday} and Name to yesterday. The curly brackets reference the column name returned by the query.

In the Dynamic values section, enable the feature, paste the query into the Query field, and set the Connection string to a SQL Server accessible from the machine running the Jet Analytics Ingest Service (for example, Server=localhost). Enter the SQL authentication username and password.

Timeseries (dynamic start and end dates)

The /timeseries endpoint returns rates for a range of dates:

https://data.fixer.io/api/timeseries ?access_key=API_KEY &start_date=2012-05-01 &end_date=2012-05-25

Note:

The timeseries endpoint requires a paid Fixer IO plan.

Set the start_date and end_date query parameter values to {start_date} and {end_date} respectively — these reference columns returned by the dynamic SQL query below:

SELECT CONVERT(varchar(10), GETDATE()-32, 23) AS start_date, CONVERT(varchar(10), GETDATE()-2, 23) AS end_date

Custom date range (looping through individual dates)

For free-plan users who cannot use the timeseries endpoint, the same result can be achieved by looping through individual dates. This requires an existing Prepare instance database.

First, add a user-defined function to the landing data area of the Prepare instance:

CREATE FUNCTION [Landing].[DateRange] ( @StartDate DATETIME, @EndDate DATETIME ) RETURNS @SelectedRange TABLE (IndividualDate DATETIME) AS BEGIN ;WITH cteRange (DateRange) AS ( SELECT @StartDate UNION ALL SELECT DATEADD(dd, 1, DateRange) FROM cteRange WHERE DateRange <= DATEADD(dd, -1, @EndDate) ) INSERT INTO @SelectedRange (IndividualDate) SELECT DateRange FROM cteRange OPTION (MAXRECURSION 3660); RETURN END

Configure the endpoint with Path = {range} and Name = range. In the Dynamic values section, use the following query to generate one row per date:

SELECT CONVERT(varchar(10), IndividualDate, 23) AS range FROM Landing.DateRange( CONVERT(varchar(10), GETDATE()-32, 23), CONVERT(varchar(10), GETDATE()-2, 23) )

Point the connection string to the specific SQL Server instance and Prepare database:

Server=localhost\SQL2019;Database=DevDW1

XSLT table flattening

By default, the Fixer API response splits into two tables: latest_TX_Root (containing the date, base, and timestamp) and rates (containing the exchange rates). The two tables share a TX_Autogenerated_Element_Id field, but this always has the value 0, making it impossible to join results from multiple endpoints.

XSLT table flattening can merge these into a single table. The basic XSLT template structure is:

<xsl:stylesheet version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" indent="yes" /> <xsl:template match="/"> <xbi_root> <xsl:apply-templates select="/TX_Autogenerated_Root/TX_Autogenerated_Element/rates" mode="rows" /> </xbi_root> </xsl:template> ... </xsl:stylesheet>

The xsl:apply-templates and the match attribute in the second template must both point to the same XPath — the location of the rates data in the response XML. Use ../ to navigate to parent-level fields such as date, timestamp, and base.

Unpivot currency rates using XSLT for-each

The default flattened output places all currency rates on a single row. To produce one row per currency — which is easier to work with in a Prepare instance — use an xsl:for-each loop to unpivot the rates:

<?xml version="1.0" encoding="UTF-8" ?> <xsl:stylesheet version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" indent="yes"/> <xsl:template match="/"> <xbi_root> <xsl:apply-templates select="//rates" mode="rows"/> </xbi_root> </xsl:template> <xsl:template match="//rates" mode="rows"> <xsl:variable name="timestamp" select="//timestamp"/> <xsl:variable name="date" select="//date"/> <xsl:variable name="base" select="//base"/> <xsl:for-each select="*[not(contains(., 'E'))]"> <xsl:element name="Latest"> <xsl:element name="code"> <xsl:value-of select="local-name()"/> </xsl:element> <xsl:element name="rate"> <xsl:value-of select="."/> </xsl:element> <xsl:element name="timestamp"> <xsl:value-of select="$timestamp"/> </xsl:element> <xsl:element name="date"> <xsl:value-of select="$date"/> </xsl:element> <xsl:element name="base"> <xsl:value-of select="$base"/> </xsl:element> </xsl:element> </xsl:for-each> </xsl:template> </xsl:stylesheet>

Key elements of this template:

The three parent-level fields (timestamp, date, base) are stored as xsl:variable elements and referenced with $variableName.
The xsl:for-each select="*[not(contains(., 'E'))]" iterates over all child elements of rates, excluding any whose text content contains the character E — this filters out rates with invalid scientific notation values.
local-name() returns the element name (the currency code).
select="." returns the text content of the current element (the exchange rate).
The outer xsl:element name="Latest" sets the result table name.

The result is one row per currency, with code, rate, timestamp, date, and base columns:

code	rate	timestamp	date	base
GBP	0.861219	1751956814	2025-07-08	EUR
EUR	1	1751956814	2025-07-08	EUR

Replace decimal separators in rate values

Depending on the locale settings of the SQL Server, dots in numeric values may be read as thousands separators rather than decimal points. To ensure correct decimal representation, use the XSLT translate() function to replace dots with commas in the rate element:

Replace:

<xsl:element name="rate"> <xsl:value-of select="."/> </xsl:element>

With:

<xsl:element name="rate"> <xsl:value-of select="translate(., '.', ',')"/> </xsl:element>

The translate(., '.', ',') function replaces every dot with a comma in the current node's string value. The final merged result across all endpoints looks like this: