The CData Sync App is designed for streaming REST only.

This streamed file content does not include all of the metadata associated with remotely stored REST files, such as file and folder name.

If access to both the file metadata and the actual file content is needed, then the CData Sync App must be used in tandem with the associated file system driver(s) for the service the REST files are remotely stored in.

The following file system drivers are available:

• AmazonS3
• Box
• Dropbox
• FTP
• GoogleCloudStorage
• IBLCloudObjectStorage
• OneDrive
• SFTP

See the relevant CData file system driver's documentation for a configuration guide for connecting to stored REST file metadata.

Adding a Connection to REST

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the REST icon is not available, click the Add More icon to download and install the REST connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Modeling XML/JSON/CSV Data

The DataModel property controls how your data is represented into tables.

• Document (default): Model a top-level, document view of your REST data. The Sync App returns nested object arrays as aggregated XML/JSON/CSV objects.
• FlattenedDocuments: Implicitly join nested array objects and parent objects into a single table.
• Relational: View nested object arrays as individual, related tables containing a primary key and a foreign key that links to the parent document.

Set Format to XML, JSON, or CSV in accordance with the data structuring standard used by the REST source you want to connect to. and set DataModel to more closely match the data representation to the structure of your data.

Next Steps

• See Modeling REST Data for information on customizing schema discovery and executing SQL to REST.
• See Fine-Tuning Data Access for more advanced connection settings: fine-tune the default data modeling settings, connect through a firewall, or troubleshoot connections.

Kerberos

To authenticate to REST with Kerberos, set AuthScheme to NEGOTIATE.

Authenticating to REST via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

Retrieve Kerberos Tickets

The Sync App provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Cerberos Credential Cache Files:

1. Ensure that the KRB5CCNAME variable is present in your environment.
2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.
3. To obtain a ticket:
   1. Open the MIT Kerberos Ticket Manager application.
   2. Click Get Ticket.
   3. Enter your principal name and password.
   4. Click OK.

   If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The Sync App uses the cache file to obtain the Kerberos ticket to connect to REST.

Note: If you would prefer not to edit KRB5CCNAME, you can use the KerberosTicketCache property to set the file path manually. After this is set, the Sync App uses the specified cache file to obtain the Kerberos ticket to connect to REST.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with REST.

Enabling Cross-Realm Authentication

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

The CData Sync App enables the granular control useful in more complex deployments or network topologies; you can use the following connection properties to fine-tune data access, connect through a firewall, or troubleshoot connections.

Fine-Tuning Data Access

You can use the following properties to gain greater control over how the Sync App parses the XML data into rows. You can also customize the schemas detected based on the connection string.

• RowScanDepth: This property determines the number of rows that will be scanned to detect column data types when generating table metadata.
• XPath: Explicitly specify the paths to nested object arrays, instead of detecting them during the row scan.

• GenerateSchemaFiles: This property enables you to persist table metadata in static schema files that are easy to customize, to persist your changes to column data types, for example. Set this property to "OnStart" to generate schema files for all tables in your database at connection. Or, set this property to "OnUse" to generate schemas as you execute SELECT queries to tables. The resulting schemas are based on the connection properties you use to configure Automatic Schema Discovery.

  To use the resulting schema files, set the Location property to the folder containing the schemas. See Customizing Schemas for more information.

In this section we will show how to control the various schemes that the Sync App offers to bridge the gap with relational SQL and REST services. The CData Sync App provides a managed way for you to use the two prevailing techniques for dealing with nested data:

• Parsing the data structure and building a relational model based on the existing hierarchy.
• Drilling down into the nested elements using horizontal and vertical flattening.

Parsing Hierarchical Data

By default, the Sync App automatically detects the rows in a document, so that you do not need to know the structure of the underlying data to query it with SQL. Set the DataModel property to choose a basic configuration of how the Sync App models the rows into tables.

Flattening Objects and Arrays into Rows

To flatten data, you only need to be familiar with two data structures -- objects and arrays. In JSON, these are literal structures. In XML, the analogous structures are below:

• Object: Any parent element that does not repeat at the same height.
• Array: Any element that repeats at the same height.

In the following example from the people collection, maintenance is an object array, since each maintenance node has child elements.

<maintenance>
  <date>07-17-2017</date>
  <desc>oil change</desc>
</maintenance>
<maintenance>
  <date>01-03-2018</date>
  <desc>new tires</desc>
</maintenance> 

Configuring Automatic Schema Discovery

The Sync App discovers columns and data types by scanning the RowScanDepth count of objects in arrays. Set the FlattenObjects and FlattenArrays properties to configure how nested data is flattened into columns; see Automatic Schema Discovery for examples.

Executing SQL to REST

Any relation you can access through flattening you can also access with an ad-hoc SQL query. The Sync App enables you to query nested data with the following capabilities:

• Vertical Flattening: Access nested object arrays as separate tables.
• Free-Form Queries: Query any nested structure without flattening the data.
• XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

Customizing Schemas

Customizing Schemas enables you to project your chosen relational structure on top of a document. This allows you to choose the names of columns, their data types, and the locations of their values in the document.

System Catalog

The System Tables reflect the schemas you configured, custom schemas or dynamically discovered. The Stored Procedures surface additional functionality in the Sync App's data processing operations that cannot be modeled as SELECT, INSERT, UPDATE, or DELETE. You can find the reported stored procedures defined in .rsb files in the folder specified by Location -- if Location is not specified, the db subfolder of the installation directory.

Below is the raw data used throughout this chapter. The data includes entries for people, the cars they own, and various maintenance services performed on those cars:

 
<?xml version="1.0" encoding="UTF-8" ?>
<root>
  <rootAttr1>rootValue1</rootAttr1>
  <people>
    <personal>
      <age>20</age>
      <gender>M</gender>
      <name>
        <first>John</first>
        <last>Doe</last>
      </name>
    </personal>
    <jobs>support</jobs>
    <jobs>coding</jobs>
    <vehicles>
      <type>car</type>
      <model>Honda Civic</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>sunroof</features>
      <features>rims</features>
      <maintenance>
        <date>07-17-2017</date>
        <desc>oil change</desc>
      </maintenance>
      <maintenance>
        <date>01-03-2018</date>
        <desc>new tires</desc>
      </maintenance>
    </vehicles>
    <vehicles>
      <type>truck</type>
      <model>Dodge Ram</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>lift kit</features>
      <features>tow package</features>
      <maintenance>
        <date>08-27-2017</date>
        <desc>new tires</desc>
      </maintenance>
      <maintenance>
        <date>01-08-2018</date>
        <desc>oil change</desc>
      </maintenance>
    </vehicles>
    <addresses>
      <type>work</type>
      <zip>12345</zip>
    </addresses>
    <addresses>
      <type>home</type>
      <zip>12357</zip>
    </addresses>
    <source>internet</source>
  </people>
  <people>
    <personal>
      <age>24</age>
      <gender>F</gender>
      <name>
        <first>Jane</first>
        <last>Roberts</last>
      </name>
    </personal>
    <jobs>sales</jobs>
    <jobs>marketing</jobs>
    <source>phone</source>
    <vehicles>
      <type>car</type>
      <model>Toyota Camry</model>
      <insurance>
        <company>Car Insurance</company>
        <policy_num>98765</policy_num>
      </insurance>
      <features>upgraded stereo</features>
      <maintenance>
        <date>05-11-2017</date>
        <desc>tires rotated</desc>
      </maintenance>
      <maintenance>
        <date>11-03-2017</date>
        <desc>oil change</desc>
      </maintenance>
    </vehicles>
    <vehicles>
      <type>car</type>
      <model>Honda Accord</model>
      <insurance>
        <company>Car Insurance</company>
        <policy_num>98765</policy_num>
      </insurance>
      <features>custom paint</features>
      <features>custom wheels</features>
      <maintenance>
        <date>10-07-2017</date>
        <desc>new air filter</desc>
      </maintenance>
      <maintenance>
        <date>01-13-2018</date>
        <desc>new brakes</desc>
      </maintenance>
    </vehicles>
    <addresses>
      <type>home</type>
      <zip>98765</zip>
    </addresses>
    <addresses>
      <type>work</type>
      <zip>98753</zip>
    </addresses>
  </people>
  <rootAttr2>rootValue2</rootAttr2>
  <rootAttr3>rootValue3</rootAttr3>
  <rootAttr3>rootValue4</rootAttr3>
</root>

The Sync App offers three basic configurations to model nested data in XML and JSON as tables. See the following sections for examples of parsing objects and arrays.

• Flattened Documents Model: Implicitly join nested object arrays into a single table.
• Relational Model: Model object arrays as individual tables containing a primary key and a foreign key that links to the parent document.
• Top-Level Document Model: Model a top-level view of a document. Nested object arrays are returned as aggregates.

For users who simply need access to the entirety of their data, flattening the data into a single table is the best option. The Sync App will use streaming and only parses the data once per query in this mode.

Joining Object Arrays into a Single Table

With DataModel set to "FlattenedDocuments", the Sync App returns a separate table for each object array, but implicitly JOINed to the parent table. Any nested sibling XPath values (child paths at the same height) will be treated as a SQL CROSS JOIN.

Example

Below is a sample query and the results, based on the sample document in Raw Data and parsing based on the XPaths /root/people, /root/people/vehicles, and /root/people/vehicles/maintenance. This implicitly JOINs the people element with the vehicles element and implicitly JOINs the vehicles element with the maintenance element.

Connection String

Use the following connection string to query the Raw Data in this example.

URI=C:\people.txt;Format=XML;DataModel=FlattenedDocuments;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;'

Query

The following query drills into the nested elements in each people element. Since the XPath property included the vehicles node, you can query an element of a vehicle explicitly.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [type],
  [model],
  [insurance.company] AS ins_company,
  [insurance.policy_num] AS ins_policy_num,
  [date] AS maint_date,
  [desc] AS maint_desc
FROM
  [people]

Results

With horizontal and vertical flattening based on the described paths, each vehicle element is implicitly JOINed to its parent people element and each maintenance element is implicitly JOINed to its parent vehicle element.

See Also

• Automatic Schema Discovery: Configure the columns reported in the table schemas.
• Free-Form Queries: Use dot notation to select nested data.
• Vertical Flattening: Query nested data as separate tables.
• XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

Using a top-level document view of the data provides ready access to top-level elements. The Sync App returns nested elements in aggregate, as single columns.

One aspect to consider is performance. You forego the time and resources to process and parse nested elements -- the Sync App parses the returned data once, using streaming to read the data. Another consideration is your need to access any data stored in nested parent elements, and the ability of your tool or application to process JSON or XML.

Modeling a Top-Level Document View

With DataModel set to "Document" (the default), the Sync App scans only a single element, the top-level element by default. The top-level elements are available as columns due to the default object flattening. Nested elements are returned as aggregates.

You can set XPath to specify an element other than the top-level one.

Example

Below is a sample query and the results, based on the sample document in Raw Data. The query results in a single "people" table based on the XPath "/root/people".

Connection String

Set the DataModel connection property to "Document" to perform the following query and see the example result set. The Sync App will scan only the XPath value below:

URI=C:\people.txt;Format=XML;DataModel=Document;XPath='/root/people';

Query

The following query pulls the top-level elements and the subelements of the vehicles element into the results.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [vehicles]
FROM
  [people]
  

Results

With a document view of the data, the personal element is flattened into 4 columns and the source and vehicles elements are returned as individual columns, resulting in a table with 6 columns.

  <vehicles><type>car</type><model>Honda Civic</model><insurance><company>ABC Insurance</company><policy_num>12345</policy_num></insurance><features>sunroof</features><features>rims</features><maintenance><date>07-17-2017</date><desc>oil change</desc></maintenance><maintenance><date>01-03-2018</date><desc>new tires</desc></maintenance></vehicles><vehicles><type>truck</type><model>Dodge Ram</model><insurance><company>ABC Insurance</company><policy_num>12345</policy_num></insurance><features>lift kit</features><features>tow package</features><maintenance><date>08-27-2017</date><desc>new tires</desc></maintenance><maintenance><date>01-08-2018</date><desc>oil change</desc></maintenance></vehicles>

  <vehicles><type>car</type><model>Toyota Camry</model><insurance><company>Car Insurance</company><policy_num>98765</policy_num></insurance><features>upgraded stereo</features><maintenance><date>05-11-2017</date><desc>tires rotated</desc></maintenance><maintenance><date>11-03-2017</date><desc>oil change</desc></maintenance></vehicles><vehicles><type>car</type><model>Honda Accord</model><insurance><company>Car Insurance</company><policy_num>98765</policy_num></insurance><features>custom paint</features><features>custom wheels</features><maintenance><date>10-07-2017</date><desc>new air filter</desc></maintenance><maintenance><date>01-13-2018</date><desc>new brakes</desc></maintenance></vehicles>

See Also

• Automatic Schema Discovery: Configure column discovery with horizontal flattening.
• Free-Form Queries: Use dot notation to select nested data.
• Vertical Flattening: Query nested data as separate tables.
• XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

The CData Sync App can be configured to create a relational model of the data, treating each XPath as an individual table containing a primary key and a foreign key that links to the parent document. This is particularly useful if you need to work with your data in existing BI, reporting, and ETL tools that expect a relational data model.

Joining Nested Arrays as Tables

With DataModel set to "Relational", any JOINs are controlled by the query. Any time you perform a JOIN query, the file or source will be queried once for each table included in the query.

Example

Below is a sample query against the sample document in Raw Data, using a relational model based on the XPaths "/root/people", "/root/people/vehicles", and "/root/people/vehicles/maintenance".

Connecting String

Set the DataModel connection property to "Relational" and set the XPath connection property to "/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;" to perform the following query and see the example result set.

URI=C:\people.txt;Format=XML;DataModel=Relational;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;'

Query

The following query explicitly JOINs the people, vehicles, and maintenance tables.

SELECT 
  [people].[personal.age] AS age, 
  [people].[personal.gender] AS gender, 
  [people].[personal.name.first] AS first_name, 
  [people].[personal.name.last] AS last_name, 
  [people].[source], 
  [vehicles].[type], 
  [vehicles].[model], 
  [vehicles].[insurance.company] AS ins_company, 
  [vehicles].[insurance.policy_num] AS ins_policy_num, 
  [maintenance].[date] AS maint_date, 
  [maintenance].[desc] AS maint_desc
FROM 
  [people]
JOIN 
  [vehicles] 
ON 
  [people].[_id] = [vehicles].[people_id]
JOIN 
  [maintenance] 
ON 
  [vehicles].[_id] = [maintenance].[vehicles_id]

Results

In the example query, each maintenance element is JOINed to its parent vehicle element, which is JOINed to its parent people element to produce a table with 8 rows (2 maintenance entries for each of 2 vehicles each for 2 people).

See Also

• Automatic Schema Discovery: Configure the columns reported in the table schemas.
• Free-Form Queries: Use dot notation to select nested data.
• Vertical Flattening: Query nested data as separate tables.
• XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

By default, the Sync App automatically infers a relational schema by inspecting a series of REST objects in an array. This section describes the connection properties available to configure these dynamic schemas.

Discovering Tables

This section shows how to fine-tune the discovered schemas by fine-tuning the row scan. The rows detected depend on the RowScanDepth, DataModel, and XPath properties.

• RowScanDepth: This setting determines the number of object arrays to scan to find the rows and discover the columns. The process follows nested objects, counting 1 object array as 1 row.
• DataModel: Select how you want to represent the object arrays as tables. This setting also affects the objects that are scanned: the Relational and FlattenedDocuments settings will find all object arrays (including nested ones) in RowScanDepth items. The Document setting will only find the first object array.

• XPath: Set this to explicitly specify the object arrays you want to expose. Note that if DataModel is set to Document, you can only set one, top-level XPath. See Parsing Hierarchical Data for examples of accessing a sample document with different XPath values.

Discovering Columns

The columns identified during the discovery process depend on the FlattenArrays and FlattenObjects properties. If FlattenObjects is set (this is the default), nested objects will be flattened into a series of columns. For example, consider the following document:

<company>
  <id>12</id>
  <name>Lohia Manufacturers Inc.</name>
  <address>
    <street>Main Street</street>
    <city>Chapel Hill</city>
    <state>NC</state>
  </address>
  <office>Chapel Hill</office>
  <office>London</office>
  <office>New York</office>
  <annual_revenue>35,600,000</annual_revenue>
</company> 

If FlattenObjects is not set, then the address.street, address.city, and address.state columns will not be broken apart. The address column of type string will instead represent the entire object. Its value would be the following:

<address><street>Main Street</street><city>Chapel Hill</city><state>NC</state></address>

The FlattenArrays property can be used to flatten array values into columns of their own. This is only recommended for arrays that are expected to be short, for example the offices array below:

<office>London</office>
<office>Los Angeles</office>

It is best to leave other unbounded arrays as they are and piece out the data for them as needed using XML Functions.

As discussed in Automatic Schema Discovery, intuited table schemas enable SQL access to unstructured REST data. Customizing Schemas enables you to define static tables and gives you more granular control over the relational view of your data; for example, you can change the data types reported. However, you are not limited to the schema's view of your data.

You can query any nested structure without flattening the data. Any relations that you can access through Automatic Schema Discovery can also be accessed with an ad hoc SQL query.

Extended Projection Syntax

In the SELECT clause, use dot notation to specify an XPath to the data, as in the following query.

SELECT [personal.name.last], [personal.name.first], [vehicles.1.type], [vehicles.1.model] FROM people WHERE [personal.name.last] = 'Roberts' AND [personal.name.first] = 'Jane'

Note that to specify the path to a specific array element, specify the element's ordinal position. Arrays have a zero-based index, so the preceding query retrieves the second vehicle.

Example

The preceding query draws the column names from the example people document in Raw Data. Below is a person object from the array of people:

<?xml version="1.0" encoding="utf-8"?>
<root>
  <rootAttr1>rootValue1</rootAttr1>
  <people>
    <personal>
      <age>20</age>
      <gender>M</gender>
      <name>
        <first>John</first>
        <last>Doe</last>
      </name>
    </personal>
    <jobs>support</jobs>
    <jobs>coding</jobs>
    <vehicles>
      <type>car</type>
      <model>Honda Civic</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>sunroof</features>
      <features>rims</features>
      <maintenance>
        <date>07-17-2017</date>
        <desc>oil change</desc>
      </maintenance>
      <maintenance>
        <date>01-03-2018</date>
        <desc>new tires</desc>
      </maintenance>
    </vehicles>
    <vehicles>
      <type>truck</type>
      <model>Dodge Ram</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>lift kit</features>
      <features>tow package</features>
      <maintenance>
        <date>08-27-2017</date>
        <desc>new tires</desc>
      </maintenance>
      <maintenance>
        <date>01-08-2018</date>
        <desc>oil change</desc>
      </maintenance>
    </vehicles>
    <addresses>
      <type>work</type>
      <zip>12345</zip>
    </addresses>
    <addresses>
      <type>home</type>
      <zip>12357</zip>
    </addresses>
    <source>internet</source>
  </people>
</root> 

Connection String

With the following connection string, the Sync App will not parse nested data -- the data is processed when you execute the query. The properties of the top-level object are still flattened through the default FlattenObjects functionality. Nested data is returned as an XML aggregate.

URI=C:\people.txt;DataModel=Document;XPath='/root/people;'

Query

You can access any nested structure in the Raw Data document as a column:

SELECT [personal.name.last], [personal.name.first], [vehicles.1.type], [vehicles.1.model] FROM people WHERE [personal.name.last] = 'Roberts' AND [personal.name.first] = 'Jane'

Note that arrays have a zero-based index. The preceding query retrieves the example person's second vehicle.

Results

The preceding query returns the following results:

Vertical flattening queries enable you to retrieve a nested element as if it were a separate table.

Vertical Flattening Query Syntax

In the FROM clause, you can use dot notation to drill down to a nested element.

SELECT * FROM [people.vehicles] 

Example

Consider a single array element from the Raw Data document -- a person object from an array of people:

<?xml version="1.0" encoding="UTF-8" ?>
<root>
<people>
  <personal>
    <age>20</age>
    <gender>M</gender>
    <name>
      <first>John</first>
      <last>Doe</last>
    </name>
  </personal>
  <vehicles>
    <type>car</type>
    <model>Honda Civic</model>
    <insurance>
      <company>ABC Insurance</company>
      <policy_num>12345</policy_num>
    </insurance>
    <maintenance>
      <date>07-17-2017</date>
      <desc>oil change</desc>
    </maintenance>
    <maintenance>
      <date>01-03-2018</date>
      <desc>new tires</desc>
    </maintenance>
  </vehicles>
  <vehicles>
    <type>truck</type>
    <model>Dodge Ram</model>
    <insurance>
      <company>ABC Insurance</company>
      <policy_num>12345</policy_num>
    </insurance>
    <maintenance>
      <date>08-27-2017</date>
      <desc>new tires</desc>
    </maintenance>
    <maintenance>
      <date>01-08-2018</date>
      <desc>oil change</desc>
    </maintenance>
  </vehicles>
  <source>internet</source>
</people>
</root> 
  

Connection String

With the following connection string, the Sync App will not parse nested data -- the data is processed when you execute the query. Due to the default FlattenObjects functionality, the properties of the top-level element are flattened. Nested data is returned as an XML aggregate.

URI=C:\people.txt;DataModel=Document;XPath='/root/people;'

Query

Vertical flattening will allow you to retrieve the vehicles element as a separate table:

SELECT * FROM [people.vehicles]

<features>sunroof</features><features>rims</features>

<maintenance><date>07-17-2017</date><desc>oil change</desc></maintenance><maintenance><date>01-03-2018</date><desc>new tires</desc></maintenance>

<features>lift kit</features><features>tow package</features>

<maintenance><date>08-27-2017</date><desc>new tires</desc></maintenance><maintenance><date>01-08-2018</date><desc>oil change</desc></maintenance>

<features>upgraded stereo</features>

<maintenance><date>05-11-2017</date><desc>tires rotated</desc></maintenance><maintenance><date>11-03-2017</date><desc>oil change</desc></maintenance>

<features>custom paint</features><features>custom wheels</features>

<maintenance><date>10-07-2017</date><desc>new air filter</desc></maintenance><maintenance><date>01-13-2018</date><desc>new brakes</desc></maintenance>

