The CData Sync App is designed for streaming XML only.

This streamed file content does not include all of the metadata associated with remotely stored XML 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 XML 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 XML file metadata.

Create a connection to XML by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the XML icon is not available, click the Add More icon to download and install the XML connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

The CData Sync App allows connecting to local and remote XML resources. Set the URI property to the XML resource location, in addition to any other properties necessary to connect to your data source.

Connecting to Local Files

Set the ConnectionType to Local. Local files support SELECT\INSERT\UPDATE\DELETE.

Set the URI to a folder containing XML files: C:\folder1.

Connecting to Cloud-Hosted XML Files

If you need INSERT/UPDATE/DELETE cloud files, you can download the corresponding CData Sync App for that cloud host (supported via stored procedures), make changes with the local file's corresponding Sync App, then upload the file using the cloud source's stored procedures.

As an example, if you wanted to update a file stored on SharePoint, you could use the CData SharePoint Sync App's DownloadDocument procedure to download the XML file, update the local XML file with the CData XML Sync App, then use the SharePoint Sync App's UploadDocument procedure to upload the changed file to SharePoint.

A unique prefix at the beginning of the URI connection property is used to identify the cloud data store being targed by the Sync App and the remainder of the path is a relative path to the desired folder (one table per file) or single file (a single table).

Amazon S3

Set the following to identify your XML resources stored on Amazon S3:

• ConnectionType: Set the ConnectionType to Amazon S3.
• URI: Set this to an XML document in a bucket: s3://bucket1/folder1.

See Connecting to Amazon S3 for more information regarding how to connect and authenticate to XML files hosted on Amazon S3.

Azure Blob Storage

Set the following to identify your XML resources stored on Azure Blob Storage:

• ConnectionType: Set this to Azure Blob Storage.
• URI: Set this to the name of your container and the name of the blob. For example: azureblob://mycontainer/myblob.

See Connecting to Azure Blob Storage for more information regarding how to connect and authenticate to XML files hosted on Amazon Blob Storage.

Azure Data Lake Storage

Set the following to identify your XML resources stored on Azure Data Lake Storage:

• ConnectionType: Set this to Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, or Azure Data Lake Storage Gen2 SSL.
• URI: Set this to the name of the file system, the name of the folder which contains your XML files, and the name of an XML file. For example:
  • Gen 1: adl://myfilesystem/folder1
  • Gen 2: abfs://myfilesystem/folder1
  • Gen 2 SSL: abfss://myfilesystem/folder1

See Connecting to Azure Data Lake Storage for more information regarding how to connect and authenticate to XML files hosted on Azure Data Lake Storage.

Azure File Storage

Set the following properties to connect:

• ConnectionType: Set this to Azure Files.
• URI: Set this the name of your azure file share and the name of the resource. For example: azurefile://fileShare/remotePath.
• AzureStorageAccount (Required): Set this to the account associated with the Azure file.

You can authenticate either an Azure access key or an Azure shared access signature. Set one of the following:

• AzureAccessKey: Set this to the access key associated with the Azure file.
• AzureSharedAccessSignature: Set this to the shared access signature associated with the Azure file.

Box

Set the following to identify your XML resources stored on Box:

• ConnectionType: Set this to Box.
• URI: Set this the name of the file system, the name of the folder which contains your XML files, and the name of an XML file. For example: box://folder1.

See Connecting to Box for more information regarding how to connect and authenticate to XML files hosted on Box.

Dropbox

Set the following to identify your XML resources stored on Dropbox:

• ConnectionType: Set this to Dropbox.
• URI: Set this to the path to a XML file. For example: dropbox://folder1.

See Connecting to Dropbox for more information regarding how to connect and authenticate to XML files hosted on Dropbox.

FTP

The Sync App supports both plaintext and SSL/TLS connections to FTP servers.

Set the following connection properties to connect:

• ConnectionType: Set this to either FTP or FTPS.
• URI: Set this to the address of the server followed by the path to the XML file. For example: ftp://localhost:990/folder1 or ftps://localhost:990/folder1.
• User: Set this to your username on the FTP(S) server you want to connect to.
• Password: Set this to your password on the FTP(S) server you want to connect to.

Google Cloud Storage

Set the following to identify your XML resources stored on Google Cloud Storage:

• ConnectionType: Set this to Google Cloud Storage.
• URI: Set this to the path to the name of the file system, the name of the folder which contains your XML files, and the name of a XML file. For example: gs://bucket/remotePath.

See Connecting to Google Cloud Storage for more information regarding how to connect and authenticate to XML files hosted on Google Cloud Storage.

Google Drive

Set the following to identify your XML resources stored on Google Drive:

• ConnectionType: Set this to Google Drive.
• URI: Set to the path to the name of the file system, the name of the folder which contains your XML files, and the name of an XML file. For example: gdrive://folder1.

See Connecting to Google Drive for more information regarding how to connect and authenticate to XML files hosted on Google Drive.

HDFS

Set the following to identify your XML resources stored on HDFS:

• ConnectionType: Set this to HDFS or HDFS Secure.
• URI: Set this to the path to a XML file. For example:
  • HDFS: webhdfs://host:port/remotePath
  • HDFS Secure: webhdfss://host:port/remotePath

There are two authentication methods available for connecting to HDFS data source, Anonymous Authentication and Negotiate (Kerberos) Authentication.

Anonymous Authentication

In some situations, you can connect to HDFS without any authentication connection properties. To do so, set the AuthScheme property to None (default).

Authenticate using Kerberos

When authentication credentials are required, you can use Kerberos for authentication. See Using Kerberos for details on how to authenticate with Kerberos.

HTTP Streams

Set the following to identify your XML resources stored on HTTP streams:

• ConnectionType: Set this to HTTP or HTTPS.
• URI: Set this to the URI of your HTTP(S) stream. For example:
  • HTTP: http://remoteStream
  • HTTPS: https://remoteStream

See Connecting to HTTP Streams for more information regarding how to connect and authenticate to XML files hosted on HTTP Streams.

IBM Cloud Object Storage

Set the following to identify your XML resources stored on IBM Cloud Object Storage:

• ConnectionType: Set this to IBM Object Storage Source.
• URI: Set this to the bucket and folder. For example: ibmobjectstorage://bucket1/remotePath.

See Connecting to IBM Object Storage for more information regarding how to connect and authenticate to XML files hosted on IBM Cloud Object Storage.

OneDrive

Set the following to identify your XML resources stored on OneDrive:

• ConnectionType: Set this to OneDrive.
• URI: Set this to the path to a XML file. For example: onedrive://remotePath.

See Connecting to OneDrive for more information regarding how to connect and authenticate to XML files hosted on OneDrive.

Oracle Cloud Storage

Set the following properties to authenticate with HMAC:

• ConnectionType: Set the ConnectionType to Oracle Cloud Storage.
• URI: Set this to an XML document in a bucket: os://bucket/remotePath.
• AccessKey: Set this to an Oracle Cloud Access Key.
• SecretKey: Set this to an Oracle Cloud Secret Key.
• OracleNamespace: Set this to an Oracle cloud namespace.
• Region (optional): Set this to the hosting region for your S3-like Web Services.

SFTP

Set the following to identify your XML resources stored on SFTP:

