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>

Extracting Values​

From Parent​

If you need to pull a value from the parent of the current json context. You can use the parent-path attribute.

<Action name="GetDataTable">
  <!-- Fetch the list of invoices from a json file -->
  <ReadFile path="invoices.json">
   <!-- For each invoice, get the related Line Items -->
   <ForEach path="invoices">
    <DataTableTransform path="line_items">
     <!-- Get the Invoice ID from the parent element -->
     <ColumnMap parent-path="id" to="invoice_id" />				
      </DataTableTransform>
   </ForEach>
 </ReadFile>
</Action>

From Inside JSON Element​

If you need to select part of a value from a string inside an json element you can use a Regular Expression with the expression attribute. The first group match will be returned as the value for the column.

<DataTableTransform path="contacts">
    <ColumnMap path="address" to="_internalID" expression="R:(\d{3,})" />
</DataTableTransform>

To select the OData ETag element use this format because of the @ in the name.

<ColumnMap path="$.['@odata.etag']" to="etag" />

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>

More details on adding datasources via a URL can be seen here.

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>

More details on paging options can be found on the paging page.

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.