The Sync App can return XML as column values. The Sync App enables you to use SQL functions to work with these column values. The following sections provide examples; for a reference, see STRING Functions. The examples in this section use the following array (see Parsing Hierarchical Data for more information on parsing XML objects and arrays):

 
<grades>
  <student>
    <grade>A</grade>
    <score>2</score>
  </student>
  <student>
    <grade>A</grade>
    <score>6</score>
  </student>
  <student>
    <grade>A</grade>
    <score>10</score>
  </student>
  <student>
    <grade>A</grade>
    <score>9</score>
  </student>
  <student>
    <grade>B</grade>
    <score>14</score>
  </student>
</grades>

XML_EXTRACT

SELECT Name, XML_EXTRACT(grades,'[0].grade') AS Grade, XML_EXTRACT(grades,'[0].score') AS Score FROM Students;

XML_COUNT

SELECT Name, XML_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;

XML_SUM

SELECT Name, XML_SUM(score,'[x].score') AS TotalScore FROM Students;

XML_MIN

SELECT Name, XML_MIN(score,'[x].score') AS LowestScore FROM Students;

XML_MAX

SELECT Name, XML_MAX(score,'[x].score') AS HighestScore FROM Students;

The Sync App can return JSON structures as column values. The Sync App enables you to use standard SQL functions to work with these JSON structures. The examples in this section use the following array:

[
     { "grade": "A", "score": 2 },
     { "grade": "A", "score": 6 },
     { "grade": "A", "score": 10 },
     { "grade": "A", "score": 9 },
     { "grade": "B", "score": 14 }
]

JSON_EXTRACT

SELECT Name, JSON_EXTRACT(grades,'[0].grade') AS Grade, JSON_EXTRACT(grades,'[0].score') AS Score FROM Students;

JSON_COUNT

SELECT Name, JSON_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;

JSON_SUM

SELECT Name, JSON_SUM(score,'[x].score') AS TotalScore FROM Students;

JSON_MIN

SELECT Name, JSON_MIN(score,'[x].score') AS LowestScore FROM Students;

JSON_MAX

SELECT Name, JSON_MAX(score,'[x].score') AS HighestScore FROM Students;

You can customize the table schemas detected through Automatic Schema Discovery to change column names and data types, enable update functionality, and more. The processing operations of the CData Sync App hide the complexity of processing data and communicating with remote data sources, but they also expose control over these layers through custom schemas.

Custom schemas are defined in configuration files. In this chapter we outline the structure of these files.

Generating Schema Files

Generating Schema Files allows you to persist and customize the dynamic schemas discovered through Automatic Schema Discovery.

Editing Schema Files

Tables and views are defined by authoring schema files in APIScript. APIScript is a simple configuration language that allows you to define the columns and the behavior of the table. It also has built-in operations that enable you to process REST. In addition to these data processing primitives, APIScript is a full-featured language with constructs for conditionals, looping, etc. However, as shown by the example schema, for most table definitions you will not need to use these features.

Example Schema

Below is a fully functional table schema that models the people document in the Raw Data example and contains all the components you will need to execute SQL to JSON/XML data sources.

The schema reflects the following connection string, which returns a single table containing the data delineated by the specified XPaths -- see Parsing Hierarchical Data for a guide to the different DataModel settings.

DataModel=FLATTENEDDOCUMENTS;URI=C:\people.xml;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance';Location=C:\myschemas;GenerateSchemaFiles=OnStart;Format=XML;

You can find more information on each of the components of a schema in Column Definitions, SELECT Execution, INSERT Execution, UPDATE Execution, and DELETE Execution. You can also create stored procedures to implement capabilities of your API that cannot be modeled as SELECT, INSERT, UPDATE, or DELETE statements. See Defining Stored Procedures for more information.

  <api:script>
     <api:info title="Persons" desc="Parse the OData Persons feed.">
    <!-- You can modify the name, type, and column size here. -->
    <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from XML. -->
      <attr name="ID"           xs:type="int" key="true" readonly="false"   other:xPath="/feed/entry/content/properties/ID" />
      <attr name="EmployeeID"   xs:type="int"            readonly="true"    other:xPath="/feed/entry/content/properties/EmployeeID" />
      <attr name="Name"         xs:type="string"         readonly="false"   other:xPath="/feed/entry/content/properties/Name" />
      <attr name="TotalExpense" xs:type="double"         readonly="true"    other:xPath="/feed/entry/content/properties/TotalExpense" />
      <attr name="HireDate"     xs:type="datetime"       readonly="true"    other:xPath="/feed/entry/content/properties/HireDate" />
      <attr name="Salary"       xs:type="int"            readonly="true"    other:xPath="/feed/entry/content/properties/Salary" />
    </api:info>
    
    <api:set attr="uri" value="http://services.odata.org/V4/OData/(S(5cunewekdibfhpvoh21u2all))/OData.svc/Persons" /> 
  
    <!-- The XPath attribute of a schema is the path to a repeating element that defines the separation of rows -->
    <api:set attr="XPath" value="/feed/entry/" />
  
    <!-- See the xmlproviderGet page in the Operations subchapter to set any needed HTTP parameters. -->
    <api:set attr="ContentType"   value="application/atom+xml" />
  
  <!-- The GET method corresponds to SELECT. Here you can change the parameters of the request for data. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:call op="xmlproviderGet">
      <api:push/>
    </api:call>
  </api:script>
  
  <!-- To add support for INSERTS please see the INSERT Execution page within the help for further information and examples. -->
  <api:script method="POST">
    <api:set attr="method" value="POST"/>
    <api:call op="xmlproviderGet">
      <api:throw code="500" desc="Inserts are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  <!-- To add support for UPDATES please see the UPDATE Execution page within the help for further information and examples. -->
  <api:script method="MERGE">
    <api:set attr="method" value="PUT"/>
    <api:call op="xmlproviderGet">
      <api:throw code="500" desc="Updates are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  <!-- To add support for DELETES please see the DELETE Execution page within the help for further information and examples. -->
  <api:script method="DELETE">
    <api:set attr="method" value="DELETE"/>
    <api:call op="xmlproviderGet">
      <api:throw code="500" desc="Deletes are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  </api:script> 

To gain more control over the schemas discovered dynamically through Automatic Schema Discovery, you can save the schema to a configuration file. The GenerateSchemaFiles property enables you to automatically generate schemas based on the data modeling settings configured in the connection string. The CreateSchema stored procedure enables you to create schemas for other XPaths after you connect.

Generating Schema Files Automatically

You can generate schemas when you connect or when you execute a query. The Sync App will save the schemas to the folder specified by Location.

• Set GenerateSchemaFiles to OnStart to generate schemas for all tables detected when you connect.
• Set GenerateSchemaFiles to OnUse to generate a schema for the referenced table when you query the table.

Using the CreateSchema Stored Procedure

You can call the CreateSchema stored procedure to generate a schema for the XPaths you specify. Below are the stored procedure's inputs and outputs.

Input

Output

Example Stored Procedure Call

With DataModel set to FLATTENDOCUMENTS in the connection string, the example stored procedure call below results in a schema that flattens all the XPath arrays into a single table.

See Flattened Documents Model for more information on this data model.

EXECUTE CreateSchema TableName='GenPeople', 
FileLocation='C:\\tests\\scripts', 
URI='C:\\tests\\people.xml', 
XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance'

Next Steps

• Column Definitions: Change column names and data types or the XPath to the column value.
• SELECT Execution: Access the HTTP request -- for example, to implement server-side searches.
• INSERT Execution: Enable inserts by mapping inputs from the INSERT statement to an HTTP request.
• UPDATE Execution: Enable updates by mapping the updated column values to an HTTP request.
• DELETE Execution: Enable deletes by mapping the primary key to an HTTP request.

The basic attributes of a column are the name of the column, the data type, whether the column is a primary key, and the XPath. The Sync App uses the XPath to extract nodes from hierarchical data.

Mark up column attributes in the api:info block of the schema file. Set the XPath in the other:xPath property, as shown in the example below. You can also provide a description of each attribute using the desc property.

<api:info title="Persons" desc="Parse the OData Persons feed.">
    <attr name="ID"           xs:type="int" key="true" readonly="false"   other:xPath="content/properties/ID" desc="The ID associated with an individual person." />
    <attr name="EmployeeID"   xs:type="int"            readonly="true"    other:xPath="content/properties/EmployeeID" desc="The employee ID associated with the person." />
    <attr name="Name"         xs:type="string"         readonly="false"   other:xPath="content/properties/Name" desc="The name of the person." />
    <attr name="TotalExpense" xs:type="double"         readonly="true"    other:xPath="content/properties/TotalExpense" desc="The total experience associated with the person." />
    <attr name="HireDate"     xs:type="datetime"       readonly="true"    other:xPath="content/properties/HireDate" desc="The hire date of the person." />
    <attr name="Salary"       xs:type="int"            readonly="true"    other:xPath="content/properties/Salary" desc="The salary of the person." />
  </api:info>

Defining Column XPaths

The other:xPath property is used to specify the XPath that selects the column's value.

Absolute paths start with a '/' and contain the full XPath to the nested data.

<attr name="ID"           xs:type="int" key="true" other:xPath="/feed/entry/content/properties/ID" desc="The ID associated with the person." /> 

Indexed XPath values can also be used to specify an element within the document in the case that multiple elements with the same name are nested at the same level. The indexes are zero based.

<api:info>
  <attr name=PhoneNumber1 xs:type="string" other:xPath="Person/PhoneNumber[0]" desc="The person's phone number." />
  <attr name=PhoneNumber2 xs:type="string" other:xPath="Person/PhoneNumber[1]" desc="The person's phone number." />
</api:info>

Notes:

• XPaths and column names (when used to generate the XPath) are case sensitive.
• Any paths specified outside an XPath will only be retrieved if they are found prior to a row (XPath) being found, with the exception of the last row pushed (which will push all found paths). This is due to each row being pushed in a streaming fashion.

Defining Row XPaths

A row XPath specifies the path to an element that repeats at the same height -- an object array that the Sync App splits into rows. With DataModel set to FlattenedDocuments or Relational, you can specify more than one XPath in a semicolon-separated list. See Parsing Hierarchical Data for guides to these data modeling strategies.

The connection string can define the XPath property or you can define the row XPath as an attribute of an individual schema. Use the api:set keyword to define the row XPaths for a schema. With DataModel set to FlattenedDocuments or Relational, you can specify more than one XPath in a semicolon-separated list.

<api:set attr="XPath" value="/root/people;/root/people/vehicles;/root/people/vehicles/maintenance" /> 

A wildcard XPath can also be used and is helpful in the case that the XPaths are all at the same height but contain different names:

<api:set  attr="XPath" value="/feed/*" />

Defining Column Value Formats

You can set the other:valueFormat property to "aggregate" to identify a column as an aggregate.

• The aggregate option will return the aggregate found at the XPath specified. For example, consider the following:
  

      <repeat>
      <name>TestAggregate</name>
      <myobjcol1>
        <object1>myData</object1>
        <object1>myData1</object1>
        <object2>myData2</object2>
      </myobjcol1>
      </repeat>
      

  The following example extracts the myobjcol1 element to a column named MyObjCol1:
  

      <api:info>
        <attr name="MyObjCol1" xs:type="string" other:xPath="myobjcol1" other:valueFormat="aggregate" desc="An object column." />
      </api:info>
      <api:set attr="XPath" value="/repeat"/>
      

  This column value will be the following:
  

      <myobjcol1>
        <object1>myData</object1>
        <object1>myData1</object1>
        <object2>myData2</object2>
      </myobjcol1>
      

Mapping SELECT criteria to query parameters

Some APIs will support filtering the results of a request by specifying a query parameter. If this parameter maps to a WHERE clause, you can use the other:filter property to program this mapping.

• other:filter is a semicolon separated list of <parameter name>:<operator list>. <parameter name> is the name of the query parameter and <operator list> is a comma-separated list of operators used for the mapping. Valid operators are <, <=, =, >, >=, and LIKE.

The below example is for two query parameters, 'modifiedSince' and 'modifiedBefore', which filter the results based on the 'modifiedAt' column.

    <attr name="ModifiedAt"           xs:type="datetime" readonly="false"   other:xPath="content/properties/modifiedAt" other:filter="modifiedBefore:<;modifiedSince:>,>=,=" desc="Datetime when last modified." />
    

This example would take the query statement

SELECT * 
FROM <table> 
WHERE modifedAt < '<datetime>'

When a SELECT query is issued, the Sync App executes the GET method of the schema. The GET method invokes the Sync App's built-in operations to process REST. Invoking the GET method gives you control over the request for data.

The following procedures show several ways to exert control over a request for data, including:

• using SELECT WHERE to search the remote server-side data;
• using LIMIT to limit the results returned by the server; and
• implementing paging.

Query Processing

By default, the Sync App processes the query client-side. All you need to set to execute any SELECT statement locally are the XPath and URI connection properties.

(It is also possible for the Sync App to offload supported queries to the server while processing the rest of the query client side. Pushing these filters to the server helps optimize "later" stages of the query, such as LIMIT, JOIN, GROUP BY, and ORDER BY.)

Execute Selects to REST

The following steps describe how to build a script that enables you to execute SQL-92 queries, processed client side. Before you invoke a data processing operation, you must provide the URI and, optionally, the XPath. Use the api:set keyword to declare these attributes.

1. Set the URI attribute to a local file or an HTTP-accessible address.
   

   <api:set  attr="uri"                      value="NorthwindOData.xml" /> 

2. If needed, set the XPath attribute to the XPath of the data that constitutes an individual row. By default, the Sync App scans the document to detect the rows (see Parsing Hierarchical Data).
   

   <api:set  attr="XPath" value="/feed/entry/" /> 

3. Invoke the operation in the GET method. Inside the script block, use the api:push keyword to invoke the operation. Specify the operation with the op parameter. This keyword pushes the results of processing to the schema's output.
   

   <api:script method="GET">
     <api:set attr="method" value="GET"/>
     <api:set attr="uri" value="[uri]?$format=atom"/>
     <api:call op="xmlproviderGet">
       <api:push />
     </api:call>
   </api:script>

Process SELECT WHERE on the Server

This section shows how to translate a SELECT WHERE statement into a search request to REST APIs. The procedure uses the following statement:

SELECT * 
FROM <table> 
WHERE modifedAt < '2017-10-10' AND modifedAt > '2017-09-01'

If the server supports this filter via query parameters, you can use the api:info collumn definition's other:filter property to specify the desired mapping. For the above query, we can use this property to map the modifiedAt < '<date>' filter to the query parameter that returns results that were modifed before a given date, and use the modifedAt > '<date>' filter to the query parameter that filters results that were modifed after that date.

The other:filter is a semicolon separated list with the format: <parameter name>:<operator list>, where:

• <parameter name> is the name of the query parameter, and
• <operator list> is a comma-separated list of operators used for the mapping.

Valid operators are <, <=, =, >, >=, and LIKE.

To perform this mapping, we use the following markup for the modifedAt column definition:

    <attr name="modifiedAt"           xs:type="datetime" readonly="false"   other:xPath="content/properties/modifiedAt" other:filter="modifiedBefore:<;modifiedSince:>" />
    

This query results in the following request:

[url]?modifedBefore=2017-10-10&modifedSince=2017-09-01

If your API filter is not passed in a query parameter, you must pass it in the script. For example, consider an API that filters by name by querying the /persons/{name}/data endpoint:

SELECT *
FROM Persons 
WHERE (Name = 'Fran Wilson') 

In the GET method of the schema, use the attributes of the _input item, one of the Items in API Script, to access the search criteria and build the HTTP data retrieval request. The corresponding script below builds the request.

The api:check element is useful for checking the existence of an attribute before attempting to access its value. A variety of Value Formatters are available to do transformations, like URL-encoding a string.

<api:script method="GET">
  <api:check attr="_input.Name">
    <api:set attr="uri" value="[uri]/[_input.name|urlencode]/data"/>
  </api:check>
</api:script>

Search with Pseudo Columns

If you want to build search criteria using inputs other than the columns returned in the results, you can specify a pseudo column in the WHERE clause.

For example, the Weather Underground API supports returning a forecast for a specified location. The Location itself is not part of the forecast data; it is specified in the request URI, as in the request below:

http://api.wunderground.com/api/{MyAPIKey}/hourly/q/{MyLocation}.xml 

For example, the following SQL query obtains a forcast for the zip code 27516:

SELECT * FROM Hourly WHERE Location="27516"

To implement the above query, add a Location pseudo columns as follows:

1. Add a Location input parameter to the column definitions in the api:info block. (Location is required; if it is not specified, Sync App returns an error.)
   

   <api:info>
     ...
     <input  name="Location"                 required="true"/>
   </api:info>

2. To build the URI, reference the _input item's Location attribute:
   

   <api:set attr='uri' value="http://api.wunderground.com/api/[_connection.APIKey]/hourly/q/[_input.Location].xml"/>

3. To make the request and process the response, invoke the operation:
   

   <api:script method="GET" >
     <api:push op="xmlproviderGet"/>
   </api:script> 

Implement Paging

To support automatic paging, add the 'Rows@Next' input to the list of columns in the api:info block.

<input name="rows@next" desc="Identifier for the next page of results. Do not set this value manually." />

<api:set attr="EnablePaging" value="TRUE" />

The driver supports four types of paging implementations automatically:

• Where the URL of the next page is returned in the response;
• When the query includes a parameter to specify your current page offset;
• When you send a page token in the query parameter of the next request; or
• When the query includes a parameter to specify the current page number.

We describe these implementations in the next few subsections.

Paging Via Next Page URL

When the service returns the URL for the next page in the response body or header, set the 'pageurlpath' attribute to the location of this data. If a value is present at this location, it is used to set the URL of the next request.

If the next page URL is passed in the response body, set 'pageurlpath' to the XPath of the element.

<api:set  attr="pageurlpath"                             value="/data/nextPage" /> 

If the next page URL is passed in the response header with a 'Link' header, you can prefix 'pageurlpath' with header: to denote its location.

<api:set  attr="pageurlpath"                             value="header:Link" /> 

Paging Via Record Offset

If the service provides a record offset query parameter to control paging, you can set the name of the offset query parameter, the name of the page size query parameter, and the page size to be passed. Note that if there is no parameter to control page size, the page size parameter does not need to be set. However, in that case, you must set pagesize to the default page size.

<api:set  attr="pageoffsetparam"                      value="offset" /> 
<api:set  attr="pagesizeparam"                        value="limit" /> 
<api:set  attr="pagesize"                             value="100" /> 

<api:set  attr="pageoffsetparam"  value="offset" />

URI?offset=0<OtherParams>

<api:set  attr="pageoffsetparam"   value="offset;1" />

URI?offset=1<OtherParams>

Paging Via Page Number

Similar to record offset, if the service provides a query parameter that sets the page number, you can set the name of the page number query parameter, the name of the page size query parameter, and the page size to be passed. Note that if there is no parameter to control page size, the page size parameter does not need to be set. However, in that case, you must set pagesize to the default page size.

<api:set  attr="pagenumberparam"                      value="page" /> 
<api:set  attr="pagesizeparam"                        value="pagesize" /> 
<api:set  attr="pagesize"                             value="100" /> 

Paging Via Token

In cases where a token is returned in the response body, the token should be passed to a paging parameter in the subsequent request via the 'pagetokenparam' and 'pagetokenpath' attributes. 'pagetokenpath' should be set to the XPath of the element.

Some services also send a variable that denotes whether or not there are more pages, if that is the case, you can also set the 'hasmorepath' attribute to its XPath.

If the page token is passed in a query parameter, set 'pagetokenparam' to the name of the parameter.

<api:set  attr="pagetokenpath"                        value="/data/token" /> 
<api:set  attr="hasmorepath"                          value="/data/has_more" /> 
<api:set  attr="pagetokenparam"                       value="nextpagetoken" /> 

In cases where the token needs to be passed in the request body, set 'pagetokenparam' to its XPath.

<api:set  attr="pagetokenpath"                        value="/request/nextpagetoken" /> 

Other Paging Types

If your API does not follow any of the paging pattersn just discussed, you need a custom paging implementation. Set the information needed from the first page in the 'Rows@Next' attribute. When the 'Rows@Next' value is set in the output, the Sync App automatically calls the method again with the 'Rows@Next' value in the input, after all results for this page are returned.

You can use the value of this input to modify the request on the next pass, to get the next page of data. Set the Rows@Next input to any information needed to make the request for the next page of data.

For example, if your API returns the next page's URL in the response, you can obtain that value by providing the XPath to the URL:

<api:set  attr="elementmappath#"  value="/next_page" />
<api:set  attr="elementmapname#"  value="rows@next" /> 

<api:check attr="_input.rows@next">
  <api:set  attr="uri"  value="[_input.rows@next]" />
  <api:else>
    <api:set  attr="uri"  value="<first page's URL>" />
  </api:else>
<api:check> 

Process Other SELECT Statements Server Side

You can build any HTTP request in the GET method. Use the _query item to access other components of the SELECT query. The following table shows the attributes of the GET method _query item that describe the query that was issued to the Sync App:

SELECT Id, Name FROM Accounts WHERE City LIKE '%New%' AND COUNTRY = 'US' GROUP BY CreatedDate ORDER BY Name LIMIT 10,50;

City LIKE '%New%' AND COUNTRY = 'US'

Process LIMIT on the Server

If your API supports it, you can implement the LIMIT clause to restrict the number of results that need to be retrieved from the server.

When you build the API request, reference the value of the _query item's limit attribute. In the OData API, you can specify a limit using the $top query string parameter, shown below.

http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$top=10

Below is the corresponding script:

<api:check attr="_query.limit">
  <api:set  attr="uri"      value="http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$top=[_query.limit]" />
</api:check> 

Keyword Reference

For more information on the keywords used in this section, see the API Script Reference:

• api:script
• api:call
• api:push
• api:info
• api:check
• api:set

When an INSERT statement is executed, the Sync App executes the POST method of the schema, where you can build the HTTP insert request.

Execute Inserts to REST

In the POST method, the _input item, one of the Items in API Script, contains the columns to insert. For example, consider the following statement:

INSERT INTO Persons
(Name, Id)
VALUES
('Maria Anders','7') 

You can use the _input item's attributes to set these column values in the HTTP insert request. In the OData API, this is an HTTP POST. The POST data to make the preceding insert in the OData API is below:

<entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
  <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
  <content type="application/xml">
    <m:properties>
      <d:Name m:type="Edm.String">Maria Anders</d:Name>
      <d:ID m:type="Edm.Int32">7</d:ID>
    </m:properties>
  </content>
</entry>