• ConnectionType: Set this to SFTP.
• URI: Set this to the address of the server followed by the path to the folder to be used as the root folder. For example: sftp://server:port/remotePath.

See Connecting to SFTP for more information regarding how to connect and authenticate to XML files hosted on SFTP.

SharePoint Online

Set the following to identify your XML resources stored on SharePoint Online:

• ConnectionType: Set this to SharePoint REST or SharePoint SOAP.
• URI: Set this to a document library containing XML files. For example:
  • SharePoint Online REST: sprest://remotePath
  • SharePoint Online SOAP: sp://remotePath

See Connecting to SharePoint Online for more information regarding how to connect and authenticate to XML files hosted on SharePoint Online.

Connecting to HTTP XML Streams

Set the URI to the HTTP or HTTPS URL of the XML resource you want to access as a table. Set AuthScheme to use the following authentication types. The Sync App also supports OAuth authentication; see Using OAuth for more information.

• HTTP:To use HTTP Basic or Digest, set the User and Password and set the corresponding AuthScheme. Set CustomHeaders if you need access to the request headers. Set CustomUrlParams to modify the URL query string.
• Windows (NTLM): Set the Windows User and Password to connect and set AuthScheme to "NTLM".
• Kerberos and Kerberos Delegation: To authenticate with Kerberos, set the User and Password and set AuthScheme to NEGOTIATE. To use Kerberos Delegation, set AuthScheme to KERBEROSDELEGATION.

For example:

URI=http://www.host1.com/streamname1;AuthScheme=BASIC;User=admin;Password=admin

Securing XML Connections

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.

After connecting to your data source, set DataModel to more closely match the data representation to the structure of your data.

Connecting to XML

Below are example connection strings to XML files or streams, using the Sync App's default data modeling configuration (see below)

Modeling XML Data

The DataModel property is the controlling property over how your data is represented into tables and toggles the following basic configurations.

• Document (default): Model a top-level, document view of your XML data. The Sync App returns nested object arrays as aggregated XML 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.

Next Steps

• See Parsing Hierarchical Data for examples showing how to query a dataset in each DataModel configuration.
• See Modeling XML Data for information on customizing schema discovery and how to execute SQL to XML.
• See Fine-Tuning Data Access for more advanced connection settings: fine-tune the default data modeling settings, connect through a firewall, or troubleshoot connections.

Before You Connect

Obtain AWS Keys

To obtain the credentials for an IAM user, follow the steps below:

1. Sign into the IAM console.
2. In the navigation pane, select Users.
3. To create or manage the access keys for a user, select the user and then go to the Security Credentials tab.

To obtain the credentials for your AWS root account, follow the steps below:

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.

Connecting to Amazon S3

Specify the following to connect to data:

• StorageBaseURL: Specify the base S3 service URL if it has a different URL from "amazonaws.com". Make sure to specify the full URL. For example: http://127.0.0.1:9000.
• AWSRegion: Set this to the region where your XML data is hosted.

Authenticating to Amazon S3

There are several authentication methods available for connecting to XML including:

• Root Credentials
• AWS Role, as an AWS Role (from an EC2 Instance or by specifying the root credentials)
• SSO (ADFS, Okta, PingFederate)
• MFA
• Temporary Credentials
• Credentials File

Root Credentials

To authenticate using account root credentials, set the following:

• AuthScheme: Set this to AwsRootKeys.
• AWSAccessKey: The access key associated with the AWS root account.
• AWSSecretKey: The secret key associated with the AWS root account.

Note: Use of this authentication scheme is discouraged by Amazon for anything but simple tests. The account root credentials have the full permissions of the user, making this the least secure authentication method.

EC2 Instances

If you are using the Sync App from an EC2 Instance and have an IAM Role assigned to the instance, you can use the IAM Role to authenticate. To do so, set the following properties to authenticate:

• AuthScheme: Set this to AwsEC2Roles.

If you are also using an IAM role to authenticate, you must additionally specify the following:

• AWSRoleARN: Specify the Role ARN for the role you'd like to authenticate with. This will cause the Sync App to attempt to retrieve credentials for the specified role.
• AWSExternalId: Only if required when you assume a role in another account.

IMDSv2 Support

The XML Sync App now supports IMDSv2. Unlike IMDSv1, the new version requires an authentication token. Endpoints and response are the same in both versions. In IMDSv2, the XML Sync App first attempts to retrieve the IMDSv2 metadata token and then uses it to call AWS metadata endpoints. If it is unable to retrieve the token, the Sync App reverts to IMDSv1.

AWS IAM Roles

In many situations it may be preferable to use an IAM role for authentication instead of the direct security credentials of an AWS root user.

To authenticate as an AWS role, set the following:

• AuthScheme: Set this to AwsIAMRoles.
• AWSRoleARN: Specify the Role ARN for the role you'd like to authenticate with. This will cause the Sync App to attempt to retrieve credentials for the specified role.
• AWSExternalId: Only if required when you assume a role in another account.
• AWSAccessKey: The access key of the IAM user to assume the role for.
• AWSSecretKey: The secret key of the IAM user to assume the role for.

Note: Roles may not be used when specifying the AWSAccessKey and AWSSecretKey of an AWS root user.

ADFS

Set the AuthScheme to ADFS. The following connection properties need to be set:

• User: Set this to your ADFS username.
• Password: Set this to your ADFS password.
• SSOLoginURL: Set this to the login URL used by the SSO provider.

AuthScheme=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';

ADFS Integrated

To use the ADFS Integrated flow, specify the SSOLoginURL and leave the username and password empty.

Okta

Set the AuthScheme to Okta. The following connection properties are used to authenticate through Okta:

• User: Set to your Okta user.
• Password: Set to your Okta password.
• SSOLoginURL: Set to the login URL used by the SSO provider.

• using a trusted application or proxy that overrides the Okta client request
• configuring MFA

then you need to use combinations of SSOProperties input parameters to authenticate using Okta. Otherwise, you do not need to set any of these values.

In SSOProperties when required, set these input parameters:

• APIToken: When authenticating a user via a trusted application or proxy that overrides the Okta client request context, set this to the API Token the customer created from the Okta organization.
• MFAType: Set this if you have configured the MFA flow. Currently we support the following types: OktaVerify, Email, and SMS.
• MFAPassCode: Set this only if you have configured the MFA flow. If you set this to empty or an invalid value, the Sync App issues a one-time password challenge to your device or email. After the passcode is received, reopen the connection where the retrieved one-time password value is set to the MFAPassCode connection property.
• MFARememberDevice: Okta supports remembering devices when MFA is required. If remembering devices is allowed according to the configured authentication policies, the Sync App sends a device token to extend MFA authentication lifetime. This property is, by default, set to True. Set this to False only if you do not want MFA to be remembered.

Example connection string:

AuthScheme=Okta;SSOLoginURL='https://example.okta.com/home/appType/0bg4ivz6cJRZgCz5d6/46';User=oktaUserName;Password=oktaPassword;

PingFederate

Set the AuthScheme to PingFederate. The following connection properties need to be set:

• User: Set this to the PingFederate user.
• Password: Set this to PingFederate password for the user.
• SSOLoginURL: Set this to the login url used by the SSO provider.
• SSOExchangeUrl: The Partner Service Identifier URI configured in your PingFederate server instance under: SP Connections > SP Connection > WS-Trust > Protocol Settings. This should uniquely identify a PingFederate SP Connection, so it is a good idea to set it to your AWS SSO ACS URL. You can find it under AWS SSO > Settings > View Details next to the Authentication field.

