Skip to main content

Data Table Transform

The DataTableTransform action will automatically map columns from the json response to the DataTable based on the schema map mapping the columns by name (case-sensitive). If you want to manage the mapping or add additional mappings then you can add ColumnMap nodes to the DataTableTransform.

Mapping to Columns​

For example mapping a Json node Currency to a Schema Column named CCY. The Path here is a JsonPath expression and therefore can select complex nodes.

<DataTableTransform>
 <ColumnMap path="Currency" to="CCY" />
</DataTableTransform>

Static Values​

If you want to map a static value to a column you can also do this with the value attribute like this.

<DataTableTransform>
 <ColumnMap value="1" to="version" />
</DataTableTransform>

Data Table Discovery​

The DataTableTransform function is used to discover the data table and write the results to the columns listed in the schema. For example the following :

<Action name="GetDataTable">
  <Fetch url="https://services.odata.org/TripPinRESTierService/People">
    <DataTableTransform path="value" />
  </Fetch>
</Action>

Will write the data returned from the fetch url to the column values described in the schema. The full connector code for this can be seen here:

<Connector name="ODataV4 Sample" description="Connect to an OData V4 Feed" version="1.0">
  <Datasources>
    <Datasource name="People">
      <Actions>
        <Action name="GetDataSchema">
          <StaticSchemaMap>
            <Column name="UserName" type="System.String" unique="true" allownull="false" />
            <Column name="FirstName" type="System.String" length="255" />
            <Column name="LastName" type="System.String" />
            <Column name="Emails" type="JArray" />
          </StaticSchemaMap>
        </Action>
        <Action name="GetDataTable">
          <Fetch url="https://services.odata.org/TripPinRESTierService/People">
            <DataTableTransform path="value" />
          </Fetch>
        </Action>
      </Actions>
    </Datasource>
  </Datasources>
</Connector>

Paging Requests​

You can also find DataTableTransform within paging requests, for example in this page counter paging request.

<PagingRequest path="data" start="1">
  <Fetch url="{URL}/app/list/{Datasource.Name}?limit={PageSize}&amp;page={PagingRequest.Page}">
    <DataTableTransform path="data" />
  </Fetch>
</PagingRequest>

Stacked Response​

If you need to extract multiple parts from the JSON response then you can use the DataTableTransform function to extract the data and assign the value to a column.

Below the bank holidays are extracted from the UK government feed and the static region values are assigned to a region column.

<Fetch url="https://www.gov.uk/bank-holidays.json">
  <DataTableTransform path="england-and-wales.events">
    <ColumnMap value="england-and-wales" to="region" />
  </DataTableTransform>
  <DataTableTransform path="scotland.events">
    <ColumnMap value="scotland" to="region" />
  </DataTableTransform>
  <DataTableTransform path="northern-ireland.events">
    <ColumnMap value="northern-ireland" to="region" />
  </DataTableTransform>
</Fetch>

Adding Columns from Arrays​

To add a column found within an array deeper in the response you will need to define the path to that specific node in the GetDataTable section and define the column in the StaticSchemaMap section.

For example say there is a field called name wrapped within an array title companies: In the schema map section you would add: 

<Column name="name" data-type="System.String" />

And in the GetDataTable section you could add the following DataTableTransform below the existing one to get the values and map it to the summary column.

<DataTableTransform>
  <ColumnMap path="companies.name" to="name" />
</DataTableTransform>

An alternative approach would be to use calculated columns with the JSELECTTOKEN function to extract the name value.