After validating that the needed attributes exist, you can set the column values in the POST data. Set the "data" attribute to the POST data -- the body of the api:set keyword is convenient for setting long or multiline values like this.

The api:call keyword invokes the operation that will make the HTTP request. Before invoking the operation, use api:set to set the method attribute to the HTTP method you want within the scope of the api:script keyword.

<api:script method="POST">
    <api:set attr="method" value="POST"/>
    <api:validate attr="_input.Name" desc="Name and Id are required to insert." />
    <api:validate attr="_input.Id" desc="Name and Id are required to insert." />
    <api:set attr="data">
      <entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
        <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
        <content type="application/xml">
          <m:properties>
            <d:Name m:type="Edm.String">[_input.Name]</d:Name>
            <d:ID m:type="Edm.Int32">[_input.Id]</d:ID>
          </m:properties>
        </content>
      </entry>
    </api:set>
    <api:call op="xmlproviderGet"/>
  </api:script>
  

Access Components of INSERT Statements

In the POST method, the _query item contains the following attributes:

INSERT INTO Account (account_name, account_type) VALUES ('Contoso','Company')

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

• api:script
• api:set
• api:validate
• api:call

When an UPDATE statement is executed, the Sync App executes the MERGE method of the schema, where you can build the HTTP update request.

To see this method in a complete example, refer to Customizing Schemas.

Execute Updates to REST

In the MERGE method, the _input item, one of the Items in API Script, contains the columns to update. For example, consider the following statement:

UPDATE Persons 
SET Name='Ana Trujilo'
WHERE Id = '7' 

You can use the _input item's attributes to set these column values in the HTTP update request. In the OData API, this can be an HTTP PUT or PATCH. The PUT data to make the preceding update in the OData API is below:

<entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
  <id>http://services.odata.org/V4/OData/%28S%28tjlj1hwyun3kt2iv0ef21c2d%29%29/OData.svc//Persons(ID=4)</id>
  <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
  <content type="application/xml">
    <m:properties>
      <d:ID m:type="Edm.Int32">4</d:ID>
        <d:Name m:type="Edm.String">Ana Trujilo</d:Name>
    </m:properties>
  </content>
</entry>

After validating that the needed attributes exist, you can set the column values in the PUT data. Set the "data" attribute to the PUT data -- the body of the api:set keyword is convenient for setting long or multiline values like this.

The api:call keyword invokes the operation that will make the HTTP request. Before invoking the operation, use api:set to set the method attribute to the HTTP method you want within the scope of the api:script keyword.

<api:script method="MERGE">
  <api:set attr="method" value="PUT"/>
  <api:validate attr="_input.Id" desc="An Id is required to update." />
  <api:set attr="data">
    <entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
      <api:set attr="uri" value="[uri]([_input.Id])" />
      <id>[uri]</id>
      <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
      <content type="application/xml">
        <m:properties>
          <d:ID m:type="Edm.Int32">[_input.Id]</d:ID>
          <api:check attr="_input.Name">
            <d:Name m:type="Edm.String">[_input.Name]</d:Name>
          </api:check>
        </m:properties>
      </content>
    </entry>
  </api:set>
  <api:call op="xmlproviderGet"/>
</api:script>

Access the Components of UPDATE Statements

In the MERGE method, the _query item contains the following attributes:

UPDATE Account SET Name='John' WHERE Id = @myId

Id = @myId

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

• api:script
• api:set
• api:validate
• api:check
• api:call

When a DELETE statement is executed, the Sync App executes the DELETE method of the schema, where you can build the HTTP delete request.

To see this method in a complete example, refer to Customizing Schemas.

Execute Deletes to REST

In the DELETE method, the _input item, one of the Items in API Script, will contain the primary key of the record to delete. For example, consider the following statement:

DELETE FROM Persons WHERE Id = '7'

Below is the corresponding delete request in the OData API:

DELETE myapi.com/Persons(7)

In the corresponding DELETE method below, the required column, the primary key, is first validated and then used to modify the URI in an HTTP DELETE request:

<api:script method="DELETE">
  <api:set attr="method" value="DELETE"/>
  <api:validate attr="_input.Id" desc="An Id is required to delete." />
  <api:set attr="uri" value="[uri]([_input.Id])" />
  <api:call op="xmlproviderGet"/>
</api:script>
  

You can check that an input was provided and alert the user otherwise with the api:validate keyword. After validating that the primary key attribute exists, you can specify the primary key in the request URI. Use the api:set keyword to set the URI value.

The api:call keyword invokes the operation that will make the HTTP request. Before invoking the operation, set the method attribute to the HTTP method you want within the scope of the api:script keyword.

Access Components of DELETE Statements

In the DELETE script, the _query item contains the following attributes:

DELETE FROM Account WHERE Id = @myId

Id = @myId

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

• api:script
• api:set
• api:validate
• api:call

Below is the structure of the Persons XML document used in the custom schema examples. This data is a simplified version of the XML response from the odata.org Northwind test service. This service is used to demonstrate data manipulation operations to XML-based REST APIs.

 
<values odata.context="http://services.odata.org/V4/OData/OData.svc/$metadata#Persons">
  <value>
    <ID>0</ID>
    <Name>Paula Wilson</Name>
  </value>
  <value>
    <ID>1</ID>
    <Name>Jose Pavarotti</Name>
  </value>
  <value>
    <ID>2</ID>
    <Name>Art Braunschweiger</Name>
  </value>
  <value odata.type="#ODataDemo.Customer">
    <ID>3</ID>
    <Name>Liz Nixon</Name>
    <TotalExpense>99.99</TotalExpense>
  </value>
  <value odata.type="#ODataDemo.Customer">
    <ID>4</ID>
    <Name>Liu Wong</Name>
    <TotalExpense>199.99</TotalExpense>
  </value>
  <value odata.type="#ODataDemo.Employee">
    <ID>5</ID>
    <Name>Jaime Yorres</Name>
    <EmployeeID>10001</EmployeeID>
    <HireDate>2000-05-30T00:00:00Z</HireDate>
    <Salary>15000</Salary>
  </value>
  <value odata.type="#ODataDemo.Employee">
    <ID>6</ID>
    <Name>Fran Wilson</Name>
    <EmployeeID>10002</EmployeeID>
    <HireDate>2001-01-02T00:00:00Z</HireDate>
    <Salary>12000</Salary>
  </value>
</values>

Query Slicer logic will enable the Sync App to push down separate requests for each filter value using the IN clause.

This enables the Sync App to avoid client side filtering.

Setting up Query Slicer Logic

Open the RSD where Query Slicer logic will be implemented.

See the example apiscript below. This will need to be used with a restadoGet operation instead of an XML/JSON operation.

<api:info title="queryslicer" desc="Generated schema file." other:queryslicercolumn="flight_number2222" xmlns:other="http://apiscript.com/ns?v1">

<attr name="flight_number2222"                   xs:type="integer"  readonly="false"              other:xPath="/json/flight_number"   other:filter="{flight_number2222}"  />

<api:check attr="_input.flight_number2222">
<api:set attr="uri" value="[uri]?flight_number={flight_number2222}"/>

With this configuration, the following query will now dynamically pass down separate requests for each filter value:

SELECT * FROM launches WHERE flight_number2222 IN ('1', '2', '3')

SELECT * FROM launches WHERE flight_number2222 IN (SELECT flightId IN flights)

In order for this function to work, you need to set other:queryslicercolumn="flight_number2222" in the child table's RSD only.

Limits and Considerations

In a script, most of the places that access a sliced input will not return single elements. For example, if you do something like this within your GET block trying to access the sliced value of the ID field:

<api:set attr="URI" value="http://example.com/[_input.id]" />

This won't work and will give you back a URL containing the full SQL list (e.g. http://example.com/(1, 2, 3)) instead of just a single ID.

The only place in the script that you can use sliced IDs is in URIs, because the brace bits are expanded at a later stage where the sliced input is actually available:

<api:set attr="URI" value="http://example.com/{id}" />

Stored procedures are function-like interfaces to the data source that can be used to search, modify, or delete data. Stored procedures model actions that typically cannot be represented as SELECT, INSERT, UPDATE, or DELETE statements. Modeling stored procedures is a similar process to modeling tables in that you can use the same built-in data processing operations to implement stored procedures.

Stored Procedure Schemas vs. Table Schemas

With minor modifications, you can follow the same process to create a stored procedure that you would follow to add support for INSERT, UDPATE, and DELETE statements: define stored procedures in .rsb files, which, like .rsd files, consist of an info block and scripts that call data processing operations.

Instead of columns, the info block defines the input and output parameters of the stored procedure. Instead of the attr element, define inputs with the input element in the info block.

As with other SQL statements, when the stored procedure is executed, the _input item contains the input parameters. You can use the _input item to map the stored procedure inputs to the operation inputs.

As with table schemas, you can use the info block to process the response. Describe the outputs of a stored procedure with the output attribute in the info block. In the output attributes, you can specify the XPaths to extract nodes of hierarchical data.

Example Stored Procedure

The following stored procedure retrieves a Person record given their Id. This fully functional schema shows how to build the request and use XPaths to parse the response.

A stored procedure executes the GET method of the schema. Build the API request in this method: check that required inputs were provided and alert the user otherwise with the api:validate keyword. Use Value Formatters to simplify working with strings, dates, and math expressions.

Invoke the operation with the api:call keyword. To return data, insert the api:push keyword in the scope of the operation call.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="GetODataPersonById" description="Retrieves the OData Person specified by Id.">
    <output name="ID"            other:xPath="/feed/entry/content/properties/ID" />
    <output name="EmployeeID"    other:xPath="/feed/entry/content/properties/EmployeeID" />
    <output name="Name"          other:xPath="/feed/entry/content/properties/Name" />
    <output name="TotalExpense"  other:xPath="/feed/entry/content/properties/TotalExpense" />
    <output name="HireDate"      other:xPath="/feed/entry/content/properties/HireDate" />
    <output name="Salary"        other:xPath="/feed/entry/content/properties/Salary" />  
  </api:info>
  
  <api:set attr="uri" value="http://services.odata.org/V4/OData/(S(5cunewekdibfhpvoh21u2all))/OData.svc/Persons" /> 
  
  <!-- The XPath attribute of the schema splits the XML into rows based on elements that repeat at the same level. -->
  <api:set attr="XPath" value="/entry/" />

  <!-- See the xmlproviderGet page in the Operations subchapter to set any needed HTTP parameters. -->
  <api:set attr="ContentType"   value="application/atom+xml" />

  <!-- The GET method corresponds to EXECUTE. Within the script block, you can see the URI modified to append a query string parameter. The results of processing are pushed to the schema's output.  -->
  <api:script method="GET">
    <api:set attr="method" value="GET"/>
    <api:set attr="uri" value="[uri]([_input.Id])?$format=atom"/>
    <api:call op="xmlproviderGet">
      <api:push />
    </api:call>
  </api:script>
	
</api:script>

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

• api:script
• api:info
• api:validate
• api:set
• api:call
• api:push

The Sync App has high-performance operations for processing XML data sources. These operations are platform neutral: Schema files that invoke these operations can be used in both .NET and Java. You can also extend the Sync App with your own operations written in .NET or Java.

The Sync App has the following operations:

The jsonproviderGet operation is an APIScript operation that is used to process local JSON files or remote JSON data stores. It allows you to split JSON content into rows.

Required Parameters

• URI: The URI parameter specifies the location of the JSON content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://). The URI connection property can also provide this input.

• JSONPath: The JSONPath parameter is used to split the document into multiple rows. The Sync App will detect the paths to the rows when the DataModel property is set to FlattenedDocuments or Relational. See Parsing Hierarchical Data for a guide to configuring how the data is modeled.

  The XPath connection property can also provide the row XPaths. Specify the XPaths as absolute paths; define multiple paths in a semicolon-separated list.

  A wildcard JSONPath can also be used and is helpful in the case that the JSONPaths are all at the same height but contain different names:
  

  <api:set  attr="JSONPath" value="/feed/*" />

HTTP

The jsonproviderGet operation can be used to make HTTP requests to JSON data sources and process the results. It abstracts the complexity of connecting to JSON-based REST APIs but also gives you control over the layers involved, providing the following inputs to configure authentication, the HTTP request and headers, and firewall traversal.

Column Mapping

The jsonproviderGet operation reads the Column Definitions from the api:info section of the table schema file. In the column definition, the other:xPath property maps the column value to a JSON element.

Input Parameters

You can use api:set to specify the operation's input parameters, listed below. The connection properties also pass inputs to the operation; setting an attribute in the schema will override the connection property.

<api:set  attr="uri"                      value="NorthwindOData.json" /> 

Processing

• URI: The URI parameter specifies the location of the JSON content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://).

• JSONPath: The JSONPath parameter specifies the XPath to an array element that is used to split the document into rows. Specify multiple paths in a semicolon-separated list; see DataModel to configure the tables discovered based on these paths. By default the Sync App will detect the object arrays in the document as the rows -- see Automatic Schema Discovery to configure the row scan.

  A wildcard JSONPath can also be used and is helpful in the case that the JSONPaths are all at the same height but contain different names:
  

  <api:set  attr="JSONPath" value="/feed/*" />

HTTP

• Method: The HTTP method that corresponds to the SQL data manipulation statement. The allowed values are GET, POST, PUT, DELETE, and MERGE. The default value is GET.
• ContentType: The content type of the HTTP post. Relevant only if data is specified.
• Data: Data to include in the put or the post.
• Text: Input JSON text (an alternative to URI).
• Header:Name#: The name for each custom header to pass with the request.
• Header:Value#: The value for each custom header to pass with the request.
• ParamName#: The name for each parameter to pass with the request.
• ParamValue#: The value for each parameter to pass with the request.
• Cookie:*: Any cookies that should be added to the request.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• LogFile: The file where exchanged/transferred data is logged.
• PushResponseHeader#: The response headers which should be returned from this operation. Any headers added to this list are returned as header:* parameters.
• PushResponseCookie#: The response cookies which should be returned from this operation. Any cookies added to this list are returned as cookie:* parameters.

Authentication

• User: The username used for authentication.
• Password: The password used for authentication.
• AuthScheme: The authentication method to use. Only relevant if User and Password are provided. The allowed values are BASIC, DIGEST, NONE, NTLM, NEGOTIATE. The default value is BASIC.
• KerberosKDC: The KDC setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosRealm: The Realm setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosToken: The Kerberos token used for authentication.

OAuth

• Version: The OAuth version. The allowed values are DISABLED, 1.0, 2.0. The default value is DISABLED.
• Token: The access token for OAuth.
• Token_Secret: The access token secret. OAuth 1.0 only.
• Client_Id: The OAuth client Id. OAuth 1.0 only.
• Client_Secret: The OAuth client secret. OAuth 1.0 only.
• Sign_Method: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Other_Options: Other options to control the behavior of OAuth.

Proxy

• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, PROPRIETARY, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.

Firewall

• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, and SOCKS5. The default value is NONE.

Advanced Processing

• ElementMapPath#: The XPath to the subelement that you want to specify as columns within your chosen row. This can be relative to the item (for example, title) or absolute (for example, /json/@version).
• ElementMapName#: The name of the chosen ElementMapPath. The ElementMapName input can be used to give a name to the XPath chosen in ElementMapPath.
• ElementArrayMapPath#: The XPath to the element that you want to specify as an array.
• ElementArrayMapName#: The name of the chosen ElementArrayMapPath. The ElementArrayMapName input can be used to give a name to the XPath chosen in ElementArrayMapPath.
• AggregateFormat: The format to use with aggregate values. The allowed values are JSON, XML. The default value is JSON.
• AggregatePath#: The XPath to the element that you want to return as an aggregate.
• AggregateName#: The name of the chosen aggregate. The AggregateName input can be used to give a name to the XPath chosen in AggregatePath.
• ResultCodeXPath: The XPath of the result code element in the response.
• ResultSuccessCode: The success code of the value located at ResultCodeXPath that is used to verify and identify the request as being successful.
• ErrorMsgXPath: The XPath of the error message returned in the response.
• IndexParentNodes: Multiple occurrences of the same element will be indexed if this is set to true. If set to false, multiple occurrences will be combined into an array. The allowed values are TRUE, FALSE. The default value is TRUE.
• TrimStartingParentIndexes: Specifies whether to trim starting parent indexes. Only valid when IndexParentNodes is TRUE. Specifically useful when the JSONPath specifies multidimensional arrays. The allowed values are TRUE, FALSE. The default value is FALSE.
• PushEmptyArrayObjects: Specifies whether to push empty objects within multidimensional arrays. The allowed values are TRUE, FALSE. The default value is FALSE.
• PushPrefix: The prefix to be prepended to each item attribute that is pushed.
• EnablePaging: Specifies whether the Rows@Next input will be used for paging. The allowed values are TRUE, FALSE. The default value is FALSE.
• OutputPrefix: Whether the prefix should be output with each pushed attribute of an item. The allowed values are TRUE, FALSE. The default value is FALSE.
• PushOuterValueRow: Whether to generate special rows for paths outside of the repeat element when no other rows would contain them. If one of these rows is generated it will contain the values of all mapped paths outside the repeat element, along with a special attribute called @isoutervalue which is set to true. However, if there is a row in the repeat element which contains these values then the @isoutervalue is not pushed.

  The allowed values are TRUE, FALSE. The default value is FALSE.

Output Parameters

• *: Objects found.
• header:*: Any headers requested from the PushResponseHeader# input (e.g. adding "Content-Type" to PushResponseHeader# will return "header:Content-Type")
• cookie:*: Any cookies requested from the PushResponseCookie# input (e.g. adding "session" to PushResponseCookie# will return "cookie:session")

<api:call op="jsonproviderGet" out="output">
  <api:map from="output" to="temp" map="* = *" />
  <api:push item="temp" />
</api:call >

The flexible layout has different restrictions to be aware of:

• It is slightly slower than the optimized layout.
• It does not support column names containing a period, so you will want to set the hidden PathDelimiter property to an underscore and access flattened columns using names like a_b_c instead of a.b.c.

The xmlproviderGet operation is an APIScript operation that is used to process local and remote XML content. It allows you to split XML content into rows.

Required Parameters

• URI: The URI parameter specifies the location of the XML content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://). The URI connection property can also provide this input.

• XPath: The XPath parameter is used to split the document into multiple rows. The Sync App will detect the row XPaths in the document when the DataModel property is set to FlattenedDocuments or Relational. See Parsing Hierarchical Data for a guide to configuring how the data is modeled.

  The XPath connection property can also provide the row XPaths. Specify the XPaths as absolute paths; define multiple paths in a semicolon-separated list.

  A wildcard XPath can also be used and is helpful in the case that the XPaths are all at the same height but contain different names:
  

  <api:set  attr="XPath" value="/feed/*" />

HTTP Operation

The xmlproviderGet operation can be used to make HTTP requests to XML data sources and process the results. It abstracts the complexity of connecting to XML-based REST APIs but also gives you control over the layers involved, providing the following inputs to configure authentication, the HTTP request and headers, and firewall traversal.

Column Mapping

The xmlproviderGet operation reads the Column Definitions from the api:info section of the table schema file. In the column definition, the other:xPath property maps the column value to an XML element.

Input Parameters

You can use api:set to specify the operation's input parameters, as shown below.

<api:set  attr="uri"                      value="NorthwindOData.xml" /> 

The connection properties also pass inputs to the operation; setting one of the following input attributes in the schema will override the connection property.

Processing

• URI: The URI parameter specifies the location of the XML content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://).

• XPath: The XPath parameter is used to split the document into multiple rows. The Sync App will detect the row XPaths in the document when the DataModel property is set to FlattenedDocuments or Relational. See Parsing Hierarchical Data for a guide to configuring how the data is modeled.

  You can also set the XPath connection property to delineate the paths to the tables. Specify absolute paths and define multiple paths in a semicolon-separated list.

  A wildcard XPath can also be used and is helpful in the case that the XPaths are all at the same height but contain different names:
  

  <api:set  attr="XPath" value="/feed/*" />

HTTP

• Method: The HTTP method that corresponds to the SQL data manipulation statement. The allowed values are GET, POST, PUT, DELETE, and MERGE. The default value is GET.
• ContentType: The content type of the HTTP post. Relevant only if data is specified.
• Data: Data to include in the put or the post. To post a file use a file:// URI.
• Text: Input XML text (an alternative to URI).
• Header:Name#: The name for each custom header to pass with the request.
• Header:Value#: The value for each custom header to pass with the request.
• ParamName#: The name for each parameter to pass with the request.
• ParamValue#: The value for each parameter to pass with the request.
• Cookie:*: Any cookies that should be added to the request.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• LogFile: The file where exchanged/transferred data is logged.
• PushResponseHeader#: The response headers which should be returned from this operation. Any headers added to this list are returned as header:* parameters.
• PushResponseCookie#: The response cookies which should be returned from this operation. Any cookies added to this list are returned as cookie:* parameters.

Authentication

• User: The username used for authentication.
• Password: The password used for authentication.
• AuthScheme: The authentication method to use. Only relevant if User and Password are provided. The allowed values are BASIC, DIGEST, NONE, NTLM, NEGOTIATE. The default value is BASIC.
• KerberosKDC: The KDC setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosRealm: The Realm setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosToken: The Kerberos token used for authentication.

OAuth

• Version: The OAuth version. The allowed values are DISABLED, 1.0, 2.0. The default value is DISABLED.
• Token: The access token for OAuth.
• Token_Secret: The access token secret. OAuth 1.0 only.
• Client_Id: The OAuth client Id. OAuth 1.0 only.
• Client_Secret: The OAuth client secret. OAuth 1.0 only.
• Sign_Method: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Other_Options: Other options to control the behavior of OAuth.

Proxy

• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, PROPRIETARY, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.

Firewall

• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, and SOCKS5. The default value is NONE.

Advanced Processing