• AuthScheme (optional): The authorization scheme to be used for the IdP endpoint. The allowed values for this IdP are None or Basic.

• SSLClientCert
• SSLClientCertType
• SSLClientCertSubject
• SSLClientCertPassword

authScheme=pingfederate;SSOLoginURL=https://mycustomserver.com:9033/idp/sts.wst;SSOExchangeUrl=https://us-east-1.signin.aws.amazon.com/platform/saml/acs/764ef411-xxxxxx;user=admin;password=PassValue;AWSPrincipalARN=arn:aws:iam::215338515180:saml-provider/pingFederate;AWSRoleArn=arn:aws:iam::215338515180:role/SSOTest2;

MFA

For users and roles that require Multi-factor Authentication, specify the following to authenticate:

• AuthScheme: Set this to AwsMFA.
• CredentialsLocation: The location of the settings file where MFA credentials are saved. See the Credentials File Location page under Connection String Options for more information.
• MFASerialNumber: The serial number of the MFA device if one is being used.
• MFAToken: The temporary token available from your MFA device.

• AWSAccessKey: The access key of the IAM user for whom MFA will be issued.
• AWSSecretKey: The secret key of the IAM user whom MFA will be issued.

• AWSRoleARN: Specify the Role ARN for the role you'd like to authenticate with. This will cause the Sync App to attempt to retrieve credentials for the specified role using MFA.
• AWSExternalId: Only if required when you assume a role in another account.

Note that you can control the duration of the temporary credentials by setting the TemporaryTokenDuration property (default 3600 seconds).

Temporary Credentials

To authenticate using temporary credentials, specify the following:

• AuthScheme: Set this to TemporaryCredentials.
• AWSAccessKey: The access key of the IAM user to assume the role for.
• AWSSecretKey: The secret key of the IAM user to assume the role for.
• AWSSessionToken: Your AWS session token. This will have been provided alongside your temporary credentials. See AWS Identity and Access Management User Guide for more info.

The Sync App can now request resources using the same permissions provided by long-term credentials (such as IAM user credentials) for the lifespan of the temporary credentials.

If you are also using an IAM role to authenticate, you must additionally specify the following:

• AWSRoleARN: Specify the Role ARN for the role you'd like to authenticate with. This will cause the Sync App to attempt to retrieve credentials for the specified role.
• AWSExternalId: Only if required when you assume a role in another account.

Credentials Files

You can use a credentials file to authenticate. Any configurations related to AccessKey/SecretKey authentication, temporary credentials, role authentication, or MFA can be used. To do so, set the following properties to authenticate:

• AuthScheme: Set this to AwsCredentialsFile.
• AWSCredentialsFile: Set this to the location of your credentials file.
• AWSCredentialsFileProfile (optional): Optionally set this to the name of the profile you would like to use from the specified credentials file. If not specified, the profile with the name default will be used.

Before You Connect

To obtain the credentials for an AzureBlob user, follow the steps below:

1. Sign into the Azure portal with the credentials for your root account.
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.

Connecting to Azure Blob Storage

Set the AzureAccessKey connection property to the access key associated with the Azure blob to identify the user.

Authenticating to Azure Blob Storage

You can authenticate to Azure Blob Storage as an Azure AD user, with MSI authentication, or using an Azure Service Principal.

Azure AD

You can authenticate an Azure AD account using either an Azure Access Key or OAuth authentication.

Method 1: Storage Account and Access Key

Set the following to authenticate with an Azure Access Key:

• AuthScheme: Set this to AzureAD.
• AzureStorageAccount: Set this to the account associated with the Azure data lake store.
• AzureAccessKey: Set this to the access key associated with the Azure data lake store.

Method 2: OAuth

Set the following to authenticate with OAuth:

• AuthScheme: Set this to AzureAD.
• AzureStorageAccount: Set this to the account associated with the Azure data lake store.

Azure MSI

If you are connecting from an Azure VM with permissions for Azure Blob storage, set the following:

• AuthScheme: Set this to AzureMSI.
• AzureStorageAccount: Set this to the account associated with the Azure blob.

Azure Service Principal

If you would like to authenticate with a service principal instead of a client secret, it is also possible to authenticate with a client certificate. Set the following to authenticate:

• AuthScheme: Set this to AzureServicePrincipal.
• AzureTenant: Set this to the tenant you wish to connect to.
• OAuthGrantType: Set this to CLIENT.
• OAuthClientId: Set this to the Client Id in your app settings.
• OAuthJWTCert: Set this to the JWT Certificate store.
• OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

Connecting to Azure Data Lake Storage

You can authenticate to Azure Data Lake Storage as an Azure AD user, with MSI authentication, or using an Azure Service Principal.

Azure AD

You can authenticate an Azure AD account using either an Azure Access Key or OAuth authentication.

Method 1: Storage Account and Access Key

Set the following to authenticate with an Azure Access Key:

• AuthScheme: Set this to AzureAD.
• AzureStorageAccount: Set this to the account associated with the Azure Data Lake store.
• AzureAccessKey: Set this to the access key associated with the Azure Data Lake store.

Method 2: OAuth

Set the following to authenticate with OAuth:

• AuthScheme: Set this to AzureAD.
• AzureStorageAccount: Set this to the account associated with the Azure Data Lake store.

Azure MSI

If you are connecting from an Azure VM with permissions to connect to Azure Data Lake Storage, set the following:

• AuthScheme: Set this to AzureMSI.
• AzureStorageAccount: Set this to the account associated with the Azure Data Lake store.

Azure Service Principal

If you would like to authenticate with a service principal instead of a client secret, it is also possible to authenticate with a client certificate.

• AuthScheme: Set this to AzureServicePrincipal.
• AzureTenant: Set this to the tenant you wish to connect to.
• OAuthGrantType: Set this to CLIENT.
• OAuthClientId: Set this to the Client Id in your app settings.
• OAuthJWTCert: Set this to the JWT Certificate store.
• OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

Connecting to Box

Use the OAuth authentication standard to connect to Box. You can authenticate with a user account or with a service account. A service account is required to grant organization-wide access scopes to the Sync App. The Sync App facilitates these authentication flows as described below.

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Box. You can then use the Sync App to get and manage the OAuth token values. See Create a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.

2. Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the redirect URI.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the Sync App automatically refresh the OAuth access token, set the following on the first data connection.

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the path where you want the Sync App to save the OAuth values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Authenticate with a Service Account

Set the AuthScheme to OAuthJWT to authenticate with this method.

Service accounts have silent authentication, without user authentication in the browser. You can also use a service account to delegate enterprise-wide access scopes to the Sync App.

You need to create an OAuth application in this flow. See Create a Custom OAuth App to create and authorize an app. You can then connect to Box data that the service account has permission to access.

After setting the following connection properties, you are ready to connect:

• OAuthClientId: Set to the Client Id in your app settings.
• OAuthClientSecret: Set to the Client Secret in your app settings.
• OAuthJWTCertType: Set to "PEMKEY_FILE".
• OAuthJWTCert: Set to the path to the .pem file you generated.
• OAuthJWTCertPassword: Set to the password of the .pem file.
• OAuthJWTCertSubject: Set to "*" to pick the first certificate in the certificate store.
• OAuthJWTSubjectType: Set to "enterprise" or "user" depending on the Application Access Value you selected in your app settings. The default value of this connection property is "enterprise".
• OAuthJWTSubject: Set to your enterprise Id if your subject type is set to "enterprise" or your app user Id if your subject type is set to "user".
• OAuthJWTPublicKeyId: Set to the Id of your public key in your app settings.

When To Create a Custom OAuth Application

You may choose to use your own OAuth Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Create an OAuth App for User Accounts

1. Log in to your Box developers dashboard and click Create New App. Select your app type (e.g., Custom App).
2. Select the Standard OAuth 2.0 (User Authentication) authentication method and click View Your App. After creating your app, you can click Configuration from the main menu to access your app settings.
3. Note the displayed Client ID and Client Secret. You will later need to use these values to populate the OAuthClientId and OAuthClientSecret connection properties.
4. Set the Redirect URI and store the specified value for later Sync App configuration.
   • When setting up a , set the Redirect URI to http://localhost:33333 or a different port number.
   • When setting up a web app, set the Redirect URI to https://<yourwebappserver:><port>.
5. Select the scope of user permissions your app will request.

Create an OAuth App for Service Accounts

Follow the steps below to create an OAuth application and generate a private key. You will then authorize the service account.

1. Log in to your Box developers dashboard and click Create New App. Select your app type (e.g., Custom App).
2. Select the OAuth 2.0 with JWT (Server Authentication) authentication method, provide an application name, and click Create App. After creating your app, you can click Configuration from the main menu to access your app settings.
3. Note the displayed Client ID and Client Secret. You will later need to use these values to populate the OAuthClientId and OAuthClientSecret connection properties.
4. Select the application access level and the scope of user permissions your app will request. The enterprise access level enables you to work with existing users in your enterprise. The application-level setting restricts access to App-type users, users that have only API access.
5. Generate your RSA keypair. First you need to generate your private key. You can do this by running the following OpenSSL command : openssl genrsa -aes128 -out private_key.pem 2048 (you can install and use the Cygwin package to run the OpenSSL RSA keypair if you are using Windows).
6. Generate your public key by running the following OpenSSL command: openssl rsa -pubout -in private_key.pem -out public_key.pem. Copy the contents of the generated public_key.pem file and then add it by using the Add and Manage Public Keys option in your app settings.

7. Authorize the application in the enterprise admin console: Navigate to Apps > Custom Apps Manager > Add App. In the Add App modal window, provide the Client Id and click Next to identify and verify the app.

   Note: If you change the JWT access scopes, you will need to reauthorize the application in the enterprise admin console: Click Apps in the main menu and then select the ellipsis button next to your JWT application name. Select Reauthorize App in the menu.

Connecting to Dropbox

Dropbox uses the OAuth authentication standard.

Dropbox OAuth Scopes

You need to choose between using CData's embedded OAuth app or Create a Custom OAuth App.

TODO -- PUT NEEDED SCOPES HERE

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Dropbox. You can then use the Sync App to get and manage the OAuth token values. See Create a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the noted App key value from your OAuth app settings.
• OAuthClientSecret: Set this to the App secret from your OAuth app settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.

2. Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the redirect URI.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the Sync App automatically refresh the OAuth access token, set the following on the first data connection.

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the noted App key value from your OAuth app settings.
• OAuthClientSecret: Set this to the App secret from your OAuth app settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the path where you want the Sync App to save the OAuth values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the noted App key value from your OAuth app settings.
• OAuthClientSecret: Set this to the App secret from your OAuth app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

When To Create a Custom OAuth Application

You may choose to use your own OAuth Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Create a Custom OAuth App

1. Log in to your Dropbox developers dashboard and click Create New App. Select the Dropbox API type. Select the Full Dropbox access for your app.
2. After creating your app, you can view Configuration from the main menu that displays your app settings.
3. On the app Settings tab, note the values of App key and App secret for later Sync App configuration.
4. Set the Redirect URI and store the specified value for later Sync App configuration.
   • When setting up a , set the Redirect URI to http://localhost:33333 or a different port number.
   • When setting up a web app, set the Redirect URI to https://<yourwebappserver:><port>.
5. On the app Permissions tab, select the scope of user permissions your app will request.

No further values need to be specified in the XML app settings.

Connecting to Google Cloud Storage

Set the ProjectId property to the Id of the project you want to connect to.

Authenticating to Google Cloud Storage

The Sync App supports using user accounts and GCP instance accounts for authentication.

The following sections discuss the available authentication schemes for Google Cloud Storage:

• User Accounts (OAuth)
• Service Account (OAuthJWT)
• GCP Instance Account

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Desktop Applications

CData provides an embedded OAuth application that simplifies OAuth desktop Authentication. Alternatively, you can create a custom OAuth application. See Create a Custom OAuth App for information about creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

• OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.

• Extracts the access token from the callback URL.
• Obtains a new access token when the old one expires.
• Saves OAuth values in OAuthSettingsLocation that persist across connections.

Web Applications

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Callback URL you specified in your application settings. The stored procedure returns the URL to the OAuth endpoint.
2. Navigate to the URL that the stored procedure returned in Step 1. Log in to the custom OAuth application and authorize the web application. Once authenticated, the browser redirects you to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set AuthMode to WEB and the Verifier input to the "code" parameter in the query string of the callback URL.

Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the path where the Sync App saves the OAuth token values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the Sync App on a machine with an internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
2. Then configure the Sync App to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application click Google Cloud Storage OAuth endpoint to open the endpoint in your browser.
   • If you are using a custom OAuth application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the Sync App. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the verifier code.
• OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the Sync App to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the path specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the Sync App to enable the automatic refreshing of the access token.

GCP Instance Accounts

When running on a GCP virtual machine, the Sync App can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.

Introduction

When To Create a Custom OAuth Application

You may choose to use your own OAuth Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Enable the

1. Navigate to the Google Cloud Console.
2. Select Library from the left-hand navigation menu. This opens the Library page.
3. In the search field, enter "" and select from the search results.
4. On the page, click ENABLE.

Create an OAuth Application for User Accounts (OAuth)

Follow these steps to create a custom OAuth application:

1. Navigate to the Google Cloud Console.
2. If you have not done so, follow the steps in the console to create an OAuth consent screen.
3. Select Credentials from the left-hand navigation menu.
4. On the Credentials page, select Create Credentials > OAuth Client ID.
5. In the Application Type menu, select Web application.
6. Specify a name for your OAuth custom web application.
7. Under Authorized redirect URIs, click ADD URI and enter a redirect URI. Press Enter.
8. Click CREATE, which returns you to the Credentials page.
9. A window opens that displays your client Id and client secret. Although the client secret is accessible from from the Google Cloud Console, we recommend you write down the client secret. You need both the client secret and client Id to specify the OAuthClientId and OAuthClientSecret connection properties.

Create an OAuth Application for Service Accounts (OAuthJWT)

1. Navigate to the Google Cloud Console.
2. If you have not done so, follow the steps in the console to create an OAuth consent screen.
3. Select Credentials from the left-hand navigation menu.
4. On the Credentials page, select Create Credentials > Service account.
5. On the Create service account page, enter the Service account name, the Service account ID, and, optionally, a description.
6. Click DONE. This returns you to the Credentials page.

