Column/Schema Definition

Within the XML file you have a couple of ways to define the schema. You can either create a static schema, listing the columns and their properties, or you can use the schema discovery if the API being connected to supports it.

Static Schema Definition

When you add a datasource via a URL or file the schema will be listed as a static schema. HOwever if you would rather define the schema yourself, the below example should help.

Within the datasource node you need to add an action node with the GetDataSchema function. You then use the StaticSchemaMap node to specify it is a static schema, and within this define each column. The column needs the name and data type defining, and if it is a key column whether it is unique and if null values should be allowed.

<Action name="GetDataSchema">
    <StaticSchemaMap>
        <Column name="id" data-type="System.Int32" unique="true" allow-null="false" />
        <Column name="tourist_name" data-type="System.String" />
        <Column name="tourist_email" data-type="System.String" />
        <Column name="tourist_location" data-type="System.String" />
        <Column name="createddate" data-type="System.DateTime" />
    </StaticSchemaMap>
</Action>

The next step would be to define where the data is coming from through the GetDataTable function and a fetch action.

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.

The example below shows how this would come together with paging included:

<Datasource name="tourist">
    <Action name="GetDataSchema">
        <StaticSchemaMap>
            <Column name="id" data-type="System.Int32" unique="true" allow-null="false" />
            <Column name="tourist_name" data-type="System.String" />
            <Column name="tourist_email" data-type="System.String" />
            <Column name="tourist_location" data-type="System.String" />
            <Column name="createddate" data-type="System.DateTime" />
        </StaticSchemaMap>
    </Action>
    <Action name="GetDataTable">
        <PagingRequest path="data" start="1">
            <Fetch url="http://restapi.adequateshop.com/api/Tourist?page={PagingRequest.Page}">
                <DataTableTransform path="data" />
            </Fetch>
        </PagingRequest>
    </Action>
</Datasource>

Static Values

If you have static values that need to be assigned to a column you can use the ColumnMap function do so within the DataTableTransform function. In the example below when a specific node in the results are accessed the value "northern-ireland" should be written to the column region:

<DataTableTransform path="northern-ireland.events">
    <ColumnMap value="northern-ireland" to="region" />
</DataTableTransform>

Schema Discovery

If you have an API that supports it, then you can discover the schema through an API call using GetDataSchema.

The code below shows an example of the details needed to discover the schema and the column node defines the properties that should be extracted for each column within the response.

<Action name="GetDataSchema">
    <Fetch url="{URL}/app/list/{Datasource.Name}/Schema">
        <ForEach path="Columns">
            <Column name="$Name" data-type="$DataType" allow-null="$AllowNull" length="$MaxLength" unique="$Unique" />
        </ForEach>
    </Fetch>
</Action>

Node	Description
Action	Defines the action to be run, in this case GetDataSchema
Fetch	Defines the URL to fetch the schema details from
For Each	Defines that this should loop over each column in the response
Column	Defines what should be extracted for each column in the response. What you can return will depend upon what the API supports. You can define a static value if the API does not have a definition.

If the API does not have the column details you may need to manually define these. For example you may not be able to discover column data types as they might not be listed at teh endpoint. You could therefore manually define all as strings if needed.

After this you would then get and write the data to each column by using the GetDataTable function.

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

For this you need to define the fetch action to return the data and use the DataTableTransform to specify the data is to be entered into the columns. If the API supports paging, like the example above, make sure to enter the paging details. You can read more on paging here.

Column Definition

When defining the columns for the schema, at a minimum you need to define a name. It is also preferable to add a data-type to help with the schema mapping for when you use the datasource in a Data Sync project.

The table below describes what properties can make up a column.

Property	Description
name	The name or title of a column.
data-type	The data type of the data stored within that column.
Unique	If this is the primary key of the data and contains unique values this should be set to true. Not listing this property will set the value to false by default.
allow-null	Should null values be allowed in this column. If this is the primary key then this will usually be false. Not listing this property will set the value to true by default.