• ElementMapPath#: The XPath to the subelement that you want to specify as columns within your chosen row. This can be relative to the item (for example, title) or absolute (for example, /rss/@version).
• ElementMapName#: The name of the chosen ElementMapPath. The ElementMapName input can be used to give a name to the XPath chosen in ElementMapPath.
• ElementArrayMapPath#: The XPath to the element that you want to specify as an array.
• ElementArrayMapName#: The name of the chosen ElementArrayMapPath. The ElementArrayMapName input can be used to give a name to the XPath chosen in ElementArrayMapPath.
• AggregateFormat: The format to use with aggregate values. The allowed values are JSON, XML. The default value is XML.
• AggregatePath#: The XPath to the element that you want to return as an aggregate.
• AggregateName#: The name of the chosen aggregate. The AggregateName input can be used to give a name to the XPath chosen in AggregatePath.
• ResultCodeXPath: The XPath of the result code element in the response.
• ResultSuccessCode: The success code of the value located at ResultCodeXPath that is used to verify and identify the request as being successful.
• ErrorMsgXPath: The XPath of the error message returned in the response.
• IndexParentNodes: Multiple occurrences of the same element will be indexed if this is set to true. If set to false, multiple occurrences will be combined into an array. The allowed values are TRUE, FALSE. The default value is TRUE.
• PushPrefix: The prefix to be prepended to each item attribute that is pushed.
• EnablePaging: Specifies whether the Rows@Next input will be used for paging. The allowed values are TRUE, FALSE. The default value is FALSE.
• OutputPrefix: Whether the prefix should be output with each pushed attribute of an item. The allowed values are TRUE, FALSE. The default value is FALSE.
• PushOuterValueRow: Whether to generate special rows for paths outside of the repeat element when no other rows would contain them. If one of these rows is generated it will contain the values of all mapped paths outside the repeat element, along with a special attribute called @isoutervalue which is set to true. However, if there is a row in the repeat element which contains these values then the @isoutervalue is not pushed.

  The allowed values are TRUE, FALSE. The default value is FALSE.

Output Parameters

• *: The elements and the attributes found within each item element.
• header:*: Any headers requested from the PushResponseHeader# input (e.g. adding "Content-Type" to PushResponseHeader# will return "header:Content-Type")
• cookie:*: Any cookies requested from the PushResponseCookie# input (e.g. adding "session" to PushResponseCookie# will return "cookie:session")

<api:call op="xmlproviderGet" out="output">
  <api:map from="output" to="temp" map="* = *" />
  <api:push item="temp" />
</api:call>

The csvproviderGet operation is an API Script operation that is used to process CSV content. It allows you to split CSV content into rows.

Required Parameters

• URI: The URI parameter specifies the location of the CSV content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://).

Network Operation

The csvproviderGet operation can be used to execute remote data retrieval operations. It abstracts the request and also enables configuration of most aspects through the following inputs, including authentication and firewall traversal. See ProxyAuthScheme and FirewallType the properties needed to negotiate a firewall.

Column Mapping

The csvproviderGet operation reads the api:info section of the table schema file to map various elements in the CSV document into column values within a row. It does so using the other:internalname property of the column definition.

HTTP

• Method: The HTTP method that corresponds to the SQL data manipulation statement. The allowed values are GET, POST, PUT, DELETE, and MERGE. The default value is GET.
• ContentType: The content type of the HTTP post. Relevant only if data is specified.
• Data: Data to include in the put or the post.
• Text: Input CSV text (an alternative to URI).
• Header:Name#: The name for each custom header to pass with the request.
• Header:Value#: The value for each custom header to pass with the request.
• ParamName#: The name for each parameter to pass with the request.
• ParamValue#: The value for each parameter to pass with the request.
• Cookie:*: Any cookies that should be added to the request.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• LogFile: The file where exchanged/transferred data is logged.

Authentication

• User: The username used for authentication.
• Password: The password used for authentication.
• AuthScheme: The authentication method to use. Only relevant if User and Password are provided. The allowed values are BASIC, DIGEST, NONE, NTLM, NEGOTIATE. The default value is BASIC.
• KerberosKDC: The KDC setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosRealm: The Realm setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosToken: The Kerberos token used for authentication.

OAuth

• Version: The OAuth version. The allowed values are DISABLED, 1.0, 2.0. The default value is DISABLED.
• Token: The access token for OAuth.
• Token_Secret: The access token secret. OAuth 1.0 only.
• Client_Id: The OAuth client Id. OAuth 1.0 only.
• Client_Secret: The OAuth client secret. OAuth 1.0 only.
• Sign_Method: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Other_Options: Other options to control the behavior of OAuth.

Proxy

• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, PROPRIETARY, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.

Firewall

• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, and SOCKS5. The default value is NONE.

The oauthGetAccessToken operation is an APIScript operation that is used to facilitate the OAuth authentication and refresh flows.

The Sync App includes stored procedures that invoke this operation to complete the OAuth exchange. The following example schema briefly lists some of the typically required inputs before the following sections explain them in more detail.

See Defining Stored Procedures for more information on working with custom stored procedures. For a guide to using the Sync App to authenticate, see the "Getting Started" chapter.

Creating a GetOAuthAccessToken Stored Procedure

Invoke the oauthGetAccessToken with the GetOAuthAccessToken stored procedure. The following inputs are required for most data sources and will provide default values for the connection properties of the same name.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="GetOAuthAccessToken"   description="Obtains the OAuth access token to be used for authentication with various APIs."                                                         >
    <input  name="AuthMode"               desc="The OAuth flow. APP or WEB."                                                                                                                    />
    <input  name="CallbackURL"            desc="The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. " />
    <input  name="OAuthAccessToken"       desc="The request token. OAuth 1.0 only."                                                                                                             />
    <input  name="OAuthAccessTokenSecret" desc="The request token secret. OAuth 1.0 only."                                                                                                      />
    <input  name="Verifier"               desc="The verifier code obtained when the user grants permissions to your app."                                                                       />

    <output name="OAuthAccessToken"       desc="The access token."                                                                                                                              />
    <output name="OAuthTokenSecret"       desc="The access token secret."                                                                                                                       />
    <output name="OAuthRefreshToken"      desc="A token that may be used to obtain a new access token."                                                                                         />
 </api:info>

  <!-- Set OAuthVersion to 1.0 or 2.0. -->
  <api:set attr="OAuthVersion"                                                    value="MyOAuthVersion"                 />
  <!-- Set RequestTokenURL to the URL where the request for the request token is made. OAuth 1.0 only.-->
  <api:set attr="OAuthRequestTokenURL"                                            value="http://MyOAuthRequestTokenURL" />
  <!-- Set OAuthAuthorizationURL to the URL where the user logs into the service and grants permissions to the application. -->
  <api:set attr="OAuthAuthorizationURL"                                           value="http://MyOAuthAuthorizationURL" />
  <!-- Set OAuthAccessTokenURL to the URL where the request for the access token is made. -->
  <api:set attr="OAuthAccessTokenURL"                                             value="http://MyOAuthAccessTokenURL"   />
  <!-- Set GrantType to the authorization grant type. OAuth 2.0 only. -->
  <api:set attr="GrantType"                                                       value="CODE"                           />
  <!-- Set SignMethod to the signature method used to calculate the signature of the request. OAuth 1.0 only.-->
  <api:set attr="SignMethod"                                                      value="HMAC-SHA1"                      />
  <api:call op="oauthGetAccessToken">
    <api:push/>
  </api:call>
  
</api:script>

Writing the RefreshOAuthAccessToken Stored Procedure

You can also use oauthGetAccessToken to refresh the access token by providing the following inputs:

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="RefreshOAuthAccessToken" description="Refreshes the OAuth access token used for authentication." >
    <input  name="OAuthRefreshToken"        desc="A token that may be used to obtain a new access token."           />

    <output name="OAuthAccessToken"         desc="The authentication token returned."                               />
    <output name="OAuthTokenSecret"         desc="The authentication token secret returned. OAuth 1.0 only."        />
    <output name="OAuthRefreshToken"        desc="A token that may be used to obtain a new access token."           />
    <output name="ExpiresIn"                desc="The remaining lifetime on the access token."                      />

  </api:info>

  <!-- Set OAuthVersion to 1.0 or 2.0. -->
  <api:set attr="OAuthVersion"                                                    value="MyOAuthVersion"                 />
    <!-- Set GrantType to REFRESH. OAuth 2.0 only. -->
    <api:set attr="GrantType"            value="REFRESH" />
    <!-- Set SignMethod to the signature method used to calculate the signature of the request. OAuth 1.0 only.-->
    <api:set attr="SignMethod"           value="HMAC-SHA1" />
    <!-- Set OAuthAccessTokenURL to the URL where the request for the access token is made. -->
    <api:set attr="OAuthAccessTokenURL"  value="http://MyOAuthAccessTokenURL" />
    <!-- Set AuthMode to 'WEB' when calling RefreshOAuthAccessToken -->
    <api:set attr="AuthMode" value="WEB"/>
  <api:call op="oauthGetAccessToken">
    <api:push/>
  </api:call>
  
</api:script>

Input Parameters

• OAuthVersion: The OAuth version. The allowed values are 1.0, 2.0. The default value is 1.0.
• AuthMode: The OAuth flow. OAuth 2.0 only. If you choose the App mode, this operation will launch your browser and prompt you to authenticate with your account credentials. Set this parameter to WEB to authenticate a Web app or if the Sync App is not allowed to open a Web browser. The default value is APP.
• OAuthRequestTokenURL: The URL where the Sync App makes a request for the request token. OAuth 1.0 only. Required for OAuth 1.0.
• OAuthAuthorizationURL: The URL where the user logs into the service and grants permissions to the application. In OAuth 1.0, if permissions are granted the request token is authorized.
• OAuthAccessTokenURL: The URL where the request for the access token is made. In OAuth 1.0, the authorized request token is exchanged for the access token.
• CallbackURL: The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. This value must match the callback URL you specify when you register an app. Note that your data source may additionally require the port.
• OAuthClientId: The client Id obtained when you register an app. Also called a consumer key.
• OAuthClientSecret: The client secret obtained when you register an app. Also called a consumer secret.
• OAuthAccessToken: The request token. OAuth 1.0 only.
• OAuthAccessTokenSecret: The request token secret. OAuth 1.0 only.
• OAuthRefreshToken: A token that may be used to obtain a new access token.
• GrantType: Authorization grant type. OAuth 2.0 only. The allowed values are CODE, PASSWORD, CLIENT, REFRESH. The default value is CODE.
• Verifier: The verifier code obtained when the user grants permissions to the Sync App. In the OAuth 2.0 code grant type, the verifier code is located in the code query string parameter of the callback URL. In OAuth 1.0, the verifier is located in the oauth_verifier query string parameter of the callback URL.
• SignMethod: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Cert: Path for the PFX personal certificate file. OAuth 1.0 only.
• CertPassword: Personal certificate password. OAuth 1.0 only.
• OtherOptions: Other options to control the behavior of OAuth.
• OAuthParam:*: Other parameters.
• PostData: The HTTP POST data.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• LogFile: Specifies a file where the request and response are logged.
• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, PROPRIETARY, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, SOCKS5. The default value is NONE.
• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.

Output Parameters

• OAuthAccessToken: The access token.
• OAuthTokenSecret: The access token secret.
• OAuthRefreshToken: A token that may be used to obtain a new access token.
• ExpiresIn: The remaining lifetime on the access token.
• OAuthParam:*: Other parameters sent from the server.

The oauthGetUserAuthorizationURL is an APIScript operation that is used to facilitate the OAuth authentication flow for Web apps, for offline apps, and in situations where the Sync App is not allowed to open a Web browser. To pass the needed inputs to this operation, define the GetOAuthAuthorizationURL stored procedure. The Sync App can call this internally.

Define stored procedures in .rsb files with the same file name as the schema's title. The example schema briefly lists some of the typically required inputs before the following sections explain them in more detail.

For a guide to authenticating in the OAuth flow, see the "Getting Started" chapter. You can find more information about stored procedures and how to write them in Defining Stored Procedures.

Writing the GetOAuthAuthorizationURL Stored Procedure

Call oauthGetUserAuthorizationURL in the GetOAuthAuthorizationURL stored procedure.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="Get OAuth Authorization URL" description="Obtains the OAuth authorization URL used for authentication with various APIs."                                                          >
    <input  name="CallbackURL"                  desc="The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. " />

    <output name="URL"                          desc="The URL where the user logs in and is prompted to grant permissions to the app. "                                                               />
    <output name="OAuthAccessToken"             desc="The request token. OAuth 1.0 only."                                                                                                             />
    <output name="OAuthTokenSecret"             desc="The request token secret. OAuth 1.0 only."                                                                                                      />
  </api:info>

  <!-- Set OAuthVersion to 1.0 or 2.0. -->
  <api:set attr="OAuthVersion"          value="MyOAuthVersion"                 />
  <!-- Set ResponseType to the desired authorization grant type. OAuth 2.0 only.-->
  <api:set attr="ResponseType"           value="code"                           />
  <!-- Set SignMethod to the signature method used to calculate the signature. OAuth 1.0 only.-->
  <api:set attr="SignMethod"            value="HMAC-SHA1"                      />
  <!-- Set OAuthAuthorizationURL to the URL where the user logs into the service and grants permissions to the application. -->
  <api:set attr="OAuthAuthorizationURL"  value="http://MyOAuthAuthorizationURL" />
  <!-- Set OAuthAccessTokenURL to the URL where the request for the access token is made. -->
  <api:set attr="OAuthAccessTokenURL"   value="http://MyOAuthAccessTokenURL"/>
  <!-- Set RequestTokenURL to the URL where the request for the request token is made. OAuth 1.0 only.-->
  <api:set attr="OAuthRequestTokenURL"   value="http://MyOAuthRequestTokenURL"       />
  <api:call op="oauthGetUserAuthorizationUrl">
    <api:push/>
  </api:call>
  
</api:script>

<p>

Input Parameters

• OAuthVersion: The OAuth version. The allowed values are 1.0, 2.0. The default value is 1.0.
• OAuthAuthorizationURL: The URL where the user logs into the service and grants permissions to the application. In OAuth 1.0, if permissions are granted the request token is authorized.
• OAuthRequestTokenURL: The URL where the Sync App makes a request for the request token. OAuth 1.0 only. Required for OAuth 1.0.
• CallbackURL: The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. This value must match the callback URL you specify when you register an app. Note that your data source may additionally require the port. The default value is http://127.0.0.1/.
• OAuthClientId: The client Id. Also called a consumer key.
• OAuthClientSecret: The client secret. Also called a consumer secret.
• ResponseType: The desired authorization grant type. OAuth 2.0 only. The allowed values are CODE, IMPLICIT. The default value is CODE.
• SignMethod: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, RSA-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Cert: Path for the personal certificate PFX file. OAuth 1.0 only.
• CertPassword: Personal certificate password. OAuth 1.0 only.
• OtherOptions: Other options to control the behavior of OAuth.
• OAuthParam:*: Other parameters. OAuth 1.0 only.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, PROPRIETARY, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, SOCKS5. The default value is NONE.
• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.

Output Parameters

• URL: The URL where the user logs in and is prompted to grant permissions to the app. In OAuth 1.0, if permissions are granted the request token is authorized.
• OAuthAccessToken: The request token. OAuth 1.0 only.
• OAuthTokenSecret: The request token secret. OAuth 1.0 only.
• OAuthParam:*: Other parameters sent from the server. OAuth 1.0 only.

The utiladoRefreshOAuth operation causes the Sync App to refresh any OAuth tokens that are close to expiring. This has no effect when AuthScheme is set to a non-OAuth authentication mode .

The operation takes no parameters and may be called like this:

<api:call op="utiladoRefreshOAuth" />

The utiladoSleep operation causes the process that calls it to suspend execution for a specifed duration.

The required parameter is timeout. The units are in seconds. For example,

<api:call op="utiladoSleep?timeout=2" />

This section details a selection of advanced features of the REST Sync App.

User Defined Views

The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to REST and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Client SSL Certificates

The REST Sync App also supports setting client certificates. Set the following to connect using a client certificate.

• SSLClientCert: The name of the certificate store for the client certificate.
• SSLClientCertType: The type of key store containing the TLS/SSL client certificate.
• SSLClientCertPassword: The password for the TLS/SSL client certificate.
• SSLClientCertSubject: The subject of the TLS/SSL client certificate.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Sync App provides standards-based access to REST by modeling local and remote data as relational tables, views, and stored procedures. CData has its own configuration language called API Script that you can use to mark up schemas. API Script hides the complexity of crafting requests to REST and parsing the feeds returned.

However, API Script is also a high-level programming language that enables you to control almost every aspect of data access and processing. Your schema is a script interpreted by the API Script engine.

You use keywords, attributes, items, operations, and feeds to write scripts:

• Attribute: The name part of a name-value pair, as in attribute="address", value="123 Pleasant Lane".
• Item: A related group of attribute-value pairs that describe an input or output. For example:
  

  Attribute="name" value="Bob"
  Attribute="address" value="123 Pleasant Lane"
  Attribute="phone" value="123-4567"

• Feed: A list of items: for example, a list of customers with their addresses and phone numbers.
• Operation: A generic name for methods that accept items as input and produce feeds as output.
• Keyword: An API Script statement, like api:set.

• Project columns over a feed and split feed items into rows.
• Use high-level programming constructs such as if/else statements and case statements to control execution flow.
• Call operations and define your own.
• Create and modify items, the inputs to an operation, and feeds, the operation's result.
• Process the feed resulting from an operation call by iterating through its items.

Feeds are composed of items, but in API Script items themselves are used for much more than the individual parts of a feed. They are also used to represent inputs to operations.

Declare Items

In API Script items are created, named, and given attribute values through the api:set keyword:

<api:set item="input" attr="mask" value="*.txt" />

However, an item named "input" is never declared. Instead, the item is created the first time you try to set an attribute on it. In the example above, if the input item did not already exist, it would be created and the mask attribute would be set on it.

Select Attribute Values

To reference an attribute, use the syntax item.attribute (e.g., "input.mask"). To query an attribute value, surround the attribute name in square brackets ([]). This instructs the interpreter that you want to evaluate the string instead of interpreting it as a string literal.

For example, consider the following code snippet:

<api:set item="item1" attr="attr1" value="value1"/>
<api:set item="item1" attr="attr2" value="item1.attr1"/>
<api:set item="item1" attr="attr3" value="[item1.attr1]"/>

The results are the following:

• item1.attr1 gets assigned the literal string value "value1".
• item1.attr2 gets assigned the literal string value "item1.attr1".
• item1.attr3 gets assigned the value "value1", because the string "[item1.attr1]" gets evaluated at run time as the attr1 attribute of item1.

Default Item

In API Script there is always an implicit, unnamed item on an internal stack of items, the default item. In the following two cases, the default item is commonly used to make scripts shorter and easier to write:

• Calling operations: The default item is the item passed by default to the operation called by your script if no other item is specified. This means that you can use api:set to write attributes to the default unnamed item and those attributes will be passed as input to the next operation called in the script. This is one way to provide an operation's required parameters.
• Processing the current item in the output of an operation or script: If you do not specify a variable name for the result of an operation, then inside the api:call block that invokes the operation, the default item will refer to the current item produced by the operation.

<api:set attr="path" value="." />

Named Items

In addition to items declared within the script, several built-in items are available in the scope of a script. Built-in, or special, items are available in API Script that provide an interface for accessing the connection string and the SQL query. These special items are useful for mapping inputs to data processing operations.

The following sections detail the special items.

Script Inputs (_input)

The input for a script can be read from the _input item. The SQL statement provides the input for table and stored procedure scehmas: In a SELECT statement, the _input item contains the columns or pseudo columns specified in the WHERE clause.

When you read values from the default item in a script, you are reading values from _input; likewise, attributes that you write to the default item are passed as parameters to operations along with the input to the script. Only the variables defined in the info block or in the script will be available in the _input item.

Note that inside an api:call block _input is no longer the default item and you must reference it by name if you need access to it.

Script Outputs (_out[n])

The current item in the feed produced by the api:call keyword can be accessed through the default item or a named special item, "_outX", where X is the level of nesting of api:call keywords. For example, if you are inside a single api:call keyword, the item's name will be "_out1". If you are inside three levels of nested api:call keywords, then it will be _out3.

Connection String Properties (_connection)

The _connection item has the connection properties of the Sync App. The Sync App does not perform any validation of the connection string properties. It is left to the schema author to decide which properties are required, how they are used, etc.

In addition to providing access to the connection properties, the _connection item can be used to store pieces of data in a connection. For example, it may be necessary to store session tokens that can be reused while a connection lasts. You can use the api:set keyword to store any values, as shown in the code example below:

<api:set attr="_connection._token" value="[oauth.connection_token]"/>

Note: You can set only the attributes that start with the _ symbol. This is done so that the connection properties set by the user cannot be overriden.

SQL Clauses (_query)

The _query item has the following attributes that describe the query that was issued to the Sync App:

SELECT Id, Name FROM Accounts WHERE City LIKE '%New%' AND COUNTRY = 'US' GROUP BY CreatedDate ORDER BY Name LIMIT 10,50;

City LIKE '%New%' AND COUNTRY = 'US'

INSERT INTO Account(account_name, account_type) SELECT customer_name, customer_type FROM Customer#TEMP

[account_name], [account_type]

DELETE FROM Account WHERE EXISTS SELECT customer_name, customer_type FROM Customer#TEMP

[customer_name], [customer_type]

Value formatters enable you to generate new values with specific formatting. You can use value formatters to perform string, date, and math operations on values.

The general format for invoking formatters is

[ item.attribute | formatter(parameters) | formatter (parameters) | ...]

Examples

• In the following snippet any "*" character in the myid attribute's value is replaced by "-", and the resulting value is assigned to input1.id.
  

  <api:set attr="input1.id" value="[myid | replace('*', '-')]"/>

• Below, two value formatters are chained with the pipe ("|") character. In the example, only .log files are pushed from the operation.
  

  <api:call op="fileListDir">
    <api:check attr="name" value="[filename|tolower | endswith('.log')]">
      <api:push/>
    </api:check>
  </api:call>

[attr | allownull()]

Returns NULL if the attribute does not exist or the value if it does.

[attr | arrayfind(substring)]

Returns the index at which the string is found in the attribute array. The index is 1 based.

• searchstring: The string to search for in the original value.

[attr | base64decode()]

Converts the attribute value to a base 64 decoded string.

[attr | base64encode()]

Converts the attribute value to a base 64 encoded string.

[attr | capitalize()]

Returns the original attribute value with only its first character capitalized.

[attr | capitalizeall()]

Returns the original attribute value with the first character of all words capitalized.

[attr | center(integer_width[, character])]

Returns the attribute value centered in a string of width specified by the first parameter. Padding is done using the fillchar specified by the second parameter.

• width: The total width of the output string.
• character: The optional character used for padding. If not specified this defaults to a space.

[attr | contains(value[, ifcontains][, ifnotcontains])]

Returns true (or ifcontains) if the attribute value contains the parameter value, false (or ifnotcontains) otherwise.