Authenticating to Google Drive

The Sync App supports using user accounts and GCP instance accounts for authentication.

The following sections discuss the available authentication schemes for Google Drive:

• User Accounts (OAuth)
• Service Account (OAuthJWT)
• GCP Instance Account

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Web Applications

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Callback URL you specified in your application settings. The stored procedure returns the URL to the OAuth endpoint.
2. Navigate to the URL that the stored procedure returned in Step 1. Log in to the custom OAuth application and authorize the web application. Once authenticated, the browser redirects you to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set AuthMode to WEB and the Verifier input to the "code" parameter in the query string of the callback URL.

Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the path where the Sync App saves the OAuth token values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

GCP Instance Accounts

When running on a GCP virtual machine, the Sync App can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.

The Sync App facilitates the following OAuth authentication flows:

• The user consent flow enables individual users to connect to their own data.
• The service account flow enables access to domain-wide data.

Using a User Account to Connect to Google

This OAuth flow requires the authenticating user to interact with Google using the browser. The Sync App facilitates this in various ways as described below.

Authenticate to Google

When you connect the Sync App opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The Sync App then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Refreshes the access token when it expires.
3. Saves OAuth values to be persisted across connections.

Authenticate from a Web Application

When connecting via a Web application or if the Sync App is not authorized to open a browser window, you exchange a verifier code for the access token.

To begin, you need to register an OAuth app with Google and set the following connection properties.

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Once you have registered an app and set OAuthClientId and OAuthClientSecret you can exchange a verifier code for the access token.

1. Call GetOAuthAuthorizationURL. The stored procedure returns the URL of the OAuth endpoint.

2. Log in at the OAuth endpoint and authorize the application. You are redirected back via the callback URL.

   The verifier code is appended to the callback URL in a query string parameter named "code". Extract the verifier code.

3. Call GetOAuthAccessToken.

Using a Service Account to Connect to Domain-Wide Data

You can use a service account in this OAuth flow to access Google APIs on behalf of users in a domain. A domain administrator can delegate domain-wide access to the service account.

To complete the service account flow, generate a private key in the Google APIs Console. In the service account flow, the Sync App exchanges a JSON Web token (JWT) for the OAuthAccessToken. The private key is required to sign the JWT. The OAuthAccessToken authenticates that the Sync App has the same permissions granted to the service account.

Generate a Private Key

Follow the steps below to generate a private key and obtain the credentials for your application:

1. Log into the Google API Console.
2. Click Create Project or select an existing project.
3. In the API Manager, click Credentials -> Create Credentials -> Service Account Key. In the Service Account menu, select New Service Account or select an existing service account. In the Key Type section, select the P12 key type.
4. Click Create to download the key pair. The private key's password is displayed: Set this in OAuthJWTCertPassword.
5. In the Service Account Keys section on the Credentials page, click Manage Service Accounts and set OAuthJWTIssuer to the email address displayed in service account Id.
6. Click Library -> Google Drive API -> Enable API.

Service Accounts

After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
• OAuthJWTCertType: Set this to "PFXFILE".
• OAuthJWTCertPassword: Set this to the password of the .p12 file.
• OAuthJWTCertSubject: Set this to "*" to pick the first certificate in the certificate store.
• OAuthJWTIssuer: Set this to the email address of the service account.
• OAuthJWTCert: Set this to the path to the .p12 file.
• OAuthJWTSubject: Set this to the email address of the user for whom the application is requesting delegate access.

1. Creates and signs the JWT with the claim set required by the Sync App.
2. Exchanges the JWT for the access token.
3. Submits the JWT for a new access token when the token expires.

Authenticating to HTTP(S)

Basic

AuthScheme: Set this to Basic.

Digest

AuthScheme: Set this to Digest.

OAuth

AuthScheme: Set this to OAuth.

OAuthJWT

AuthScheme: Set this to OAuthJWT.

OAuthPassword

AuthScheme: Set this to OAuthPassword.

OAuthClient

AuthScheme: Set this to OAuthClient.

OAuthPKCE

AuthScheme: Set this to OAuthPKCE.

Before You Connect

Register a New Instance of Cloud Object Storage

If you do not already have Cloud Object Storage in your IBM Cloud account, you can follow the procedure below to install an instance of SQL Query in your account:

1. Log in to your IBM Cloud account.
2. Navigate to the Cloud Object Storage page, choose a name for your instance and click Create. You will be redirected to the instance of Cloud Object Storage you just created.

API Key

To connect with IBM Cloud Object Storage, you will need an ApiKey. You can obtain this as follows:

1. Log in to your IBM Cloud account.
2. Navigate to the Platform API Keys page.
3. On the middle-right corner click Create an IBM Cloud API Key to create a new API Key.
4. In the pop-up window, specify the API Key name and click Create. Note the ApiKey as you can never access it again from the dashboard.

Connecting to IBM Cloud Object Storage

You can authenticate to IBM Cloud Object Storage using either HMAC or OAuth authentication.

HMAC

Set the following properties to authenticate:

• AccessKey: Set this to an IBM Access Key (a username).
• SecretKey: Set this to an IBM Secret Key.

ConnectionType=IBM Object Storage Source;URI=ibmobjectstorage://bucket1/folder1; AccessKey=token1; SecretKey=secret1; Region=eu-gb;

OAuth

Set the following to authenticate using OAuth authentication.

• AuthScheme: Set this to OAuth.
• ApiKey: Set this to the IBM API Key noted during setup.
• Region: Set this property to your IBM instance region.

ConnectionType=IBM Object Storage Source;URI=ibmobjectstorage://bucket1/folder1; ApiKey=key1; Region=eu-gb; AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;

When you connect, the Sync App completes the OAuth process.

Authenticating to XML

XML uses the OAuth authentication standard. To authenticate using OAuth, you will need to create an app to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties. AZUREOAUTHentazuread AZUREOAUTHentazureserviceprincipalinfo AZUREOAUTHentmsiauth

Connecting to SFTP

None

SSHAuthMode: Set this to None.

Password

SSHAuthMode: Set this to Password.

Public_Key

SSHAuthMode: Set this to Public_Key.

TODO

Connecting to SharePoint Online SOAP

Set the URI to a document library containing XML files. To authenticate, set User and Password and StorageBaseURL.

For example:

Note that this connection method may not work if the StorageBaseURL ends with "-my.sharepoint.com". You should use the onedrive:// scheme when connecting to these sites because they do not support the components that of SharePoint that the Sync App needs to download files.

TODO

Connecting to SharePoint Online REST

Set the URI to a document library containing XML files. StorageBaseURL is optional. If not provided, the driver will work with the root drive. To authenticate, use the OAuth authentication standard.

For example:

Note that this connection method may not work if the StorageBaseURL ends with "-my.sharepoint.com". You should use the onedrive:// scheme when connecting to these sites because they do not support the components that of SharePoint that the Sync App needs to download files.

Authenticating with SSO

AzureAD

Azure AD Configuration

The main theme behind this configuration is the OAuth 2.0 On-Behalf-Of flow. It requires two Azure AD applications:

1. An application used for the single sign-on process to a specific service provider.
   • Amazon S3: Please follow this link for detailed instructions on how to create this application. Make sure you test the connection and you are able to login to the AWS console from Azure AD.

     Save the step "Assign the Azure AD test user" until after provisioning so that you can select the AWS roles when assigning the user.

2. A "connector" application with user_impersonation permission on the SSO application you created in the previous step. Go to Azure Active Directory > App registrations and register a new application. After you register this application, you need to allow it to make API calls to the SSO application. Go to the API permissions section of the app you registered and click the "Add a permission" box. Select the API of your SSO application by specifying the API name or Application Id and add the user_impersonation permission.

CData Driver Common Properties

The following SSOProperties are needed to authenticate to Azure Active Directory and must be specified for every service provider.

• Resource: The application Id URI of the SSO application, listed in the Overview section of the app registration.
• Tenant: The Id of the Azure AD tenant where the applications are registered. You can find this value using the instructions found here.

We will retrieve the SSO SAML response from an OAuth 2.0 On-Behalf-Of flow so the following OAuth connection properties must be specified:

• OAuthClientId: The application Id of the connector application, listed in the Overview section of the app registration.
• OAuthClientSecret: The client secret value of the connector application. Azure AD displays this when you create a new client secret (Certificates & secrets section).

Amazon S3

In addition to the common properties, the following properties must be specified when connecting to Amazon S3 service provider:

• AuthScheme: Set the AuthScheme to AzureAD.
• AWSRoleARN: The ARN of the IAM role. Find this on the Summary page of the IAM role.
• AWSPrincipalARN: The ARN of the identity provider. Find this on the identity provider's summary page.

AuthScheme=AzureAD;InitiateOAuth=GETANDREFRESH;OAuthClientId=d593a1d-ad89-4457-872d-8d7443aaa655;OauthClientSecret=g9-oy5D_rl9YEKfN-45~3Wm8FgVa2F;SSOProperties='Tenant=94be7-edb4-4fda-ab12-95bfc22b232f;Resource=https://signin.aws.amazon.com/saml;';AWSRoleARN=arn:aws:iam::2153385180:role/AWS_AzureAD;AWSPrincipalARN=arn:aws:iam::215515180:saml-provider/AzureAD;

OneLogin

OneLogin Configuration

You must create an application used for the single sign-on process to a specific provider.

• Sharepoint SOAP: Please follow this link for detailed instructions on how to create this application. Make sure you test the connection and you are able to login to Office 365 from OneLogin. Make sure you have enabled WS-TRUST in your application. Otherwise, the CData driver will not be able to connect.

Sharepoint SOAP

The following properties must be specified when connecting to Sharepoint SOAP service provider:

• AuthScheme: Set the AuthScheme to OneLogin.
• User: The username of the OneLogin account.
• Password: The password of the OneLogin account.
• SSOProperties:
  • Domain (optional): It may be required to be set this property if the domain configured on the SSO domain is different than the domain of the User.

AuthScheme='OneLogin';User=test;Password=test;SSOProperties='Domain=test.cdata;';

Okta

Okta Configuration

You must create an application used for the single sign-on process to a specific provider.

• Sharepoint SOAP: Please follow this link for detailed instructions on how to create this application and configure SSO. Make sure you test the connection and you are able to login to Office 365 from Okta. Make sure you have configured SSO using WS-Federation in your application. Otherwise, the CData driver will not be able to connect.
• Amazon S3: Please follow this link for detailed instructions on how to create this application and configure SSO. Make sure you test the connection and you are able to login to AWS from Okta. Make sure you have configured SSO with SAML 2.0 in your application. Otherwise, the CData driver will not be able to connect. Ensure that the assigned AWS role in the Okta app has access to the S3 bucket you want to connect.

Sharepoint SOAP

The following properties must be specified when connecting to Sharepoint SOAP service provider:

• AuthScheme: Set the AuthScheme to Okta.
• User: The username of the Okta account.
• Password: The password of the Okta account.
• SSOProperties:
  • Domain (optional): It may be required to be set this property if the domain configured on the SSO domain is different than the domain of the User.

AuthScheme='Okta';User=test;Password=test;SSOProperties='Domain=test.cdata;';

Amazon S3

The following properties must be specified when connecting to an Amazon S3 service provider:

• AuthScheme: Set the AuthScheme to Okta.
• User: The username of the Okta account.
• Password: The password of the Okta account.
• SSOLoginURL: Set this to the embedded URL of your AWS Okta SSO app.
• AWSRoleARN (optional): The ARN of the IAM role. Find this on the Summary page of the IAM role.
• AWSPrincipalARN (optional): The ARN of the identity provider. Find this on the identity provider's summary page.
• SSOProperties:
  • APIToken (optional): Set this to the API Token that the customer created from the Okta org. It should be used when authenticating a user via a trusted application or proxy that overrides Okta client request context.

AuthScheme=Okta;User=OktaUser;Password=OktaPassword;SSOLoginURL='https://{subdomain}.okta.com/home/amazon_aws/0oan2hZLgQiy5d6/272';

ADFS

ADFS Configuration

You must create an application used for the single sign-on process to a specific provider.

• Sharepoint SOAP: Please follow this link for detailed instructions on how to set up ADFS for Office 365 for Single Sign-On. Make sure you test the connection and you are able to login to Office 365 from ADFS.
• Amazon S3: Please follow this link for detailed instructions on how to set up ADFS for AWS Single Sign-On. Make sure you test the connection and you are able to login to AWS from ADFS.

Sharepoint SOAP

The following properties must be specified when connecting to a Sharepoint SOAP service provider:

• AuthScheme: Set the AuthScheme to ADFS.
• User: The username of the ADFS account.
• Password: The password of the ADFS account.
• SSOProperties:
  • Domain (optional): It may be required to be set this property if the domain configured on the SSO domain is different than the domain of the User.

AuthScheme='ADFS';User=test;Password=test;SSOProperties='Domain=test.cdata;';

Amazon S3

The following properties must be specified when connecting to a Sharepoint SOAP service provider:

• AuthScheme: Set the AuthScheme to ADFS.
• SSOLoginURL: Set this to the URL of your ADFS instance.
• User: The username of the ADFS account.
• Password: The password of the ADFS account.
• AWSRoleARN (optional): The ARN of the IAM role. Find this on the Summary page of the IAM role.
• AWSPrincipalARN (optional): The ARN of the identity provider. Find this on the identity provider's summary page.

AuthScheme=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';

The ADFS Integrated flow indicates you are connecting with the currently logged in Windows user credentials. To use the ADFS Integrated flow, simply do not specify the User and Password, but otherwise follow the same steps in the ADFS guide above.

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.

Parsing and Merging Multiple Files Into A Single Table

The Sync App supports reading and parsing multiple files within a single directory and merging the data into a single result set.

To enable this feature, the URI property can be set to a directory with a file mask (e.g. C:\MyDataFiles\*.xml) or a directory (e.g. C:\MyDataFiles). When a directory is specified as the URI, IncludeFiles will be used to identify the file types to include.