• value: The string to find from the attribute value.
• ifcontains: The optional value returned if the attribute value contains the parameter value.
• ifnotcontains: The optional value returned if the attribute value does not contain the parameter value.

[attr | count(substring)]

Returns the number of occurrences in the attribute value of a substring specified by the first parameter.

• substring: The substring to search for in the attribute value.

[attr | currency([integer_count])]

Returns the numeric value formatted as currency.

• count: The optional number specifying how many places to the right of the decimal are displayed. The default is 2.

[attr | decimal([integer_count])]

Returns the numeric value formatted as a decimal number.

• count: The optional number that indicates how many places to the right of the decimal are displayed. Default is 2.

[attr | def([notexists][, exists])]

Checks for the existence of an attribute and returns the specified parameter value if it does not.

• notexists: The optional value to return if the attribute value does not exist.
• exists: The optional value to return if the original attribute exists. If not specified, the original value of the attribute is returned.

[attr | empty(value)]

Returns the specified value if the attribute value is empty, otherwise the original attribute value.

• value: The value that will be used if the attribute is empty.

[attr | endswith(substring[, iftrue][, iffalse])]

Determines whether the attribute value ends with the specified parameter. Returns true (or iftrue) if the attribute ends with the value and false (or iffalse) if not.

• substring: The string expected at the end.
• iftrue: The optional value returned if the attribute value ends with the parameter value.
• iffalse: The optional value returned if the attribute value does not end with the parameter value.

[attr | equals(value[, ifequals][, ifnotequals])]

Compares the attribute value with the first parameter value and returns true (or ifequals) if they are equal and false (or ifnotequals) if they are not.

• value: The string to compare with the attribute value.
• ifequals: The optional value returned if the attribute value equals the value represented by the first parameter.
• ifnotequals: The optional value returned if the attribute value does not equal the value represented by the first parameter.

[attr | extendtabs([integer_width])]

Replaces all tab characters found in the attribute value with spaces. If the tab size specified by the parameter is not given, a default tab size of 8 characters is used.

• width: The optional tab width, defaults to 8 if not specified.

[attr | expr(expression)]

Evaluates the mathematical expression.

• expression: The expression.

[attr | find(substring[, integer_startindex])]

Returns the lowest zero-based index at which the substring is found in the attribute value.

• substring: The string to search for in the attribute value.
• startindex: The optional index at which to start the search.

[attr | getlength()]

Returns the number of characters in the attribute value.

[attr | hmacshamd5([key], [toBase64, isBase64Encoded])]

Encrypt a string with the passed-in key and HmacSHAMD5 algorithm.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.
• isBase64Encoded: The optional boolean value that specifies whether the input is base 64 encoded. Default is false.

[attr | hmacsha1([key], [toBase64, isBase64Encoded])]

Encrypt a string with the passed-in key and HmacSHA1 algorithm.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.
• isBase64Encoded: The optional boolean value that specifies whether the input is base 64 encoded. Default is false.

[attr | hmacsha256([key], [toBase64, isBase64Encoded])]

Encrypt a string with the passed-in key and HmacSHA256 algorithm.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.
• isBase64Encoded: The optional boolean value that specifies whether the input is base 64 encoded. Default is false.

[attr | hmacsha512([key], [toBase64, isBase64Encoded])]

Encrypt a string with the passed-in key and HmacSHA512 algorithm.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.
• isBase64Encoded: The optional boolean value that specifies whether the input is base 64 encoded. Default is false.

[attr | ifequal(value[, ifequals][, ifnotequals])]

Compares the attribute value with the first parameter value and returns true (or ifequals) if they are equal and false (or ifnotequals) if they are not.

• value: The string to compare with the attribute value.
• ifequals: The optional value returned if the attribute value equals the value represented by the first parameter.
• ifnotequals: The optional value returned if the attribute value does not equal the value represented by the first parameter.

[attr | ifmatches(value[, ifmatch][, ifnotmatch])]

Returns true (or ifmatch) if the attribute value matches the first parameter, otherwise false (or ifnotmatch).

• value: The value that will be compared with the attribute value.
• ifmatch: The optional value returned if the attribute value matches the parameter value.
• ifnotmatch: The optional value returned if the attribute value does not match the parameter value.

[attr | iftrue([iftrue][, iffalse])]

Checks the attribute value and returns true (or iftrue) if true and false (or iffalse) if false.

• iftrue: The optional value returned if the attribute value is true.
• iffalse: The optional value returned if the attribute value is false.

[attr | implode([separator])]

Implodes multiple values to a string separated by a separator.

• separator: The optional separator.

[attr | insert(integer_index, string)]

Inserts the specified string at the specified index.

• index: The zero-based index of the position in the original value where the new string should be inserted.
• string: The string to insert into the original value.

[attr | isalpha([ifalpha][, ifnotalpha])]

Returns true (or ifalpha) if all characters in the attribute value are alphabetic and there is at least one character, false (or ifnotalpha) otherwise.

• ifalpha: The optional value returned if the attribute value is alphabetic.
• ifnotalpha: The optional value returned if the attribute value is not alphabetic.

[attr | isalphabetic([ifalpha][, ifnotalpha])]

Returns true (or ifalpha) if all characters in the attribute value are alphabetic and there is at least one character, false (or ifnotalpha) otherwise.

• ifalpha: The optional value returned if the attribute value is alphabetic.
• ifnotalpha: The optional value returned if the attribute value is not alphabetic.

[attr | isalphanum([ifalphanum][, ifnotalphanum])]

Returns true (or ifalphanum) if all characters in the attribute value are alphanumeric and there is at least one character, false (or ifnotalphanum) otherwise.

• ifalphanum: The optional value returned if the attribute value contains only alphabetic or numeric characters.
• ifnotalphanum: The optional value returned if the attribute value contains nonalphabetic or nonnumeric characters.

[attr | isalphanumeric([ifalphanum][, ifnotalphanum])]

Returns true (or ifalphanum) if all characters in the attribute value are alphanumeric and there is at least one character, false (or ifnotalphanum) otherwise.

• ifalphanum: The optional value returned if the attribute value contains only alphabetic or numeric characters.
• ifnotalphanum: The optional value returned if the attribute value contains nonalphabetic or nonnumeric characters.

[attr | isdigit([ifnum][, ifnotnum])]

Returns true (or ifnum) if all characters in the attribute value are digits and there is at least one character, false (or ifnotnum) otherwise.

• ifnum: The optional value returned if the attribute value is numeric.
• ifnotnum: The optional value returned if the attribute value is not numeric.

[attr | islower([iflower][, ifnotlower])]

Returns true (or iflower) if all letters in the attribute value are lowercase and there is at least one character that is a letter, false (or ifnotlower) otherwise.

• iflower: The optional value returned if the attribute value is lowercase.
• ifnotlower: The optional value returned if the attribute value is not lowercase.

[attr | isnumeric([ifnum][, ifnotnum])]

Returns true (or ifnum) if all characters in the attribute value are digits and there is at least one character, false (or ifnotnum) otherwise.

• ifnum: The optional value returned if the attribute value is numeric.
• ifnotnum: The optional value returned if the attribute value is not numeric.

[attr | isspace([ifspace][, ifnotspace])]

Return true (or ifspace) if there are only white-space characters in the attribute value and there is at least one character, false (or ifnotspace) otherwise.

• ifspace: The optional value returned if the attribute value is a space.
• ifnotspace: The optional value returned if the attribute value is not a space.

[attr | isupper([ifupper][, ifnotupper])]

Returns true (or ifupper) if all letters in the attribute value are uppercase and there is at least one character that is a letter, false (or ifnotupper) otherwise.

• ifupper: The optional value returned if the attribute value is uppercase.
• ifnotupper: The optional value returned if the attribute value is not uppercase.

[attr | join([separator])]

Implodes multiple values to a string separated by a separator.

• separator: The optional separator.

[attr | jsonescape()]

Converts the attribute value to a JSON-escaped, single-line string.

[attr | just(integer_width[, character])]

Returns the attribute value left-justified in a string of length specified by the first parameter. Padding is done using the fillchar specified by the second parameter.

• width: The total width of the output string.
• character: The optional character used for padding. The default is a space.

[attr | lfind(substring[, integer_startindex])]

Returns the lowest zero-based index at which the substring is found in the attribute value.

• substring: The string to search for in the attribute value.
• startindex: The optional index at which to start the search.

[attr | ljust(integer_width[, character])]

Returns the attribute value left-justified in a string of length specified by the first parameter. Padding is done using the fillchar specified by the second parameter.

• width: The total width of the output string.
• character: The optional character used for padding. The default is a space.

[attr | lsplit(delimiter, integer_index)]

Splits the string represented by the attribute value into tokens delimited by the first parameter and returns the token at the index specified by the second parameter; counts from the left.

• delimiter: The string used as a separator for splitting the string into tokens.
• index: The index of the token requested where the first token is at index 1.

[attr | match(pattern[, index][, option])]

Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.

• pattern: The regular expression pattern to match.
• index: The optional numbered index of the match to return. The default is 0.
• option: The optional comma-separated list of regular expression options. Some of the commonly used options are IgnoreCase, Multiline, Singleline, and IgnorePatternWhitespace.

[attr | md5hash([converttobase64])]

Computes the MD5 hash of the attribute value.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.

[attr | notequals(value[, notequals][, equals])]

Compares the attribute value with the first parameter value. Returns true (or notequals) if they are not equal and false (or equals) if they are.

• value: The string to compare with the attribute value.
• notequals: The optional value returned if the attribute value does not equal the value represented by the first parameter.
• equals: The optional value returned if the attribute value equals the value represented by the first parameter.

[attr | nowhitespace()]

Removes the white space from the string represented by the atttribute value.

[attr | percentage([integer_count])]

Returns the numeric value formatted as a percentage.

• count: The optional number that indicates how many places to the right of the decimal are displayed.

[attr | regex(pattern[, index][, option])]

Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.

• pattern: The regular expression pattern to match.
• index: The optional numbered index of the match to return. The default is 0.
• option: The optional comma-separated list of regular expression options. Some of the commonly used options are IgnoreCase, Multiline, Singleline, and IgnorePatternWhitespace.

[attr | regexmatch(pattern[, index][, option])]

Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.

• pattern: The regular expression pattern to match.
• index: The optional numbered index of the match to return. The default is 0.
• option: The optional comma-separated list of regular expression options. Some of the commonly used options are IgnoreCase, Multiline, Singleline, and IgnorePatternWhitespace.

[attr | regexreplace(search, replacewith[, integer_startat])]

Replaces all occurrences of the regular expression pattern found in the attribute value with replacewith.

• search: The regular expression to search with.
• replacewith: The text to replace the match with.
• startat: The optional character index at which to start replacement. Default is 0.

[attr | remove(integer_index[, integer_count])]

Deletes characters from the attribute value; begins at the zero-based index specified by the first parameter.

• index: The position to begin deleting the characters.
• count: The optional number of characters to delete. If not provided all characters starting from the specified index will be deleted.

[attr | replace(oldvalue, newvalue[, ishex])]

Replaces all occurrences of the first parameter in the string represented by the attribute value with the value of the second parameter.

• oldvalue: The string to be replaced.
• newvalue: The string to replace all occurrences of the first parameter.
• ishex: The optional boolean value specifying whether the first parameter is a hex representation of a character to replace. Default is false.

[attr | rfind(substring[, integer_startindex])]

Returns the highest zero-based index at which the substring is found in the attribute value.

• substring: The string to search for in the original value.
• startindex: The optional index at which to start the search.

[attr | rjust(integer_width[, character])]

Returns the right-justified attribute value in a string of length specified by the second parameter. Padding is done using the fillchar specified by the first parameter.

• width: The total width of the output string.
• character: The optional character used for padding, if not specified this defaults to a space.

[attr | rsplit(delimiter, integer_index)]

Splits the string represented by the attribute value into tokens delimited with the first parameter and returns the token at the index specified by the second parameter; counts from the right.

• delimiter: The string used as a separator for splitting the string into tokens.
• index: The index of the token requested where the first token is at index 1.

[attr | sha1hash([converttobase64])]

Computes the SHA-1 hash of the attribute value.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.

[attr | substring(integer_index[, integer_length])]

Returns a substring of the attribute value; starts at the index specified by the parameter and counts to the right.

• index: The zero-based index at which the substring starts from the right.
• length: The optional length of characters from the start index. The substring to the end is returned if length is not specified.

[attr | split(delimiter, integer_index)]

Splits the string represented by the attribute value into tokens delimited by the first parameter and returns the token at the index specified by the second parameter; counts from the left.

• delimiter: The string used as a separator for splitting the string into tokens.
• index: The index of the token requested where the first token is at index 1.

[attr | sqlescape()]

Converts the attribute value to an SQL-escaped, single-line string.

• dbtype: The database type to encode. The allowed values are SQL or SQLite. Default is SQL.

[attr | startswith(substring[, iftrue][, iffalse])]

Returns true (or iftrue) if the attribute value starts with the specified parameter, false (or iffalse) otherwise.

• substring: The string expected at the begining.
• iftrue: The optional value returned if the attribute value starts with the parameter value.
• iffalse: The optional value returned if the attribute value does not start with the parameter value.

[attr | striphtml()]

Returns the string with any HTML markup removed.

[attr | substring(integer_index[, integer_length])]

Returns a substring of the attribute value; starts at the index specified by the parameter.

• index: The zero-based index at which the substring starts.
• length: The optional length of characters from the start index. A substring to the end of the string is returned if length is not specified.

[attr | toalpha()]

Returns only the letters in a string.

[attr | toalphanum()]

Returns only the alphanumeric characters in a string.

[attr | tolower()]

Returns the string represented by the attribute value with all characters converted to lowercase.

[attr | toupper()]

Returns the string represented by the attribute value with all characters converted to uppercase.

[attr | trim()]

Trims leading and trailing white space from an attribute.

[attr | trimend()]

Trims trailing white space from an attribute.

[attr | trimstart()]

Trims leading white space from an attribute.

[attr | truncate(integer_count)]

Truncates the attribute value to the number of characters specified by the parameter.

• count: The number of characters in the resulting string.

[attr | wordwrap([integer_width][, break][, cut][, wrapexp])]

Wraps a string to a given number of characters.

• width: The optional number of characters at which the string will be wrapped.
• break: The optional break parameter to be used to break the line.
• cut: The optional boolean value that specifies whether to wrap the string at or before the specified width. Default is false.
• wrapexp: The optional regex expression to be used as a breakable characters. Default is space.

[attr | print([delim])]

Returns a string with all the values of the attribute concatenated using the specified delimiter.

• delim: The optional delimiter to separate values with. Default is a comma.

[attr | xmldecode()]

Converts the attribute value to a XML decoded string.

[attr | xmlencode()]

Converts the attribute value to a XML encoded string.

[attr | compare([value][, inputformat])]

Returns a signed number indicating the relative values of dates represented by the attribute value and parameter value.

• value: The optional string representation of the date that will be compared with the attribute value. Default is now.
• inputformat: The optional input format specifier. Default is autodetected.

[attr | date([outputformat])]

Returns the current system date and time in the format specified by the parameter if one was provided.

• outputformat: The optional format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T long time pattern), file (Windows file time), MM/dd/yy, etc.

[attr | dateadd([interval][, integer_value][, outputformat][, inputformat])]

Returns a string value of the datetime that results from adding the specified number interval (a signed integer) to the specified date part of the date.

• interval: The optional interval you want to add. Specify year, month, day, hour, minute, second, or millisecond.
• value: The optional number of intervals you want to add. Can either be positive for dates in the future or negative for dates in the past.
• outputformat: The optional output format specifier. Valid specifiers include d(short date pattern), D(long date pattern), f(long date/short time pattern), F(long date/time pattern), g(general short date/time pattern), G(general short date/long time pattern), r or R(RFC1123 pattern), s(sortable date/time pattern), t(short time pattern), T(long time pattern), file(Windows file time), MM/dd/yy, etc.
• inputformat: The optional input format specifier. Default is autodetected.

[attr | datediff([interval][, value][, inputformat])]

Returns the difference (in units specified by the first parameter) between now and the date specified by the second parameter.

• interval: The optional interval you want the result in. Specify day, hour, minute, second, or millisecond.
• value: The optional string representation of the date to compare with attribute value. Default is now.
• inputformat: The optional input format specifier. Default is autodetected.

[attr | day([inputformat])]

Returns the day component, expressed as a value between 1 and 31, of the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | dayofweek([inputformat])]

Returns the day of week for the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | dayofyear([inputformat])]

Returns the day of year expressed as a value between 1 and 366 for the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | filetimenow()]

Returns the date and time for the current system file time.

[attr | fromfiletime([outputformat])]

Converts a valid file time to a valid datetime value formatted as specified by the parameter if one was provided.

• outputformat: The optional output format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T (long time pattern), file (Windows file time), MM/dd/yy, etc.

[attr | isleap([ifleap][, ifnotleap])]

Returns true (or ifleap) if the 4-digit year represented by the attribute value is a leap year, false (or ifnotleap) otherwise.

• ifleap: The optional value returned if the attribute value is a leap year.
• ifnotleap: The optional value returned if the attribute value is not a leap year.

[attr | month([inputformat])]

Returns the month component expressed as a value between 1 and 12 of the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | now([outputformat])]

Returns the current system date and time in the format specified by the parameter if one was provided.

• outputformat: The optional format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T long time pattern), file (Windows file time), MM/dd/yy, etc.

[attr | todate([outputformat][,inputformat])]

Returns the date specified by the attribute value formatted as specified by the parameter if one was provided.

• outputformat: The optional output format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T (long time pattern), file (Windows file time), MM/dd/yy, etc.
• inputformat: The optional input format specifier. Default is autodetected.

[attr | tofiletime([inputformat])]

Converts a valid datetime to a valid file time value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | tomonth()]

Returns the name of the month for the numeric value specified by the attribute value.

[attr | toutc([outputformat][, inputformat])]

Returns the date specified by the attribute value converted to UTC and formatted as specified by the outputformat parameter if one was provided.

• outputformat: The optional format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T (long time pattern), and file (Windows file time), MM/dd/yy, etc.

[attr | utcnow([outputformat])]

Returns the current system UTC date and time.

• outputformat: The optional format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g(general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T (long time pattern), file (Windows file time), MM/dd/yy, etc.

[attr | weekday([inputformat])]

Returns the day of the week as an integer where Monday is 0 and Sunday is 6.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | year([inputformat])]

Returns the year component of the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | abs()]

Returns the absolute value of the numeric attribute value.

[attr | add([value])]

Returns the sum of the numeric attribute value and the value specified by the parameter.

• value: The optional numeric value to add to the specified attribute value. Default is 1.

[attr | and(value)]

Returns the AND of two values. The values provided on each side must be 1/0, yes/no or true/false.

• value: The boolean value to compare by.

[attr | ceiling()]

Returns the smallest integer greater than or equal to a numeric attribute value.

[attr | div([value])]

Returns the result of dividing the numeric attribute value by the specified value of the parameter.

• value: The optional numeric value to divide the numeric attribute value by. Default is 2.

[attr | divide([value])]

Returns the result of dividing the numeric attribute value by the specified value of the parameter.

• value: The optional numeric value to divide the numeric attribute value by. Default is 2.

[attr | floor()]

Returns the largest integer less than or equal to the numeric attribute value.

[attr | greaterthan(value[, ifgreater][, ifnotgreater])]

Returns true (or ifgreater) if the attribute value is greater than the parameter value, false (or ifnotgreater) otherwise.

• value: The numeric value to compare with the attribute value.
• ifgreater: The optional value returned if the attribute value is greater than the parameter value.
• ifnotgreater: The optional value returned if the attribute value is not greater than the parameter value.

[attr | isbetween(integer_lowvalue, integer_highvalue[, ifbetween][, ifnotbetween])]

Returns true (or ifbetween) if the attribute value is greater than or equal to the first parameter value and less than or equal to the second parameter value, false (or ifnotbetween) otherwise.

• lowvalue: The lower bound of the range to check.
• highvalue: The higher bound of the range to check.
• ifbetween: The optional value returned if the attribute value is greater than or equal to the first parameter value and less than or equal to the second parameter value.
• ifnotbetween: The optional value returned if the attribute value is less than the first parameter value or greater than the second parameter value.

[attr | isequal(value[, ifequal][, ifnotequal])]

Returns true (or ifequal) if the attribute value is equal to the parameter value, false (or ifnotequal) otherwise.

• value: The numeric value to compare with the attribute value.
• ifequal: The optional value returned if the attribute value is equal to the parameter value.
• ifnotequal: The optional value returned if the attribute value is not equal to the parameter value.

[attr | isgreater(value[, ifgreater][, ifnotgreater])]

Returns true (or ifgreater) if the attribute value is greater than the parameter value, false (or ifnotgreater) otherwise.

• value: The numeric value to compare with the attribute value.
• ifgreater: The optional value returned if the attribute value is greater than the parameter value.
• ifnotgreater: The optional value returned if the attribute value is not greater than the parameter value.

[attr | isless(value[, ifless][, ifnotless])]

Returns true (or ifless) if the attribute value is less than the parameter value, false (or ifnotless) otherwise.

• value: The numeric value to compare with the attribute value.
• ifless: The optional value returned if the attribute value is less than the parameter value.
• ifnotless: The optional value returned if the attribute value is not less than the parameter value.

[attr | lessthan(value[, ifless][, ifnotless])]

Returns true (or ifless) if the attribute value is less than the parameter value, false (or ifnotless) otherwise.

• value: The numeric value to compare with the attribute value.
• ifless: The optional value returned if the attribute value is less than the parameter value.
• ifnotless: The optional value returned if the attribute value is not less than the parameter value.

[attr | mathadd([value])]

Returns the sum of the numeric attribute value and the value specified by the parameter.

• value: The optional numeric value to add to the specified attribute value. Default is 1.

[attr | mathmod(value)]

Returns the modulus of the numeric attribute value divided by the specified parameter value.

• value: The number to divide the attribute value by.

[attr | mathpow([value])]

Returns the numeric attribute value raised to the power specified by the parameter value.

• value: The optional power to raise the attribute value to. Default is 2.

[attr | mathround([integer_value])]

Returns the numeric attribute value rounded to the number of decimal places specified by the parameter.

• value: The optional number of decimal places. Default is 2.

[attr | mathsub([value])]

Returns the difference between the numeric attribute value and the value specified by the parameter.