This functionality is also available on cloud based service providers such as Google Drive (e.g. gdrive://remotepath/*.xml), Amazon S3 (e.g. s3://remotepath/*.xml), FTP (ftp://server:port/remotepath/*.xml), etc.

When setting URI to a directory, be sure to include an ending forward slash (e.g. s3://remotepath/) to denote that its a directory.

A comma-separated list of URIs is supported to include multiple files.

To retrieve all files (including those without a file extension), you can use a file mask of '*' (e.g. s3://remotepath/*) or set IncludeFiles to include a '*' entry.
To include just files without an extension, you can set IncludeFiles to include a 'NOEXT' entry.

When parsing a batch of files, the files are put into a queue.
Each file will be retrieved and parsed in a streaming fashion with each row pushed as it is parsed.
Therefore files will never be stored in memory or stored in a temporary location on disk, thus limiting the amount of memory usage required.

Metadata Discovery

When reading multiple files, the Sync App will use the first file identified to discovery metadata.
A single file is used for metadata discovery for performance reasons.
In cases where the first identified file contains partial XML data (as compared to the other files in the directory), columns/data for the other files may not be returned.
This is due to the Sync App not being able to properly identify all the columns available in the selected files from the single file used for metadata discovery.

To work around these cases, a "MetadataDiscoveryURI" option can be set via the Other property (e.g. MetadataDiscoveryURI=file:///C:\MyDataFiles\main.xml).
When a MetadataDiscoveryURI is specified, the Sync App will use the specified URI to discover tables and columns.
Once the metadata has been discovered, the URI value will be used to retrieve and parse the XML data.

Error Handling

When parsing multiple files, there may be cases where a file is not able to be parsed (such as invalid XML, a network connectivity issue, etc.).
In such cases, the Sync App will not return an exception message but rather will log the error in a temporary table called ErrorInfo#TEMP.
This is done so that failures don't occur in the middle of reading a large batch of files and having to start over.
Instead the temporary table (ErrorInfo#TEMP) can be queried after the initial query has finished.
This will allow you to identify if there were any files that failed to parse, thus allowing you to retry the failed requests and merging them with your primary result set.

To retrieve the error list, you can issue the following query: SELECT * FROM ErrorInfo#TEMP.
This table contains two columns: URI and Description.
URI contains the URI for the single file that failed (which can be set via the URI property to retry).
Description contains the description as to why the file failed to parse. In the case that no errors occurred, an empty result set will be returned.

Navigating Subdirectories

The Sync App supports navigating subdirectories when parsing local files.
This functionality is exposed by using the '*' wildcard character in the directory name of the URI value.

For example, suppose you have a 'Data' directory that contains folders with unique names (such as a date value) each of which contain similar XML data files.
You can read all these files into a single table by setting URI to 'file:///C:\Data\*\*.xml' or you can set it to the directory without a file mask 'file:///C:\Data\*'.

Partial directory matching is also supported.
For example, to retrieve all XML files within folders starting with '2018' the following URI value can be used: file:///C:\Data\2018*\*.xml.

Note: Using wildcards in directory names is not applicable when connecting to cloud resources.

This section shows how to use the Sync App to authenticate using Kerberos.

Kerberos

To authenticate to XML using Kerberos, set the following properties:

• AuthScheme: Set this to NEGOTIATE.
• KerberosKDC: Set this to the host name or IP Address of your Kerberos KDC machine.
• KerberosSPN: Set this to the service and host of the XML Kerberos Principal. This is the value prior to the '@' symbol (for instance, ServiceName/MyHost) of the principal value (for instance, ServiceName/[email protected]).

Retrieve the Kerberos Ticket

You can use one of the following options to retrieve the required Kerberos ticket.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. Note that you do not need to set the User or Password connection properties with this option.

1. Ensure that you have an environment variable created called KRB5CCNAME.
2. Set the KRB5CCNAME environment variable to a path pointing to your credential cache file (for instance, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0). This file is created when generating your ticket with MIT Kerberos Ticket Manager.
3. To obtain a ticket, open the MIT Kerberos Ticket Manager application, click Get Ticket, enter your principal name and password, then click OK. If successful, ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.
4. Now that you have created the credential cache file, the Sync App uses the cache file to obtain the Kerberos ticket to connect to XML.

As an alternative to setting the KRB5CCNAME environment variable, you can directly set the file path using the KerberosTicketCache property. When set, the Sync App uses the specified cache file to obtain the Kerberos ticket to connect to XML.

Keytab File

If the KRB5CCNAME environment variable has not been set, you can retrieve a Kerberos ticket using a Keytab File. To do so, 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 both the KRB5CCNAME environment variable and the KerberosKeytabFile property have not been set, you can retrieve a ticket using a user and password combination. To do this, set the User and Password properties to the user/password combination that you use to authenticate with XML.

Cross-Realm

More complex Kerberos environments may require cross-realm authentication where multiple realms and KDC servers are used (e.g., where one realm/KDC is used for user authentication and another realm/KDC is used for obtaining the service ticket).

In such an environment, 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.

This section shows how to use the Sync App to authenticate to any data source that supports OAuth.

OAuth requires the authenticating user to interact with XML using the browser. The Sync App facilitates this in various ways as described in the following sections.

Before following the procedures below, you need to register an OAuth app with the service to obtain the OAuthClientId and OAuthClientSecret.

Web Applications

When connecting via a Web application, or if the Sync App is not authorized to open a browser window, use the provided stored procedures to get and manage the OAuth token values.

Note: You can extend the stored procedure schemas to set defaults for the OAuth URLs or other connection string properties. See Operations for a guide.

Set Up the OAuth Flow

Provide the OAuth URLs to authenticate in the Web flow.

• OAuthRequestTokenURL: Required for OAuth 1.0. In OAuth 1.0 this is the URL where the app makes a request for the request token.
• OAuthAuthorizationURL: Required for OAuth 1.0 and 2.0. This is 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: Required for OAuth 1.0 and 2.0. This is 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.
• OAuthRefreshTokenURL: Required for OAuth 2.0. In OAuth 2.0 this is the URL where the refresh token is exchanged for a new access token when the old one expires. Note that for your data source this may be the same as the access token URL.

Get an Access Token

In addition to the OAuth URLs, set the following additional connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your app settings. This is also called the consumer key.
• OAuthClientSecret: Set this to the client secret in your app settings. This is also called the consumer secret.
• OAuthVersion: Set this to the OAuth version, either 1.0 or 2.0.

You can then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the AuthMode input to WEB and set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.
2. Log in and authorize the application. You are redirected back to the callback URL.

3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB.

   In OAuth 1.0, set the Verifier input to the "oauth_verifier" parameter. Extract the verifier code from the callback URL. Additionally, set the AuthToken and AuthSecret to the values returned by GetOAuthAccessToken.

   In OAuth 2.0, set the Verifier input to the "code" parameter in the query string of the callback URL.

Connect to Data and Refresh the Token

The OAuthAccessToken returned by GetOAuthAccessToken has a limited lifetime. To automatically refresh the token, set the following on the first data connection. Alternatively, use the RefreshOAuthAccessToken stored procedure to manually refresh the token.

OAuth Endpoints

• OAuthRequestTokenURL
• OAuthAuthorizationURL
• OAuthAccessTokenURL
• OAuthRefreshTokenURL

OAuth Tokens and Keys

• OAuthClientId
• OAuthClientSecret
• OAuthRefreshToken
• OAuthAccessToken

Initiate OAuth

• OAuthVersion: Set this to 1.0 or 2.0.
• InitiateOAuth: Set this to REFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthSettingsLocation: Set this to the path where the Sync App will save the OAuth values, to be persisted across connections.

On subsequent data connections, set the following:

• InitiateOAuth
• OAuthSettingsLocation
• OAuthRequestTokenURL
• OAuthAuthorizationURL
• OAuthAccessTokenURL
• OAuthRefreshTokenURL

JWT

The Sync App supports using JWT as an authorization grant in situations where a user cannot perform an interactive sign-on. After setting the following connection properties, you are ready to connect:

• OAuthVersion: Set this to 2.0
• OAuthAccessTokenURL: Set this to the URL where the JWT is exchanged for an access token.
• OAuthJWTCert: Set this to the certificate you want to use. In most cases this will be a path to a PEM or PFX file.
• OAuthJWTCertType: Set this to the correct certificate type. In most cases this will either PEMKEY_FILE or PFXFILE.
• OAuthJWTCertPassword: If the certificate is encrypted, set this to the encryption password.
• OAuthJWTIssuer: Set this to the issuer. This corresponds to the iss field in the JWT.

Note that the JWT signature algorithm cannot be set directly. Only the RS256 algorithm is supported.

The Sync App will then construct a JWT including following fields, and submit it to OAuthAccessTokenURL for an access token.

• scope This will come from Scope if it is provided.
• aud This will come from OAuthJWTAudience if it is provided.
• iss This will come from OAuthJWTIssuer.
• iat This is the time when the JWT is generated.
• exp This is the value of iat plus the value of OAuthJWTValidityTime.
• sub This will come from OAuthJWTSubject if it is provided.

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 XML services. The CData Sync App provides a managed way for you to use the two prevailing techniques for dealing with nested XML 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 XML 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:

• 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 XML objects in XML 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 XML

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: 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 an XML 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 as tables.

• 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 an XML document. Nested object arrays are returned as XML aggregates.

For users who simply need access to the entirety of their XML data, flattening the data into a single table is the best option. The Sync App will use streaming and only parses the XML 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;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 as an XML path, 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: 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 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 default object flattening. Nested elements are returned as aggregated XML.

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;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: 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 in the XML file or source, 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 XML 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 XML 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 XML paths "/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;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: 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 XML 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 XML 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;

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 XML. 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 local or remote XML.

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;

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.

<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" />
    <attr name="EmployeeID"   xs:type="int"            readonly="true"    other:xPath="content/properties/EmployeeID" />
    <attr name="Name"         xs:type="string"         readonly="false"   other:xPath="content/properties/Name" />
    <attr name="TotalExpense" xs:type="double"         readonly="true"    other:xPath="content/properties/TotalExpense" />
    <attr name="HireDate"     xs:type="datetime"       readonly="true"    other:xPath="content/properties/HireDate" />
    <attr name="Salary"       xs:type="int"            readonly="true"    other:xPath="content/properties/Salary" />
  </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" /> 

Indexed XPath values can also be used to specify an element within the XML 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]"/>
  <attr name=PhoneNumber2 xs:type="string" other:xPath="Person/PhoneNumber[1]"/>
</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 XML 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"/>
      </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:>,>=,=" />
    

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, which invokes the Sync App's built-in operations to process XML. In the GET method you have control over the request for data. The following procedures show several ways to use this: search the remote data, server-side, with SELECT WHERE, LIMIT the results returned by the server, or implement paging.

Query Processing

By default, the Sync App will process the query client-side, in memory, so the XPath and URI connection properties are all you need to set to execute any SELECT statement.

The Sync App can also offload supported queries to the server while processing the rest of the query client side. For example, a server-side search filters the data so that a smaller dataset can be processed client-side. See the documentation for the SupportEnhancedSQL property for more information on the Query Processing feature.

Execute Selects to XML

The following steps result in a script that allows you to execute SQL-92 queries. The queries are processed client side. Before invoking a data processing operation, you will need to 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 will scan 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

The following sections show how to translate a SELECT WHERE statement into a search request to XML APIs. The procedure uses the following statement:

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

If this filter is supported on the server via query parameters, you can use the other:filter property of the api:info column definition to specify the desired mapping. For the above query, we will use this property to map the modifiedAt < '<date>' filter to the query parameter that returns results that were modifed before a given date, and the modifedAt > '<date>' filter to the query parameter that filters results that were modifed after. other:filter is specified as follows:

• 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.

To perform this mapping, we would 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 will need to 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>

Searching with Pseudo Columns

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

For example, in the Weather Underground API, you can return 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 

Below is an example forecast query expressed in SQL:

SELECT * FROM Hourly WHERE Location="27516"

Follow the steps below to add a Location pseudo column and implement the preceding query:

1. Add a Location input parameter to the column definitions in the api:info block. (The Location will be required; the Sync App should return an error if a Location is not specified.)
   

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

2. Reference the _input item's Location attribute to build the URI:
   

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

3. Invoke the operation to make the request and process the response:
   

   <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. The first is when the URL of the next page is returned in the response. The second type involves specifying your current page offset in a query parameter. The third type is where you send the current page number in a query parameter. The fourth requires sending a page token in the query parameter of the next request. If your API utilizes one of these paging patterns, follow the below examples to implement paging:

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 will be 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. If the returned 'Link' header value contains a partial URL path, you can set the 'BaseURI' attribute to the base URL that should be prepended to the url path returned in the 'Link' header.
  

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

Paging Token

Sometimes a token is returned in the response body that should be passed to a paging parameter in the subsequent request. In this case, you can set 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" /> 

  If has_more is true, this will pass the token at /data/token to the next query: ?nextpagetoken=<token>
• Sometimes the token needs to be passed in the request body. You can do that by setting 'pagetokenparam' to its XPath.
  

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

Record Offset

If the service provides a record offset query parameter to control paging, you can implement this by setting the name of the offset query parameter, the name of the page size query parameter, and the page size to be passed. Note that the page size parameter does not need to be set if there is no parameter to control this. In this 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" /> 

Page Number

Similar to record offset, if the service provides a query parameter that sets the page number, you can implement this by setting 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 the page size parameter does not need to be set if there is no parameter to control this. In this 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" /> 

Other Paging Types

If your API does not follow any of those paging patterns, you will need a custom paging implementation. This is done by setting 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 will automatically call the method again with the 'Rows@Next' value in the input after it is finished returning results for this page. 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, your API may return the next page's URL in the response. You can obtain this 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. In the GET method, 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'

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.

Reference the value of the _query item's limit attribute when you build the API request. In the OData API, a limit can be specified with 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

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

• 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 XML

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 XML

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 XML

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>

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 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:
  

  <rsb: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.

<rsb: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:
  

  <rsb: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 oauthGetAccessToken operation is an RSBScript 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.

<rsb:script xmlns:rsb="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema"">

  <rsb: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."                                                                                         />
 </rsb:info>

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

Writing the RefreshOAuthAccessToken Stored Procedure

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

<rsb:script xmlns:rsb="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema"">

  <rsb: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."                      />

  </rsb:info>

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

<rsb:script xmlns:rsb="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema"">

  <rsb: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."                                                                                                      />
  </rsb:info>

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

OAuth

JWT OAuth

Kerberos

SSL

SSH

Firewall