• value: The optional numeric value to subtract the attribute value by.

[attr | modulus(value)]

Returns the modulus of the numeric attribute value divided by the specified parameter value.

• value: The number to divide the attribute value by.

[attr | multiply([value])]

Returns the result of multiplying the numeric attribute value with the specified value of the parameter.

• value: The optional numeric value to multiply the numeric attribute value by. Default is 2.

[attr | or(value)]

Returns the OR of two values. The values provided on each side must be 1/0, yes/no or true/false.

• value: The boolean value to compare by.

[attr | pow([value])]

Returns the numeric attribute value raised to the power specified by the parameter value.

• value: The optional power to raise the attribute value to. Default is 2.

[attr | rand([integer_value])]

Returns a random integer between 0 and the parameter value.

• value: The optional value that limits the highest possible random integer. Default is 100.

[attr | random([integer_value])]

Returns a random integer between 0 and the parameter value.

• value: The optional value that limits the highest possible random integer. Default is 100.

[attr | round([integer_value])]

Returns the numeric attribute value rounded to the number of decimal places specified by the parameter.

• value: The optional number of decimal places. Default is 2.

[attr | sqrt()]

Returns the square root of the numeric attribute value.

[attr | subtract([value])]

Returns the difference between the numeric attribute value and the value specified by the parameter.

• value: The optional numeric value to subtract the attribute value by.

Statements in API Script are defined using keywords, XML elements prefixed with "api:". Keywords include the usual features of a programming or scripting language, such as conditionals, loops, and flow control. However, API Script also includes special keywords tailored specifically for feed manipulation and generation; for example, api:call, api:enum, and api:set.

Most keywords take parameters that define or affect their behavior. Parameters are specified as XML attributes of the keyword element. The required and optional parameters, along with code examples, for each keyword are explained in this section.

Keyword Scope

In API Script, some keywords introduce scope; that is, some keywords can be nested inside the bodies of other keywords, and how they are nested is meaningful to the language:

• You can define instructions for an iteration within a keyword's scope: For example, the body of an api:call keyword is executed for each item in the feed produced by the operation invoked by api:call. Because of this, keywords nested inside api:call are executed for each iteration to produce each item in the feed.
• Scope associates matching pairs of keywords; for example, the api:else keyword defines an alternate execution path for a conditional keyword like api:equals or api:check. In API Script, a certain api:else keyword is associated with a conditional statement when it is nested as a direct child of the conditional statement. Thus, the api:else keyword must not be defined outside of the scope introduced by a conditional keyword.
• Keywords can introduce new items (variables) within their scope. For example, the api:call keyword will introduce a default output item representing the item being iterated over in the feed being generated.
• Keywords can inject additional attributes into the default item that provide more information within their scope. These attributes are called Control Attributes when described in this document. They are easily recognized because they start with the _ character; for example, _attr, _index, _value, etc.

The api:break keyword can be used to break out of iterations of api:call or api:enum. It can also be used to break out of the entire script if it is used outside the scope of any other API Script keywords.

Parameters

None

Control Attributes

None

Examples

Break out of api:enum. The example below lists the first two attributes of the item foo:

<api:set attr="foo.attr1" value="value1"/>
<api:set attr="foo.attr2" value="value2"/>
<api:set attr="foo.attr3" value="value3"/>

<api:enum item="foo">
[_index]: [_attr] has the value [_value]. 
<api:equals attr="_index" value="2"> 
<api:break/>
</api:equals>
</api:enum>

See Also

• api:continue: Continue to the next item.
• api:enum: Loop over the values of an item, list, range, or multivalued attribute.
• api:call: Loop over items returned by an operation.

The api:call keyword is used to call operations. Valid operations are the following:

• Built-in operations installed along with Sync App assemblies located in the bin subfolder of the application
• Operations you write yourself in .NET or Java and place in the bin subfolder of the application

Operations take items as input and return feeds as output. The scope of api:call is executed for every item in the feed returned from the call. Within the scope of api:call, you can inspect and modify the attributes in the item returned. You can then provide these attributes as inputs to another operation, thus forming an operation pipeline. Or, you can push out the item to the output.

The Output Item Stack and the Default Item

Each time an api:call is encountered, a new item is pushed onto an internal stack of items. The item on the top of this stack is the default item. This is the item that provides input to api:call when an item name is not explicitly specified in the input to the call.

The called operation writes attributes to the default item, and api:push pushes the default item to the output. When you push an item, only the attributes from the default item, at the top of the stack, are pushed.

The default item is swept clean after an item has been iterated over, and the api:call then works on the next item. This means that you will not be able to read a value set in a previous iteration of an api:call in the next iteration. To retain values across an iteration, copy them to a named item with api:set.

You can refer to the default item as _ or explicitly as _out1, _out2, _out[n], where N is the depth of the stack. This enables you to read all the values available at this point in the execution process; the lookup process starts from the default item and continues through the stack until a value is found.

In the example below, you can see how items are pushed to the top of the stack with each call and how the default item changes within the scope of each call.

<api:call op="operation1"> 
The default item here is _out1 
A push here would push the attributes from _out1 
The input item used to call operation2 is also _out1 
<api:call op="operation2"> 
The default item here is _out2 
A push here will push the attributes from _out2 
If there was another api:call here the input item used would be _out2 
_out2 is swept clean here for the next iteration 
</api:call> 
The default item here is again _out1 
A push here would again push the attributes from _out1 
The input item at this level is again _out1 
_out1 is swept clean here for the next iteration 
</api:call> 

Specify Input to an Operation

There are 3 ways to provide input to a call:

• The default item: If an input item is not specified, the called operation reads input values from the default item. You can use the api:set keyword to set values in the default item so that they are processed by the called operation.
  

  <api:set attr="mask" value="*.txt"/>
  <api:set attr="path" value="C:\\"/>
  <api:call op="fileListDir">
  ... 
  </api:call>

• An explicit input item: Instead of using the default item, you can use the in parameter to explicitly specify the items you want to use as input to the operation. If you choose to do so the default item is no longer used and the input values are read from the input items specified and, as shown below, the query string passed to the operation.
  

  <api:set attr="mask" value="*.* -- Will be ignored --"/>
  <api:set attr="myinput.mask" value="*.txt"/>
  <api:set attr="myinput.path" value="C:\\"/>
  <api:call op="fileListDir" in="myinput">
  ... 
  </api:call>

• Query string parameters: You can also use query string notation to specify input to an operation. The attributes specified as part of the query string are given precedence. Thus, if there is a name clash between attributes specified in the query string and those in the input item, the ones in the query string are preferred. However, the other attributes of the input item are still accessible. The example below shows the syntax to specify input in the query string:
  

  <api:set attr="myinput.mask" value="*.txt -- will be overridden --"/> 
  <api:set attr="myinput.path" value="C:\\"/> 
  <api:call op="fileListDir?mask=*.rsb" in="myinput">
  ...
  </api:call>

Parameters

• op: The name of the operation to be called.
• in[put]: A list of items used as input when invoking the operation. Attributes are looked up from left to right in the input items specified.

• out[put]: The item where the output attributes are placed. Within the api:call scope, the current output item can be retrieved using the item name specified here. You can also use _out[n] or _pipe to refer to the call's results.

  Note: Attributes set within a call are not available outside the scope of the call. This is because each iteration of the call deletes the attributes from the previous iteration; at the end of the call nothing is left. To access an attribute outside the scope of a call, use api:set to explicitly copy the attribute to the item you want to use outside the call.

• item: The name of the item to be used for both input and output.
• sep[arator]: The separator used to separate multiple input items. The default is a comma.
• ignoreprefix: A comma-separated list of prefixes that are ignored when found. Some operations return attributes with names of the form "prefix:name" (e.g., api:operation and sql:company). In some situations, you may want to treat both prefix:name and name as the same attribute.
• page and pagesize: The subset of items that will be iterated through. Used to enable paging of the operation or feed results. For example, if you specify page="2" and pagesize="5", the api:call keyword iterates only through items 6 to 10 of the resulting feed.

Control Attributes

• _index: The index of the item currently being iterated over by api:call.
• _op: The name of the operation being called. This is helpful when the operation name is not known until execution.
• _separator: The separator used to separate multiple input items.

Examples

Call an operation using the default item as input:

<api:set attr="path" value="C:\myfiles"/>
<api:call op="fileListDir">
  <api:push/>
</api:call>

See Also

• api:first: Write elements to be executed only for the first iteration of the call.
• api:catch: Catch errors within a call.
• api:continue: Continue to the next iteration.

The api:case keyword is used with the api:select keyword. The api:case keyword consists of a block of API Script that is executed if the value in api:select matches the value in api:case.

Parameters

• value: The pattern or value to compare against the value specified in api:select.
• match: The type of matches to find to determine whether the case statement should be executed. The default value is "exact", which requires an exact match of the value. Other supported types are "regex", for regular expression matching, and "glob", which supports a simple expression model similar to the one used in file-name patterns (e.g., *.txt). The .NET edition of the Sync App uses the .NET Framework version of regular expression matching. The Java edition uses Java regular expression constructs.

Control Attributes

None

Examples

Display an icon based on the condition. The api:case elements match "CompanyA" and "CompanyB" in the company_name attribute and if any occurrences are found take the action associated with that case.

<api:select value="[company_name]">
  <api:case value="CompanyA">
    <img src="http://www.companya.com/favicon.ico" />
  </api:case>
  <api:case value="CompanyB">
    <img src="http://www.companyb.com/favicon.ico" />
  </api:case>
  <api:default>
    <img src="http://www.myhosting.com/generic.ico"/>
  </api:default>
</api:select>

See Also

• api:select: Write a multiselect API Script block.
• api:default: Write a default case for api:select.

The api:catch keyword is used to create an exception-handling block in a script. In addition to api:try, you can contain an api:catch block within any of the following keywords, the scope of which serves as an implicit api:try section:

• api:call
• api:script

Parameters

• code: The code parameter allows you to selectively catch exceptions. To catch all exceptions, use the character *.

Control Attributes

• _code: The code of the caught exception.
• _desc: A short description of the caught exception.
• _details: More information about the exception, if available.

Examples

Throw and catch an exception. Inside an api:call, an APIException is thrown and caught. Inside the scope of the keyword, the api:ecode and api:emessage attributes are added to the current item and pushed out.

<api:call op="...">
  <api:throw code="myerror" description="thedescription" details="Other Details."/>
  <api:catch code="myerror">
    <api:set attr="api:ecode" value="[_code]"/>
    <api:set attr="api:emessage" value="[_description]: [_details]"/>
    <api:push/>
  </api:catch>
</api:call>

<api:catch code="*">
  An exception occurred. Code: [_code], Message: [_desc]
</api:catch>

See Also

• api:try: Define a scope to catch exceptions.
• api:throw: Force an exception.

The api:check keyword can be used with or without a value parameter. Without a value parameter, it is used to ensure that an attribute is present in an item and that it is not a null string before the body of the api:check is executed.

If a value parameter is specified, the api:check body executes only if the expression evaluates to true. Other values are considered false. The evaluation is case insensitive.

Like other simple conditionals in API Script, it can be paired with an api:else keyword. Note that, unlike api:equals, api:check does not throw an exception if the attribute does not exist in the item.

Parameters

• item: The item in which to check the attribute. Specifying an item is not required. If no item is specified, the default output item is used instead.
• attr: The name of the attribute to check. This parameter is required.
• value: An expression that evaluates to true or false. For example, the result of a formatter that returns true or false.
• action: The action to execute if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Examples

<api:check attr="_input.In_Stock">
...
</api:check>

See Also

• api:exists: Check if an attribute exists.
• api:equals: Check for equality.
• api:notequals: Check for inequality.

The api:continue keyword can be used to move to the next iterations of api:call or api:enum.

Parameters

Control Attributes

Examples

Produce a feed that lists only the files in a directory. The api:continue keyword is used to skip over items representing a directory.

<api:call op="fileListDir">
  <api:equals attr="file:isdir" value="true">
    <api:continue/>
  </api:equals>
  <api:push/>
</api:call>

List the values of single-valued attributes. All multivalued attributes are skipped by using api:continue.

<api:set attr="foo.multiattr#" value="value1"/>
<api:set attr="foo.multiattr#" value="value2"/>
<api:set attr="foo.attr2"      value="value3"/>
<api:enum item="foo">
  <api:notequals attr="_count" value="1">
    <api:continue/>
  </api:notequals>
  [_index]: [_attr] has the value [_value] <!-- only evaluated for attr2 -->
</api:enum>

See Also

• api:break: Break out of api:call or api:enum iterations.
• api:enum: Iterate over the values of an item, attribute, list, or range.
• api:call: Call scripts, operations, or feeds.

The api:default keyword is used with the api:select keyword. The api:default keyword consists of a block of API Script that is executed if the value in api:select does not match any of the values in api:case.

Parameters

Control Attributes

Examples

<api:select value="[company_name]">
  <api:case value="CompanyA">
    <img src="http://www.companya.com/favicon.ico" />
  </api:case>
  <api:case value="CompanyB">
    <img src="http://www.companyb.com/favicon.ico" />
  </api:case>
  <api:default>
    <img src="http://www.myhosting.com/generic.ico"/>
  </api:default>
</api:select>

<p>

See Also

• api:select: Write a multiselect API Script block.

The api:else keyword is used to execute an alternate block of code when a test like api:exists or api:match fails. It can also be used to execute an alternate block of code within an api:call when the call fails to produce any output items.

Unlike other languages API Script requires the api:else statement to be within the scope of the test it belongs to.

Parameters

Control Attributes

Examples

<api:call op="fileListDir" out="out">
  <api:null attr="filename">
    <api:set attr="title" value="Unnamed File"/>
    <api:else>
      <api:set attr="title" value="[filename]"/>
    </api:else>
  </api:null>
  <api:push title="[title]">
  [out.*]
  </api:push>
</api:call> 

See Also

• api:exists: Check if an attribute exists.
• api:equals: Check for equality.

The api:enum keyword can be used to enumerate over the attributes within an item, a delimited list, a supplied range of values, and the values of a multivalued attribute. The body of api:enum is executed for each element of the set that is being iterated upon.

Parameters

• item: The name of the item whose attributes you want to iterate over.
• attr: The expression to match that specifies which attributes are iterated over. For example, "api:*". You can also provide this parameter to iterate over the values of a multivalued attribute.
• prefix: The prefix based on which the item is enumerated.
• expand: The boolean value that specifies how api:enum behaves when multivalued attributes are encountered. If expand is set to true, the body of api:enum will be executed once for each value of the attribute. If false, all values will be concatenated in a single string value, and only a single iteration will be performed. By default, api:enum will not expand all values.
• sep[arator]: The separator to be used to concatenate values of a multivalued attribute if the expand parameter is false. Additionally, separator is used to tokenize the values in a list if listseparator is not defined. The default value is the new-line character "\n".
• list: The list of separated values to enumerate over. For example, if the list of values is "violet, indigo, blue, green, yellow, orange, red" and the separator is a ',' the scope of api:enum is executed for each color in the rainbow.
• range: The range of numbers or characters to enumerate over in ascending or descending order; for example, "a..z", "Z..A".
• recurse: Whether to enumerate over nested attributes. The default is true.

Control Attributes

• _attr: The name of the attribute being iterated over. When you are iterating over the values of a multivalued attribute, the attribute name will be the same except that it will also have the index as part of the name. The index is separated from the name by the hash symbol (#). For example, name#1, name#2, etc.
• _index: The index at which the attribute appears within an item or the position of the list element currently being enumerated over.
• _value: The value of the attribute being iterated over. For a multivalued attribute, the expand and separator parameter settings determine whether _value is a reference to one attribute value or a reference to all attribute values.
• _count: The number of values in an attribute or in a list.
• _separator: The separator used to separate the values in a list.

Examples

Display the attribute names and attribute values of the item named "input":

<api:set item="input" attr="Greeting" value="Hello" />
<api:set item="input" attr="Goodbye" value="See ya" />
<api:enum item="input">
  [_attr] is [_value]
  <br/>
</api:enum> 

goodbye is See ya greeting is Hello

<api:set attr="colors" value="violet, indigo, blue, green, yellow, orange, red"/>
<api:enum list="[colors]" separator=",">
  [_value] 
</api:enum>

<api:enum range="a..z">
  [_value] 
</api:enum>
<api:enum range="Z..A">
  [_value] 
</api:enum>
<api:enum range="1..25">
  [_value] 
</api:enum>

<api:set attr="foo.email#1" value="[email protected]"/>
<api:set attr="foo.email#2" value="[email protected]"/>
<api:enum attr="foo.email" expand="true">
    ([_index]) [_attr] -> [_value]
</api:enum>

(1) email#1 -> [email protected] (2) email#2 -> [email protected]

See Also

• api:break: Break out of api:call or api:enum iterations.
• api:continue: Skip an api:call or api:enum iteration.
• api:first: Provide special processing in the first iteration.
• api:last: Provide special processing in the last iteration.

The api:equals keyword compares the value of an attribute to a reference value. Unlike api:check, the api:equals keyword will throw an exception if the specified item does not contain the specified attribute. If the specified attribute exists and its value matches, then the comparison will succeed.

Note: Both api:equals and api:check expect the name of the attribute whose value will be compared with a given value. If you need to compare two values, you can use api:select instead. For example:

<api:select value="[company_name]">
  <api:case value="CompanyA">
    <img src="http://www.companya.com/favicon.ico" />
  </api:case>
  <api:case value="CompanyB">
    <img src="http://www.companyb.com/favicon.ico" />
  </api:case>
  <api:default>
    <img src="http://www.myhosting.com/generic.ico"/>
  </api:default>
</api:select>

Parameters

• item: The item in which to compare the attribute. Specifying an item is not required. If no item is specified, the default output item is used instead.
• attr: The name of the attribute to compare.
• case: Whether to use case-sensitive or case-insensitive comparison. This defaults to case-sensitive comparison; to use case-insensitive comparison, set the case parameter to "ignore".
• value: The value to compare the attribute with.
• action: The action to perform if equality is met. Allowed values: break, continue.

Control Attributes

Example

Like other conditional keywords, the body of api:equals may also contain an api:else keyword, which will be executed if the values do not match. The following lists all files except .err files:

<api:call op="fileListDir">
  <api:equals attr="file:extension" value=".err">
  <api:else>
    <api:push/>
  </api:else>
  </api:equals>
</api:call>

See Also

• api:select: Choose between more than one alternative.
• api:notequals: Create a block that is executed when equality is not met.

The api:exists keyword checks that an attribute has a value in the specified item. The api:notnull keyword is a synonym for api:exists.

Parameters

• item: The item to check. If not specified, the default output item is used.
• attr: The name of the attribute to check.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Example

<api:call op="fileListDir" output="out">
  <api:exists attr="filename">
    <api:set attr="title" value="[filename]"/>
    <api:else>
      <api:set attr="title" value="Unnamed File"/>
    </api:else>
  </api:exists>
  <api:push title="[title]">
  [out.file:*] 
  </api:push>
</api:call> 

See Also

• api:else: Create a block which is executed if the condition is not satisfied.

The api:first keyword is used to execute a section of a script for only the first iteration of an api:call or api:enum keyword. It is a convenient way to generate headings or to inspect the first item of a feed before going through the rest of the feed.

During the first iteration, the api:first body executes before any other code within the api:call or api:enum, regardless of where the api:first is located within the api:call or api:enum body. As a reminder of this, it is recommended to locate the api:first at the top of the api:call or api:enum body.

If the scope has no items, then neither api:first nor api:last are executed.

Parameters

Control Attributes

Examples

Create an HTML table from a feed where each item is represented as a row:

<api:call op="listCustomers">
  <api:first>
    <table>
    <thead>
      <api:enum item="customer">
        <td>[_attr]</td>
      </api:enum>
    </thead>
  </api:first>
    <tr>
      <api:enum item="customer">
        <td>[_value]</td>
      </api:enum>
    </tr>
  <api:last>
    </table>
  </api:last>
</api:call>

See Also

• api:last: Execute a code block only for the last iteration.

The api:finally keyword is used to execute a section of a script after control leaves an api:call, api:try, or api:script statement. It is a convenient way to clean up the formatting of generated documents.

Parameters

Control Attributes

Examples

The api:finally block is executed when an unhandled exception occurs. This can be used to ensure that tags are closed:

<table>
<api:call op="listCustomers" out="customer">
  <api:first>
    <thead>
        <api:enum item="customer">
          <td>[_attr]</td>
        </api:enum>
    </thead>
  </api:first>
  <tr>
      <api:enum item="customer">
        <td>[_value]</td>
      </api:enum>
  </tr>
  <api:finally>
  </table> <!-- ensure tags are still closed -->
  </api:finally>
</api:call>

See Also

• api:try: Define a scope to catch exceptions.
• api:call: Loop over items returned by an operation.
• api:script: Create a script block.

You can use the api:if keyword to evaluate expressions that can contain items, attributes, and values. The scope of the keyword is executed if the specified expression evaluates to true.

Parameters

• exp: The expression to evaluate. You can make string, date, and numeric comparisons.
• attr: The name of the attribute to compare the value of. The value of an attribute can be checked for a matching value or for the values null or notnull.
• value: The value to compare with the value of the attribute specified by attr.
• item: The item that contains the attribute being compared.
• operator: The name of the operator to compare the operands specified by attr and value. Allowed values: null, notnull, hasvalue, equals, equalsignorecase, notequals, lessthan, and greaterthan. Default: notnull.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

None

Examples

Evaluate a simple comparison of two values:

<api:if exp="[attr] == 10">

<api:set attr="attr1" value="value1"/>
<api:set attr="attr2" value="value2"/>
<api:if attr="attr1" value="[attr2]" operator="notequals"> <!-- Evaluates to true -->
<api:else>
False
</api:else>
True
</api:if>

<api:set attr="exists" value="true"/>
<api:if attr="exists"> <!-- Evaluates to true -->
[exists]
</api:if>

See Also

• api:exists: Check that an attribute has a value in the specified item.
• api:equals: Create a block that is executed when equality is met.

The api:include keyword is used to include API Script from other files. Like traditional includes in other programming languages, api:include is replaced by the contents of the file specified in the file parameter.

Parameters

• file | document: The name of the file to be included.

Control Attributes

Examples

Import certain attributes (companyname, copyright) in each script without duplication:

<api:include file="globals.rsb"/> 
[companyname] 
[copyright] 
<api:call ...>

The api:info keyword is used to describe the metadata for scripts. This information is used by the Sync App to implement basic error checking on user input and to set default values. The api:info keyword can contain the following:

• Column definitions
• Input parameters the script expects
• Output the script produces

Table Parameters

The following parameters can be defined for the table itself:

• desc[ription]: The description for the table. If a description is not provided, the name of the script is used.
• title: The title of the script. If a title is not provided, the name of the script is used.
• methods: The HTTP methods that can be executed against this script. SELECT, INSERT, UPDATE, and DELETE correspond to HTTP GET, POST, PUT/PATCH/MERGE, and DELETE, respectively.
• url: The URL of the script author.
• keywords: The keywords that describe the script.

Input, Output, and Column Parameters

The api:info keyword has additional parameters contained within its scope that define columns as well as script inputs and outputs. Note these parameters are not API Script keywords, but additional information for api:info.

attr

Other optional attributes provide more information about the column and enable the Sync App to represent these columns correctly in various tools.

<attr 
  name="Name"
  xs:type="string"
  other:xPath="name/full"
  readonly="true"    
  columnsize="100"
  desc="The name of the person." 
/> 

• name: The alphanumeric string that defines the name of the column.
• xs:type: The data type of the column. The string, int, double, datetime, and boolean types are supported.
• other: Attributes prefixed with 'other:' that provide extra information. These other properties can be operation specific. For example, other:xPath specifies the XPath to a node in a local or remote resource. The XPath can be relative to a RepeatElement.
• desc[ription]: A short description of the column.
• key: Whether the column is part of the primary key in the table.
• readonly: Whether the column can be updated. Allowed values are true and false.
• req[uired]: Whether the column must be specified in an insert. Allowed values are true and false.
• def[ault]: The default value for the column if none is specified.
• values: A comma-separated list of the valid values for the column. If specified, the engine will throw an error if the specified column value does not match one of the allowed values.
• reference[s]: The foreign key to the primary key of another table. The foreign key is specified with the following syntax: table.key. For example: "Employees.EmployeeId".
• columnsize: The maximum character length of a string or the precision of a numeric column. The precision of a numeric column is the number of digits.
• scale | decimaldigits: The scale of a decimal column. The scale is the number of digits to the right of the decimal point.
• isnullable: Indicates whether the column accepts null values. Note that this does not prevent sending or receiving a null value.

input

Input elements can be used in schemas for both tables and stored procedures.

The attributes of this parameter provide users with more information about the input and allow the engine to perform rudimentary checks.

In stored procedure schemas, one or more input elements are used to describe the inputs of the stored procedure. In table schemas an input element defines a pseudo column, a column that can only be used in the WHERE clause.

For XML data sources, pseudo columns cannot contain an XPath.

<input
  name="name"
  desc="Example of an input parameter"
  default="defValue"
  req="true"
  values="value1, value2, value3"
  xs:type="string" 
/>

• name: The name of the input. An alphanumeric string that may additionally contain the following: "#" denotes that the input can have multiple values, "myprefix:*" denotes a set of inputs with the same prefix, and a value of "*" denotes arbitrary input parameters.
• desc[ription]: A short description of the input.
• xs:type: The data type of the input. The string, int, double, datetime, and boolean types are supported.
• def[ault]: The default value to be used when no input value is supplied in the script call.
• key: Whether the input is a primary key.
• req[uired]: Whether the input is required. The engine will throw an error if the required input is not supplied and there is no default value defined. Allowed values are true and false.
• values: A comma-separated list of the allowed values for the input. If specified, the engine will throw an error if the specified input does not match one of the allowed values.
• alias: The alias of the input.
• other: Attributes prefixed with "other:" that provide extra information. These other properties can be operation specific.

output

Output parameters describe the output of the operation. However, they are ignored by the Sync App. Thus, the output of the operation can be entirely independent of what is defined in the info block.

<output  name="Id" 
         desc="The unique identifier of the record."
         xs:type="string"
         other:xPath="content/properties/Id" 
/>

• name: The name of the output. "myprefix:*" denotes a set of outputs with the same prefix, and a value of "*" denotes arbitrary output parameters.
• xs:type: The data type of the output. The string, int, double, datetime, and boolean types are supported.
• desc[ription]: A short description of the output.
• columnsize: The maximum character length of a string or the precision of a numeric output. The precision is the number of digits.
• other: Attributes prefixed with 'other:' that provide extra information about the output. For example, other:xPath specifies the XPath to a node in a local or remote resource. The XPath can be relative to a RepeatElement.

Examples

<api:info title="NorthwindOData" desc="Parse an XML document (NorthwindOdata.xml) into rows.">
  <attr name="ID"           xs:type="int" key="true" other:xPath="content/properties/ID" />
  <attr name="EmployeeID"   xs:type="int"            other:xPath="content/properties/EmployeeID" />
  <attr name="Name"         xs:type="string"         other:xPath="content/properties/Name" />
  <attr name="TotalExpense" xs:type="double"         other:xPath="content/properties/TotalExpense" />
  <attr name="HireDate"     xs:type="datetime"       other:xPath="content/properties/HireDate" />
  <attr name="Salary"       xs:type="int"            other:xPath="content/properties/Salary" />
</api:info> 

The following describes the metadata for a stored procedure that searches the Web using the Bing Search API:

<api:info title="SearchWeb"    desc="Use Bing to search the Web."> 
  <output name="Id"          xs:type="string"    other:xPath="content/properties/ID"          />
  <output name="URL"         xs:type="string"    other:xPath="content/properties/Url"         />
  <output name="Title"       xs:type="string"    other:xPath="content/properties/Title"       />
  <output name="Description" xs:type="string"    other:xPath="content/properties/Description" />
  <output name="DisplayURL"  xs:type="datetime"  other:xPath="content/properties/DisplayUrl"  />

  <input name="SearchTerms"    />
</api:info>

See Also

• api:script: Create a script block.
• api:call: Call scripts or operations.
• api:set: Set attributes in an item.

API Script keywords within the scope of the api:last keyword will only be executed after the last item is encountered and only if there were items returned by api:call or api:enum.

An api:last statement is executed only after the last item is processed; it maintains access to the last item in the feed. If the scope has no items, then neither api:first nor api:last are executed.

Parameters

Control Attributes

Examples

Create an HTML table from a feed where each item is represented as a row:

<api:call op="listCustomers">
  <api:first>
    <table>
    <thead>
      <api:enum item="customer">
        <td>[_attr]</td>
      </api:enum>
    </thead>
  </api:first>
  <tr>
    <api:enum item="customer">
      <td>[_value]</td>
    </api:enum>
  </tr>
  <api:last>
    </table>
  </api:last>
</api:call>

See Also

• api:first: Execute a code block only for the first iteration.

The api:map keyword is used to map attributes in one item to attributes in another item. Attributes are read from one item and written to another with the new names specified in the map string. The api:map keyword does not clear the destination item: It simply adds new attributes to it. Or, if an attribute exists in the destination item, it is overwritten, and other attributes remain as they were.

Parameters

• to: The name of the item into which attributes are to be written.
• from: The name of the item from which attributes are to be read.
• map: A list of attribute names that specify the attribute names in the destination item, followed by the attribute names in the source item. For example, you can map attributes with one prefix to attributes with another prefix by using the syntax below:
  

  customer:* = soap:*

  Any characters that are not valid attribute names are ignored and are used to demarcate the end of a name.

Control Attributes

• This keyword does not have any control attributes.

Examples

Map three attributes: The map is a succession of the "to" attribute name followed by the "from" attribute name.

<api:set item="item1" attr="var1" value="x"/>
<api:set item="item1" attr="var2" value="y"/>
<api:set item="item2" attr="attr1" value="z"/>
<api:map to="item2" from="item1" map="attr1=var1, attr2=var2"/>

You can map multiple attributes from items with one prefix to items with a different prefix. This is useful in changing the prefix from Sync App prefixes (for example, soap:*) to business domain prefixes (for example, customer:*). The following example creates a mapping from all soap:* attributes in the item soapout to attributes prefixed with customer:* in the item customer:

<api:map from="soapout" to="customer" map="customer:* = soap:*"/>

<api:map from="copyfrom" to="copyto" map="* = *"/>

See Also

• api:set: Set the attributes of an item.

The api:match keyword is similar to the api:equals keyword; however, it permits complex matching rules.

Parameters

• pattern: The pattern to match.

• type: The type of matches to find. The default value is "exact", which requires an exact match of the value. In this case api:match is identical to api:equals. Other supported types are "regex", for regular expression matching, and "glob", which supports a simple expression model similar to the one used in file-name patterns, for example, *.txt.

  The .NET edition of the Sync App uses the .NET Framework version of regular expression matching. The Java edition uses Java regular expression constructs.

• value: The value to match.

Control Attributes

None

Examples

Check for floating point numbers using a regex pattern. If the pattern is matched then the item will be pushed out.

<api:match pattern="\[-+\]?\[0-9\]*\.?\[0-9\]*" type="regex" value="-3.14">
  <api:push/>
</api:match>

See Also

• api:select: Compare multiple values.

The api:notequals keyword verifies that the attribute does not match the specified value. It has a similar behavior to the api:equals keyword.

Parameters

• item: The item in which to compare the attribute. Specifying an item is not required; if no item is specified, the default output item is used instead.
• attr: The name of the attribute to compare.
• value: The reference value to compare the attribute with.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Example

<api:call op="fileListDir">
  <api:notequals attr="file:extension" value=".err">
    <api:push/>
  </api:notequals>
</api:call>

See Also

• api:equals: Check for equality.
• api:else: Create a block which is executed if the condition is not satisfied.

The api:null keyword checks that an attribute does not exist in the specified item.

Parameters

• item: The item to check. If not specified, the default output item is used.
• attr: The name of the attribute to check.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Example

<api:call op="fileListDir" output="out">
  <api:null attr="filename">
    <api:set attr="title" value="Unnamed File"/>
    <api:else>
      <api:set attr="title" value="[filename]"/>
    </api:else>
  </api:null>
  <api:push title="[title]">
  [out.file:*] 
  </api:push>
</api:call>

See Also

• api:else: Create a block which is executed if the condition is not satisfied.

The api:notnull keyword checks that an attribute has a value in the specified item. The api:exists keyword is a synonym for api:notnull.

Parameters

• item: The item to check. If not specified, the default output item is used.
• attr: The name of the attribute to check.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Example

<api:call op="fileListDir" output="out">
  <api:notnull attr="filename">
      <api:set attr="[title]" value="[filename]"/>
    <api:else>
      <api:set attr="[title]" value="Unnamed file"/>
    </api:else>
  </api:notnull> 
  <api:push title="[title]">
  [out.file:*]
  </api:push>
</api:call>

See Also

• api:else: Create a block which is executed if the condition is not satisfied.

The api:push keyword pushes an item into the output feed of the script. If there are no api:push elements in your script, no output items will result from it.

Parameters

• item: The item to push into the feed. If not present, the item at the top of the stack will be pushed.
• title: The title for the feed item.
• desc[ription]: The description for the feed item. If none is provided, the scope of the api:push keyword will be used as the value of this attribute. The scope of the api:push keyword may contain other keywords, HTML tags, etc. Everything is evaluated as a template and is used to set the description.
• op: The name of the operation to call and then push the resulting items. For example:
  

    <api:push op="myOp"/>    
    

  This is a shorthand for the following:
  

  <api:call op="myOp">
    <api:push/>
  </api:call>

• enc[oding]: The encoding of the description. By default, the Sync App pushes the description as escaped XML. You can choose to encode the description as CDATA to include unescaped XML by setting this parameter to "cdata".

Control Attributes

Example

<api:call op="fileListDir?mask=*.log">
  <api:push>
    The .log file contains details about the transmission, including any error messages.
  </api:push>
</api:call>

The api:script keyword can be used to create script blocks that respond to SQL statements.

Parameters

• method: The HTTP method (GET, POST, PUT/PATCH/MERGE, or and DELETE) used to invoke the script. Specify multiple methods in a comma-separated list. These methods correspond to SELECT, INSERT, UPDATE, and DELETE queries, respectively.

Control Attributes

None

Examples

Call two operations that manipulate remote REST. This example uses an OData API, the odata.org Northwind reference service. In the example below, the first block will be executed only when the script is accessed using the POST method (an INSERT statement is executed), while the second block will be executed only when the script is accessed using the GET method (a SELECT statement is executed).

<api:script method="POST">
  <api:set attr="sql.rec:name" value="[_input.name]"/>
  <api:set attr="sql.rec:address" value="[_input.address]"/>
  <api:call op="sqliteInsert" in="sql">
    <api:push/>
  </api:call>
</api:script>
<api:script method="GET">
  <api:set attr="sql.query" value="SELECT * FROM [sql.table];"/>
  <api:call op="sqliteQuery" in="sql">
    <api:push/>
  </api:call>
</api:script>

The api:select keyword is similar to a switch-case block in other programming languages and can be used to create complex conditional statements. The body of api:select can contain one or more api:case keywords and one api:default keyword.

The value in api:select is matched with those specified in api:case. The body of the api:case statement contains the keywords and statements to be executed if the value specified matches the value in the api:select keyword.

The body of the api:default statement will be executed only if none of the api:case statements result in a match. The api:default keyword has no parameters and can appear only once in an api:select.

Parameters

• value: The value to compare with those specified in api:case statements.
• attr: The attribute whose value is compared with values specified in api:case statements.

Control Attributes

None

Examples

Set the icon depending on the company name. The api:case elements match "CompanyA" and "CompanyB" in the company_name attribute, and, if any occurrences are found, take the action associated with that case.

<api:select value="[company_name]">
  <api:case value="CompanyA">
    <img src="http://www.companya.com/favicon.ico" />
  </api:case>
  <api:case value="CompanyB">
    <img src="http://www.companyb.com/favicon.ico" />
  </api:case>
  <api:default>
    <img src="http://www.myhosting.com/generic.ico"/>
  </api:default>
</api:select>

<p>

See Also

• api:case: Write cases for api:select.
• api:default: Write a default case for api:select.

The api:set keyword sets a value in an attribute. If an attribute is set in an item that does not exist, the item is automatically created.

There are two ways to set a value using api:set. You can set the value parameter or, for large values that are multiline and complex, you can set the value using the scope of the keyword itself.

Parameters

• item: The item parameter is used to specify the item in which the attribute is set. Specifying an item is not required. If an item is not specified, the default item is used.
• attr: The name of the attribute. You can also specify the item using the dot notation (e.g., item.prefix:attr). Note that a complete attribute name has both a prefix and an attribute name, but the prefix is not required.
• value: The value to be assigned to the attribute. If this parameter is not provided, the entire body of the api:set keyword is used as the value. This is convenient for setting long or multiline values.
• copyfrom: The attributes from the item specified in this parameter are copied to the item specified by the item parameter.

Control Attributes

Examples

Use the scope of the keyword to set a value to the message attribute of the item named "input":

<api:set item="input" attr="message">
  Dear [name],
  You have won a cruise trip to Hawaii.
  Please confirm your acceptance by [date].
  Thanks, [sales]
</api:set>

See Also

• api:unset: Remove an attribute from an item.
• api:setm: Set multiple attributes with only one keyword.

The api:setc keyword enables you to add static text without escaping the special characters in API Script, such as square brackets. While special characters can be escaped with a backslash, this keyword provides a shortcut. For example, this keyword can be used to easily set an XPath.

Parameters

• item: The item in which to set the attribute.
• attr: The name of the attribute.
• value: The value to be assigned to the attribute. If this parameter is not provided, the entire body of the api:setc keyword is used as the value. This is convenient for setting long or multiline values.
• copyfrom: The attributes from the item specified in this parameter are copied to the item specified by the item parameter.

Control Attributes

None

Examples

Set an unescaped XPath:

<api:setc attr="xpath" value="/root/book[1]/@name" />

The api:setm keyword is a shorthand for api:set that can be used to perform multiple sets with just one keyword.

Each line, separated by \r\n, is a separate set operation. Multiline values can be specified with three single quotes ('''), as in Python.

The first equals sign ("=") separates the attribute name from the value. This means that attribute values can contain spaces. However, leading and trailing spaces are ignored. Quotes can be used to include leading or trailing spaces, as shown in the examples.

Parameters

• item: The item in which the attributes are set. Specifying an item is not required. If an item is not specified, the default item is used.

Control Attributes

None

Examples

Use the scope of the keyword to set contact attributes:

<api:setm>
name = ContactName 
company = ContactCompany
address = 600 Market Street
includespace = " This string has a leading space"
</api:setm>

See Also

• api:set: Set attributes in an item.

The api:throw keyword is used to raise an error (exception) from within a script.

Parameters

• code: A string value used to identify the source or meaning of the exception. This parameter is required.
• desc[ription]: An optional parameter that specifies a short message describing the error condition.
• details: An optional parameter that can specify additional data useful to diagnose the error condition.

Control Attributes

None

Example

The example below explicitly defines both the error code and description:

<api:throw code="myerror" desc="thedescription" />

See Also

• api:try: Define a scope for catching exceptions.
• api:catch: Catch thrown exceptions and define exception processing.

The api:try and api:catch keywords are used to create an exception-handling block in a script. If any keyword inside the api:try body throws an APIException, the Sync App will look for a matching api:catch keyword inside the same scope and will execute the catch body.

Parameters

None

Control Attributes

None

Example

Throw and catch an exception. Inside the scope of the keyword, api:ecode and api:emessage attributes are added to the current item and pushed out.

<api:call op="...">
  <api:try>
    <api:throw code="myerror" description="thedescription" details="Other Details."/>
    <api:catch code="myerror">
      <api:set attr="api:ecode" value="[_code]"/>
      <api:set attr="api:emessage" value="[_description]: [_details]"/>
      <api:push/>
    </api:catch>
  </api:try>
</api:call>

See Also

• api:catch: Process thrown exceptions.
• api:throw: Force an exception.

The api:unset keyword is used to delete attributes from an item or delete the item itself.

Parameters

• item: The item from which the attribute is to be removed. If you do not specify an item, the default output item is used. This parameter can also be used to remove an item.
• attr: The attributes to delete from the item. You can use a glob mask to delete more than one parameter, for example, "*.foo".

Control Attributes

Examples

Remove an attribute from an item before it is pushed out:

<api:call op="fileListDir">
  <api:unset attr="file:size"/>
  <api:push/>
</api:call>

See Also

• api:set: Set attributes in an item.

You can use api:validate to throw an error if a required value is not provided.

Parameters

• item: The item that contains the attribute to be validated.
• attr: The attribute to be validated. If the attribute does not exist, an error is thrown.
• code: The error code to be thrown if validation fails. Default: "api:validate".
• desc: The error description to be thrown if validation fails. By default a message is thrown that the specified attribute is required.

Examples

Throw a more specific message if the _query item does not contain the Id attribute:

<api:validate attr="_query.Id"   desc="The Id is required to delete."/>

See Also

• api:throw: Raise an error (exception) from within a script.

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

Authentication

Connection

AWS Authentication

Azure Authentication

SSO

JSON and XML

CSV

OAuth

JWT OAuth

Kerberos

SSL

SSH

Firewall

Proxy

Logging

Schema

Miscellaneous

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

The type of authentication to use when connecting to remote services.

Remarks

Amazon S3

The following options are available when ConnectionType is set to Amazon S3:

• AwsRootKeys: Set this to use the root user access key and secret. Useful for quickly testing, but production use cases are encouraged to use something with narrowed permissions.
• AwsEC2Roles: Set this to automatically use IAM Roles assigned to the EC2 machine the CData Sync App is currently running on.
• AwsIAMRoles: Set to use IAM Roles for the connection.
• ADFS: Set to use a single sign on connection with ADFS as the identify provider.
• OKTA: Set to use a single sign on connection with OKTA as the identify provider.
• PingFederate: Set to use a single sign on connection with PingFederate as the identify provider.
• AwsMFA: Set to use multi factor authentication.
• AwsTempCredentials: Set this to leverage temporary security credentials alongside a session token to connect.
• AwsCredentialsFile: Set to use a credential file for authentication.
• AzureAD: Set to use a single sign on connection with AzureAD as the identify provider.

Various Azure Services

The following options are available when ConnectionType is set to Azure Blob Storage, Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, Azure Data Lake Storage Gen2 SSL, or OneDrive:

• AzureAD: Set this to perform Azure Active Directory OAuth authentication.
• AzureMSI: Set this to automatically obtain Managed Service Identity credentials when running on an Azure VM.
• AzureServicePrincipal: Set this to authenticate as an Azure Service Principal.
• AzureServicePrincipalCert: Set this to authenticate as an Azure Service Principal using a Certificate.
• AccessKey: Set this to authenticate with the storage key associated with your REST account.
• AzureStorageSAS: Set this to authenticate with Shared Access Signature (SAS).

Azure Files

• AccessKey: Set this to authenticate with the storage key associated with your REST account.
• AzureStorageSAS: Set this to authenticate with Shared Access Signature (SAS).

Box

The following options are available when ConnectionType is set to Box:

• OAuth: Uses either OAuth1 or OAuth2, with the specific flow being determined by the OAuthGrantType. OAuthVersion must be set to determine what version of OAuth is used.
• OAuthJWT: Uses OAuth2 with the JWT bearer grant type. OAuthJWTCertType and OAuthJWTCert determine what certificate the JWT is signed with. OAuthVersion must be set to 2.0.

Dropbox

Only the following option is available when ConnectionType is set to Dropbox:

OAuth: Uses either OAuth1 or OAuth2, with the specific flow being determined by the OAuthGrantType. OAuthVersion must be set to determine what version of OAuth is used.

FTP(S)

Only the following option is available when ConnectionType is set to FTP or FTPS:

Basic: Basic user credentials (user/password).

Various Google Services

The following options are available when ConnectionType points Google Cloud Storage or Google Drive:

• OAuth: Uses either OAuth1 or OAuth2, with the specific flow being determined by the OAuthGrantType. OAuthVersion must be set to determine what version of OAuth is used.
• OAuthJWT: Uses OAuth2 with the JWT bearer grant type. OAuthJWTCertType and OAuthJWTCert determine what certificate the JWT is signed with. OAuthVersion must be set to 2.0.
• GCPInstanceAccount: When running on a GCP virtual machine, the provider can authenticate using a service account tied to the virtual machine.

HDFS

The following options are available when ConnectionType is set to HDFS or HDFS Secure:

• None: No authentication is used.
• Negotiate: Kerberos authentication.

HTTP

The following options are available when ConnectionType is set to HTTP or HTTPS:

• None: No authentication is used.
• Basic: Basic user/password authentication.
• Digest: Uses HTTP Digest authentication with User and Password.
• OAuth: Uses either OAuth1 or OAuth2, with the specific flow being determined by the OAuthGrantType. OAuthVersion must be set to determine what version of OAuth is used.
  • Bearer Token authentication: AuthScheme=OAuth, InitiateOAuth=Off, and OAuthAccessToken=Bearer token value.
• OAuthJWT: Uses OAuth2 with the JWT bearer grant type. OAuthJWTCertType and OAuthJWTCert determine what certificate the JWT is signed with. OAuthVersion must be set to 2.0.
• OAuthPassword: Uses OAuth2 with the password grant type. User and Password are the credentials. OAuthVersion must be set to 2.0.
• OAuthClient: Uses OAuth2 with the client credentials grant type. OAuthClientId and OAuthClientSecret are the credentials. OAuthVersion must be set to 2.0.
• OAuthPKCE: Uses OAuth2 with the authorization code grant type and PKCE extension. OAuthClientId is the credential. OAuthVersion must be set to 2.0.

IBM Cloud Object Storage

The following options are also available when ConnectionType is set to IBM Object Storage Source:

• OAuth: Uses either OAuth with the specific flow being determined by the InitiateOAuth. ApiKey must be set to successfully complete this flow.
• HMAC: Uses AccessKey and SecretKey to authenticate to IBM Cloud Object Storage.

Oracle Cloud Storage

Only the following option is available when ConnectionType is set to Oracle Cloud Storage:

HMAC: Uses AccessKey and SecretKey to authenticate to the Oracle Cloud Storage.

SFTP

This ConnectionType defaults to using an AuthScheme called SFTP, but the authentication method is actually controlled using the SSHAuthMode property. See this property's documentation for further information.

SharePoint REST

The following options are also available when ConnectionType is set to SharePoint REST:

• AzureAD: Set this to perform Azure Active Directory OAuth authentication.
• AzureMSI: Set this to automatically obtain Managed Service Identity credentials when running on an Azure VM.
• AzureServicePrincipal: Set this to authenticate as an Azure Service Principal.
• AzureServicePrincipalCert: Set this to authenticate as an Azure Service Principal using a Certificate.

SharePoint SOAP

The following options are also available when ConnectionType is set to SharePoint SOAP:

• Basic: Use basic user/password credentials to authenticate.
• ADFS: Set to use a single sign on connection with ADFS as the identify provider.
• Okta: Set to use a single sign on connection with OKTA as the identify provider.
• OneLogin: Set to use a single sign on connection with OneLogin as the identify provider.

Your account access key. This value is accessible from your security credentials page.

Remarks

Your account access key. This value is accessible from your security credentials page depending on the service you are using.

Your account secret key. This value is accessible from your security credentials page.

Remarks

Your account secret key. This value is accessible from your security credentials page depending on the service you are using.

The API Key used to identify the user to IBM Cloud.

Remarks

Access to resources in the REST REST API is governed by an API key in order to retrieve token. An API Key can be created by navigating to Manage --> Access (IAM) --> Users and clicking 'Create'.

The user account used to authenticate.

Remarks

Together with Password, this field is used to authenticate against the server.

This property will refer to different things based on the context, namely the value of ConnectionType and AuthScheme:

• ConnectionType=AmazonS3
  • AuthScheme=ADFS: This refers to your ADFS username.
  • AuthScheme=Okta: This refers to your Okta username.
  • AuthScheme=PingFederate: This refers to your PingFederate username.
• ConnectionType=FTP(S)
  • AuthScheme=Basic: This refers to your FTP(S) server username.
• ConnectionType=HDFS/HDFS Secure
  • AuthScheme=Negotiate: This refers to your HDFS intance username.
• ConnectionType=HTTP(S)
  • AuthScheme=Basic: This refers to the username associated with the HTTP stream.
  • AuthScheme=Digest: This refers to the username associated with the HTTP stream.
  • AuthScheme=OAuthPassword: This refers to the username associated with the HTTP stream.
• ConnectionType=SharePoint SOAP
  • AuthScheme=Basic: This refers to your SharePoint account username.
  • AuthScheme=ADFS: This refers to your ADFS username.
  • AuthScheme=Okta: This refers to your Okta username.
  • AuthScheme=OneLogin: This refers to your OneLogin username.

The password used to authenticate the user.

Remarks

The User and Password are together used to authenticate with the server.

This property will refer to different things based on the context, namely the value of ConnectionType and AuthScheme:

• ConnectionType=AmazonS3
  • AuthScheme=ADFS: This refers to your ADFS password.
  • AuthScheme=Okta: This refers to your Okta password.
  • AuthScheme=PingFederate: This refers to your PingFederate password.
• ConnectionType=FTP(S)
  • AuthScheme=Basic: This refers to your FTP(S) server password.
• ConnectionType=HDFS/HDFS Secure
  • AuthScheme=Negotiate: This refers to your HDFS intance password.
• ConnectionType=HTTP(S)
  • AuthScheme=Basic: This refers to the password associated with the HTTP stream.
  • AuthScheme=Digest: This refers to the password associated with the HTTP stream.
  • AuthScheme=OAuthPassword: This refers to the password associated with the HTTP stream.
• ConnectionType=SharePoint SOAP
  • AuthScheme=Basic: This refers to your SharePoint account password.
  • AuthScheme=ADFS: This refers to your ADFS password.
  • AuthScheme=Okta: This refers to your Okta password.
  • AuthScheme=OneLogin: This refers to your OneLogin password.

The edition of SharePoint being used. Set either SharePointOnline or SharePointOnPremise.

Remarks

The edition of SharePoint being used. Set either SharePointOnline or SharePointOnPremise.

This section provides a complete list of the Connection properties you can configure in the connection string for this provider.

Specifies the file storage service, server, or file access protocol through which your REST files are stored and retreived.

Remarks

Set the ConnectionType to one of the following:

• Auto: The Sync App infers the connection type from the syntax of the provided URI.
• Local: REST files stored on your local machine.
• Amazon S3
• Azure Blob Storage
• Azure Data Lake Storage Gen1
• Azure Data Lake Storage Gen2
• Azure Data Lake Storage Gen2 SSL
• Azure Files
• Box
• Dropbox
• FTP
• FTPS
• Google Cloud Storage
• Google Drive
• HDFS
• HDFS Secure
• HTTP: Connects to REST files hosted on HTTP streams.
• HTTPS: Connects to REST files hosted on HTTPS streams.
• IBM Object Storage Source
• OneDrive
• Oracle Cloud Storage
• SFTP
• SharePoint REST
• SharePoint SOAP

The Format property specifies the format reported by the data source.

Remarks

This property is required when using the CreateSchema stored procedure or use the Generate Schema File feature (Set GenerateSchemFiles to OnStart or OnUse)

The Uniform Resource Identifier (URI) for the XML/JSON/CSV resource location.

Remarks

Set the URI property to specify a path to a file or stream.

NOTE:

• This connection property requires that you set ConnectionType.
• If specifying a directory path, it is generally recommended to end the URI with a trailing path separator character, as an example 'folder1/' instead of 'folder1'.

See Fine-Tuning Data Access for more advanced features available for parsing and merging multiple files.

Below are examples of the URI formats for the available data sources:

Example Connection Strings and Queries

Below are example connection strings to XML/JSON/CSV files or streams.

The hosting region for your S3-like Web Services.

Remarks

The hosting region for your S3-like Web Services.

Oracle Cloud Object Storage Regions

Wasabi Regions

The Id of the project where your Google Cloud Storage instance resides.

Remarks

The Id of the project where your Google Cloud Storage instance resides. You can find this value by going to Google Cloud Console and clicking the project name at the top left screen. The ProjectId is displayed on the Id column of the matching project.

The Oracle Cloud Object Storage namespace to use.

Remarks

The Oracle Cloud Object Storage namespace to use. This setting must be set to the Oracle Cloud Object Storage namespace associated with the Oracle Cloud account before any requests can be made. Refer to the Understanding Object Storage Namespaces page of the Oracle Cloud documentation for instructions on how to find your account's Object Storage namespace.

The URL of a cloud storage service provider.

Remarks

This connection property is used to specify:

• The URL of a custom S3 service.
• The URL required for the SharePoint SOAP/REST cloud storage service provider.

  If the domain for this option ends in -my (for example, https://bigcorp-my.sharepoint.com) then you may need to use the onedrive:// scheme instead of the sp:// or sprest:// scheme.

This setting specifies the threshold, in bytes, above which the provider will choose to perform a multipart upload rather than uploading everything in one request.

Remarks

This setting specifies the threshold, in bytes, above which the Sync App will choose to perform a multipart upload rather than uploading everything in one request.

If true (default), buckets will be referenced in the request using the hosted-style request: http://yourbucket.s3.amazonaws.com/yourobject. If set to false, the bean will use the path-style request: http://s3.amazonaws.com/yourbucket/yourobject. Note that this property will be set to false, in case of an S3 based custom service when the CustomURL is specified.

Remarks

If true (default), buckets will be referenced in the request using the hosted-style request: http://yourbucket.s3.amazonaws.com/yourobject. If set to false, the bean will use the path-style request: http://s3.amazonaws.com/yourbucket/yourobject. Note that this property will be set to false, in case of an S3 based custom service when the CustomURL is specified.

When this property is set to true, AWSLakeFormation service will be used to retrieve temporary credentials, which enforce access policies against the user based on the configured IAM role. The service can be used when authenticating through OKTA, ADFS, AzureAD, PingFederate, while providing a SAML assertion.

Remarks

When this property is set to true, AWSLakeFormation service will be used to retrieve temporary credentials, which enforce access policies against the user based on the configured IAM role. The service can be used when authenticating through OKTA, ADFS, AzureAD, PingFederate, while providing a SAML assertion.

This section provides a complete list of the AWS Authentication properties you can configure in the connection string for this provider.

Your AWS account access key. This value is accessible from your AWS security credentials page.

Remarks

Your AWS account access key. This value is accessible from your AWS security credentials page:

1. Sign into the AWS Management console with the credentials for your root account.
2. Select your account name or number and select My Security Credentials in the menu that is displayed.
3. Click Continue to Security Credentials and expand the Access Keys section to manage or create root account access keys.

Your AWS account secret key. This value is accessible from your AWS security credentials page.

Remarks

Your AWS account secret key. This value is accessible from your AWS security credentials page:

1. Sign into the AWS Management console with the credentials for your root account.
2. Select your account name or number and select My Security Credentials in the menu that is displayed.
3. Click Continue to Security Credentials and expand the Access Keys section to manage or create root account access keys.

The Amazon Resource Name of the role to use when authenticating.

Remarks

When authenticating outside of AWS, it is common to use a Role for authentication instead of your direct AWS account credentials. Entering the AWSRoleARN will cause the CData Sync App to perform a role based authentication instead of using the AWSAccessKey and AWSSecretKey directly. The AWSAccessKey and AWSSecretKey must still be specified to perform this authentication. You cannot use the credentials of an AWS root user when setting RoleARN. The AWSAccessKey and AWSSecretKey must be those of an IAM user.

The ARN of the SAML Identity provider in your AWS account.

Remarks

The ARN of the SAML Identity provider in your AWS account.

The hosting region for your Amazon Web Services.

Remarks

The hosting region for your Amazon Web Services. Available values are OHIO, NORTHERNVIRGINIA, NORTHERNCALIFORNIA, OREGON, CAPETOWN, HONGKONG, JAKARTA, MUMBAI, OSAKA, SEOUL, SINGAPORE, SYDNEY, TOKYO, CENTRAL, BEIJING, NINGXIA, FRANKFURT, IRELAND, LONDON, MILAN, PARIS, STOCKHOLM, ZURICH, BAHRAIN, UAE, SAOPAULO, GOVCLOUDEAST, and GOVCLOUDWEST.

The path to the AWS Credentials File to be used for authentication.

Remarks

The path to the AWS Credentials File to be used for authentication. See https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-files.html for more information.

The name of the profile to be used from the supplied AWSCredentialsFile.

Remarks

The name of the profile to be used from the supplied AWSCredentialsFile. See https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-files.html for more information.

Your AWS session token.

Remarks

Your AWS session token. This value can be retrieved in different ways. See this link for more info.

A unique identifier that might be required when you assume a role in another account.

Remarks

A unique identifier that might be required when you assume a role in another account.

The serial number of the MFA device if one is being used.

Remarks

You can find the device for an IAM user by going to the AWS Management Console and viewing the user's security credentials. For virtual devices, this is actually an Amazon Resource Name (such as arn:aws:iam::123456789012:mfa/user).

The temporary token available from your MFA device.

Remarks

If MFA is required, this value will be used along with the MFASerialNumber to retrieve temporary credentials to login. The temporary credentials available from AWS will only last up to 1 hour by default (see TemporaryTokenDuration). Once the time is up, the connection must be updated to specify a new MFA token so that new credentials may be obtained. %AWSpSecurityToken; %AWSpTemporaryTokenDuration;

The amount of time (in seconds) a temporary token will last.

Remarks

Temporary tokens are used with both MFA and Role based authentication. Temporary tokens will eventually time out, at which time a new temporary token must be obtained. For situations where MFA is not used, this is not a big deal. The CData Sync App will internally request a new temporary token once the temporary token has expired.

However, for MFA required connection, a new MFAToken must be specified in the connection to retrieve a new temporary token. This is a more intrusive issue since it requires an update to the connection by the user. The maximum and minimum that can be specified will depend largely on the connection being used.

For Role based authentication, the minimum duration is 900 seconds (15 minutes) while the maximum if 3600 (1 hour). Even if MFA is used with role based authentication, 3600 is still the maximum.

For MFA authentication by itself (using an IAM User or root user), the minimum is 900 seconds (15 minutes), the maximum is 129600 (36 hours).

When activated, file uploads into Amazon S3 buckets will be server-side encrypted.

Remarks

Server-side encryption is the encryption of data at its destination by the application or service that receives it. Amazon S3 encrypts your data at the object level as it writes it to disks in its data centers and decrypts it for you when you access it. Learn more: https://docs.aws.amazon.com/AmazonS3/latest/userguide/serv-side-encryption.html

A BASE64-encoded UTF-8 string holding JSON which represents a string-string (key-value) map.

Remarks

Example of what the JSON may look decoded: {"aws:s3:arn": "arn:aws:s3:::_bucket_/_object_"}.

Configuration to use an S3 Bucket Key at the object level when encrypting data with AWS KMS. Enabling this will reduce the cost of server-side encryption by lowering calls to AWS KMS.

Remarks

Configuration to use an S3 Bucket Key at the object level when encrypting data with AWS KMS. Enabling this will reduce the cost of server-side encryption by lowering calls to AWS KMS.

A symmetric encryption KeyManagementService key, that is used to protect the data when using ServerSideEncryption.

Remarks

A symmetric encryption KeyManagementService key, that is used to protect the data when using ServerSideEncryption.

This section provides a complete list of the Azure Authentication properties you can configure in the connection string for this provider.

The name of your Azure storage account.

Remarks

The name of your Azure storage account.

The storage key associated with your Azure account.

Remarks

The storage key associated with your REST account. You can retrieve it as follows:

1. Sign into the azure portal with the credentials for your root account. (https://portal.azure.com/)
2. Click on storage accounts and select the storage account you want to use.
3. Under settings, click Access keys.
4. Your storage account name and key will be displayed on that page.

A shared access key signature that may be used for authentication.

Remarks

A shared access signature. You can create one by following these steps:

1. Sign into the azure portal with the credentials for your root account. (https://portal.azure.com/)
2. Click on storage accounts and select the storage account you want to use.
3. Under settings, click Shared Access Signature.
4. Set the permissions and when the token will expire
5. Click Generate SAS can copy the token.

The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

Remarks

The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

The Azure Environment to use when establishing a connection.

Remarks

In most cases, leaving the environment set to global will work. However, if your Azure Account has been added to a different environment, the AzureEnvironment may be used to specify which environment. The available values are GLOBAL, CHINA, USGOVT, USGOVTDOD.

This section provides a complete list of the SSO properties you can configure in the connection string for this provider.

The identity provider's login URL.

Remarks

The identity provider's login URL.

Additional properties required to connect to the identity provider in a semicolon-separated list.

Remarks

Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.

SSO configuration is discussed further in .

The URL used for consuming the SAML response and exchanging it for service specific credentials.

Remarks

The CData Sync App will use the URL specified here to consume a SAML response and exchange it for service specific credentials. The retrieved credentials are the final piece during the SSO connection that are used to communicate with REST.

This section provides a complete list of the JSON and XML properties you can configure in the connection string for this provider.

The XPath of an element that repeats at the same height within the XML/JSON document (used to split the document into multiple rows).

Remarks

This parameter specifies the XPath (or JSONPath syntax) of an element that has the same name as other elements at the same height.

Multiple paths can be specified using a semi-colon separated list. DataModel allows you to configure how the XPath values will be used to create tables and display data.

If left empty, the CData Sync App will determine the XPaths by parsing the REST document and identifying the object arrays.

This property will be used to generate the schema definition when a schema file (RSD) file is not present.

Specifies the data model to use when parsing XML/JSON documents and generating the database metadata.

Remarks

The Sync App splits JSON documents into rows based on the objects nested in arrays. Select a DataModel configuration to configure how the Sync App models nested object arrays into tables. See Parsing Hierarchical Data for examples of querying the data in the different configurations.

Selecting a Data Modeling Strategy

The following DataModel configurations are available. See Parsing Hierarchical Data for examples of querying the data in the different configurations.

• Document

  Returns a single table representing a row for each top-level object. In this data model, any nested object arrays will not be flattened and will be returned as aggregates. Unless an XPath value is explicitly specified, the Sync App will identify and use the top-most object array found as the XPath.

• FlattenedDocuments

  Returns a single table representing a JOIN of the available documents in the file. In this data model, nested XPath values will act in the same manner as a SQL JOIN. Additionally, nested sibling XPath values (child paths at the same height), will be treated as a SQL CROSS JOIN. Unless explicitly specified, the Sync App will identify the XPath values available by parsing the file and identifying the available documents, including nested documents.

• Relational

  Returns multiple tables, one for each XPath value specified. In this data model, any nested documents (object arrays) will be returned as relational tables that contain a primary key and a foreign key that links to the parent table. Unless explicitly specified, the Sync App will identify the XPath values available by parsing the file and identifying the available documents (including nested documents).

See Also

• XPath: Explicitly set the paths to the documents you want to include.
• FlattenArrays and FlattenObjects: Customize the columns that will be identified for each of these data models. See Automatic Schema Discovery for examples of using these properties.
• Parsing Hierarchical Data: Compare the schemas resulting from different DataModel settings, with example queries.
• Modeling REST Data: Learn about the data modeling and flattening techniques available in the Sync App.

Specifies the format of the JSON document. Only has an effect when Format is set to JSON.

Remarks

This option allows you to specify the format of the JSON document which enables parsing specifically for the selected format.

JSON Formats

The following JSONFormat configurations are available.

• JSON

  This is the default format and should be used in the majority of cases.

• JSONRows

  This is a specific format in which data is returned in a relational format consisting of rows of data contained within primitive arrays. Column information is returned as well in a separate array.

  Note: DataModel does not apply when using this JSONFormat.

  Example:
  

    {
      "dataset": {
        "column_names": [
          "Name",
          "Age",
          "Gender"
        ],
        "data": [
          [
            "John Doe",
            37,
            "M"
          ],
          [
            "David Thomas",
            25,
            "M"
          ]
        ]
      }
    }
    

  The XPath property requires special syntax to identify the column and row paths. The syntax consists of specifying a path for each using a "column:" and "row:" prefix. Using the example above, the XPath would be set to: column:/dataset/column_names;row:/dataset/data

  In the case that columns are returned in an object with additional data, an additional "columnname:" prefix can be specified to identify the path to the value containing the column name.

  Example:
  

    {
      "columns": [
        {
          "name":"first_name",
          "type":"text"
        },
        {
          "name":"last_name",
          "type":"text"
        }
      ],
      "rows": [
        [
          "John",
          "Doe"
        ],
        [
          "David",
          "Thomas"
        ]
      ]
    }
    

  In the above example, XPath would be set to: column:/columns;columnname:/columns.name;row:/rows

• LDJSON (Line-Delimited JSON)

  This format is used to parse line-delimited JSON files (also known as NDJSON or JSONLines). Line-delimited JSON files contain a separate JSON document on each line.

  Example LDJSON File:
  

    { "Name": "John Doe", "Age": 37, "Gender": "M" }
    { "Name": "David Thomas", "Age": 25, "Gender": "M" }
    { "Name": "Susan Price", "Age": 35, "Gender": "F" }
    

  The XPath value is treated the same as when using the regular JSON format. The only difference is that the root path ($.) is always used (therefore treating all the lines of JSON as it is contained within an array).

  In the above example, the XPath will be "/", which will return 3 rows containing the columns: Name, Age, and Gender.

Specifies the format of the XML document.

Remarks

The following XMLFormat configurations are available.

• XML

  This is the default format and should be used in the majority of cases. The element or attribute name containing each value is used as the column name for that value.

• XMLTable

  This format is for when the column name is separate from the data contained in that column. This is useful when the element or attribute containing the data has a generic name (like "Value") instead of being specific to each column.

  Note: DataModel does not apply when using this XMLFormat.

  Example:
  

      <Report>
        <Table>
            <Row>
                <Value label="Customer">Mark Rodgers</Value>
                <Value label="SupportCost">89.28</Value>
                <Value label="ContractValue">299.99</Value>
            </Row>
            <Row>
                <Value label="Customer">Hank Howards</Value>
                <Value label="SupportCost">225.63</Value>
                <Value label="ContractValue">0.00</Value>
            </Row>
        </Table>
      </Report>
    

  The XPath property requires special syntax to identify the column and row paths. Each path is given with a prefix depending on the type of path. All of the following are required:

  • row: This is the XPath of the element that contains each row's data. In the above example, this is: row:/Report/Table/Row
  • name: This is the XPath of the element containing the column name. In the above example, this is: column:/Report/Table/Row/Value@label
  • value: This is the XPath of the element that contains each column's data inside the row. In the above example, this is: value:/Report/Table/Row/Value

  Each of these paths is separated by a semicolon, so the complete XPath for the above example is: row:/Report/Table/Row;name:/Report/Table/Row/Value@label;value:/Report/Table/Row/Value