The CData Sync App provides a straightforward way to continuously pipeline your XML data to any database, data lake, or data warehouse, making it easily available for Analytics, Reporting, AI, and Machine Learning.
The XML connector can be used from the CData Sync application to pull data from XML and move it to any of the supported destinations.
The Sync App models XML-based REST APIs as bidirectional database tables and XML files as views (local XML files, files stored on popular cloud services, and FTP servers). The Sync App abstracts connecting to remote data as well as data processing: TLS/SSL, HTTP/FTP, and authentication. The major authentication schemes are supported, including HTTP Basic, Digest, NTLM, OAuth, and FTP.
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:
See the relevant CData file system driver's documentation for a configuration guide for connecting to stored XML file metadata.
For required properties, see the Settings tab.
For connection properties that are not typically required, see the Advanced tab.
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.
Set the ConnectionType to Local. Local files support SELECT\INSERT\UPDATE\DELETE.
Set the URI to a folder containing XML files: C:\folder1.
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).
Set the following to identify your XML resources stored on Amazon S3:
See Connecting to Amazon S3 for more information regarding how to connect and authenticate to XML files hosted on Amazon S3.
Set the following to identify your XML resources stored on Azure Blob Storage:
See Connecting to Azure Blob Storage for more information regarding how to connect and authenticate to XML files hosted on Amazon Blob Storage.
Set the following to identify your XML resources stored on Azure Data Lake Storage:
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.
Set the following properties to connect:
You can authenticate either an Azure access key or an Azure shared access signature. Set one of the following:
Set the following to identify your XML resources stored on Box:
See Connecting to Box for more information regarding how to connect and authenticate to XML files hosted on Box.
Set the following to identify your XML resources stored on Dropbox:
See Connecting to Dropbox for more information regarding how to connect and authenticate to XML files hosted on Dropbox.
The Sync App supports both plaintext and SSL/TLS connections to FTP servers.
Set the following connection properties to connect:
Set the following to identify your XML resources stored on Google Cloud Storage:
See Connecting to Google Cloud Storage for more information regarding how to connect and authenticate to XML files hosted on Google Cloud Storage.
Set the following to identify your XML resources stored on Google Drive:
See Connecting to Google Drive for more information regarding how to connect and authenticate to XML files hosted on Google Drive.
Set the following to identify your XML resources stored on HDFS:
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.
Set the following to identify your XML resources stored on HTTP streams:
See Connecting to HTTP Streams for more information regarding how to connect and authenticate to XML files hosted on HTTP Streams.
Set the following to identify your XML resources stored on IBM Cloud Object Storage:
See Connecting to IBM Object Storage for more information regarding how to connect and authenticate to XML files hosted on IBM Cloud Object Storage.
Set the following to identify your XML resources stored on OneDrive:
See Connecting to OneDrive for more information regarding how to connect and authenticate to XML files hosted on OneDrive.
Set the following properties to authenticate with HMAC:
Set the following to identify your XML resources stored on SFTP:
See Connecting to SFTP for more information regarding how to connect and authenticate to XML files hosted on SFTP.
Set the following to identify your XML resources stored on SharePoint Online:
See Connecting to SharePoint Online for more information regarding how to connect and authenticate to XML files hosted on SharePoint Online.
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.
Below are example connection strings to XML files or streams, using the Sync App's default data modeling configuration (see below)
| Service provider | URI formats | Connection example |
| Local | Single File Path (One table)
file://localPath/file.xml Directory Path (One aggregated table from all files) file://localPath | URI=C:/folder1/file.xml; |
| HTTP or HTTPS | http://remoteStream
https://remoteStream | URI=http://www.host1.com/streamname1; |
| Amazon S3 | Single File Path (One table)
s3://remotePath/file.xml Directory Path (One aggregated table from all files) s3://remotePath | URI=s3://bucket1/folder1/file.xml; AWSSecretKey=secret1; AWSRegion=OHIO; |
| Azure Blob Storage | azureblob://mycontainer/myblob/ | URI=azureblob://mycontainer/myblob/; AzureStorageAccount=myAccount; AzureAccessKey=myKey;
URI=azureblob://mycontainer/myblob/; AzureStorageAccount=myAccount; AuthScheme=OAuth; |
| Google Drive | Single File Path (One table)
gdrive://remotePath/file.xml gdrive://SharedWithMe/remotePath/file.xml Directory Path (One aggregated table from all files) gdrive://remotePath gdrive://SharedWithMe/remotePath | URI=gdrive://folder1/file.xml; AuthScheme=OAuth;
URI=gdrive://SharedWithMe/folder1/file.xml; AuthScheme=OAuth; |
| OneDrive | Single File Path (One table)
onedrive://remotePath/file.xml onedrive://SharedWithMe/remotePath/file.xml Directory Path (One aggregated table from all files) onedrive://remotePath onedrive://SharedWithMe/remotePath | URI=onedrive://folder1/file.xml; AuthScheme=OAuth;
URI=onedrive://SharedWithMe/folder1/file.xml; AuthScheme=OAuth; |
| Box | Single File Path (One table)
box://remotePath/file.xml Directory Path (One aggregated table from all files) box://remotePath | URI=box://folder1/file.xml; AuthScheme=OAuth; |
| Dropbox | Single File Path (One table)
dropbox://remotePath/file.xml Directory Path (One aggregated table from all files) dropbox://remotePath | URI=dropbox://folder1/file.xml; AuthScheme=OAuth; OAuthClientId=oauthclientid1; OAuthClientSecret=oauthcliensecret1; CallbackUrl=http://localhost:12345; |
| SharePoint SOAP | Single File Path (One table)
sp://remotePath/file.xml Directory Path (One aggregated table from all files) sp://remotePath | URI=sp://Documents/folder1/file.xml; User=user1; Password=password1; StorageBaseURL=https://subdomain.sharepoint.com; |
| SharePoint REST | Single File Path (One table)
sprest://remotePath/file.xml Directory Path (One aggregated table from all files) sprest://remotePath | URI=sprest://Documents/folder1/file.xml; AuthScheme=OAuth; StorageBaseURL=https://subdomain.sharepoint.com; |
| FTP or FTPS | Single File Path (One table)
ftp://server:port/remotePath/file.xml ftps://server:port/remotepath/file.xml Directory Path (One aggregated table from all files) ftp://server:port/remotePath ftps://server:port/remotepath; | URI=ftps://localhost:990/folder1/file.xml; User=user1; Password=password1; |
| SFTP | Single File Path (One table)
sftp://server:port/remotePath/file.xml Directory Path (One aggregated table from all files) sftp://server:port/remotePath | URI=sftp://127.0.0.1:22/folder1/file.xml User=user1; Password=password1;
URI=sftp://127.0.0.1:22/folder1/file.xml SSHAuthmode=PublicKey; SSHClientCert=myPrivateKey |
| Azure Data Lake Store Gen1 | adl://remotePath/file.xml
adl://Account.azuredatalakestore.net@remotePath/file.xml | URI=adl://folder1/file.xml; AuthScheme=OAuth; AzureStorageAccount=myAccount; AzureTenant=tenant;
URI=adl://myAccount.azuredatalakestore.net@folder1/file.xml; AuthScheme=OAuth; AzureTenant=tenant; |
| AzureDataLakeStoreGen2 | abfs://myfilesystem/remotePath/file.xml
abfs://[email protected]/remotepath/file.xml
| URI=abfs://myfilesystem/folder1/file.xml; AzureStorageAccount=myAccount; AzureAccessKey=myKey;
URI=abfs://[email protected]/folder1/file.xml; AzureAccessKey=myKey; |
| AzureDataLakeStoreGen2 with SSL | abfss://myfilesystem/remotePath/file.xml
abfss://[email protected]/remotepath/file.xml
| URI=abfss://myfilesystem/folder1/file.xml; AzureStorageAccount=myAccount; AzureAccessKey=myKey;
URI=abfss://[email protected]/folder1/file.xml; AzureAccessKey=myKey; |
| Wasabi | Single File Path (One table)
wasabi://bucket1/remotePath/file.xml Directory Path (One aggregated table from all files) wasabi://bucket1/remotePath | URI=wasabi://bucket/folder1/file.xml; AccessKey=token1; SecretKey=secret1; Region='us-west-1'; |
| Google Cloud Storage | Single File Path (One table)
gs://bucket/remotePath/file.xml Directory Path (One aggregated table from all files) gs://bucket/remotePath | URI=gs://bucket/folder1/file.xml; AuthScheme=OAuth; ProjectId=test; |
| Oracle Cloud Storage | Single File Path (One table)
os://bucket/remotePath/file.xml Directory Path (One aggregated table from all files) os://bucket/remotePath | URI=os://bucket/folder1/file.xml; AccessKey='myKey'; SecretKey='mySecretKey'; OracleNameSpace='myNameSpace' Region='us-west-1'; |
| Azure File | Single File Path (One table)
azurefile://fileShare/remotePath/file.xml Directory Path (One aggregated table from all files) azurefile://fileShare/remotePath | URI=azurefile://bucket/folder1/file.xml; AzureStorageAccount='myAccount'; AzureAccessKey='mySecretKey';
URI=azurefile://bucket/folder1/file.xml; AzureStorageAccount='myAccount'; AzureSharedAccessSignature='mySharedAccessSignature'; |
| IBM Object Storage Source | Single File Path (One table)
ibmobjectstorage://bucket1/remotePath/file.xml Directory Path (One aggregated table from all files) ibmobjectstorage://bucket1/remotePath | URI=ibmobjectstorage://bucket/folder1/file.xml; AuthScheme='HMAC'; AccessKey=token1; SecretKey=secret1; Region='eu-gb';
URI=ibmobjectstorage://bucket/folder1/file.xml; ApiKey=key1; Region='eu-gb'; AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH; |
| Hadoop Distributed File System | Single File Path (One table)
webhdfs://host:port/remotePath/file.xml Directory Path (One aggregated table from all files) webhdfs://host:port/remotePath | URI=webhdfs://host:port/folder1/file.xml |
| Secure Hadoop Distributed File System | Single File Path (One table)
webhdfss://host:port/remotePath/file.xml Directory Path (One aggregated table from all files) webhdfss://host:port/remotePath | URI=webhdfss://host:port/folder1/file.xml |
The DataModel property controls how your data is represented into tables.
Specify the following to connect to data:
There are several authentication methods available for connecting to XML including:
To authenticate using account root credentials, set these configuration parameters:
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.
Set AuthScheme to AwsEC2Roles.
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. Since the Sync App automatically obtains your IAM Role credentials and authenticates with them, it is not necessary to specify AWSAccessKey and AWSSecretKey.
If you are also using an IAM role to authenticate, you must additionally specify the following:
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.
Set AuthScheme to AwsIAMRoles.
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. If you are specifying the AWSAccessKey and AWSSecretKey of an AWS root user, you may not use roles.
To authenticate as an AWS role, set these properties:
To connect to ADFS, set the AuthScheme to ADFS, and set these properties:
To authenticate to ADFS, set these SSOProperties:
Example connection string:
AuthScheme=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';SSOProperties='RelyingParty=https://saml.salesforce.com';
The ADFS Integrated flow indicates you are connecting with the currently logged in Windows user credentials. To use the ADFS Integrated flow, do not specify the User and Password, but otherwise follow the same steps in the ADFS guide above.
To connect to Okta, set the AuthScheme to Okta, and set these properties:
If you are using a trusted application or proxy that overrides the Okta client request OR configuring MFA, you must use combinations of SSOProperties to authenticate using Okta. Set any of the following, as applicable:
Example connection string:
AuthScheme=Okta;SSOLoginURL='https://example.okta.com/home/appType/0bg4ivz6cJRZgCz5d6/46';User=oktaUserName;Password=oktaPassword;
To connect to PingFederate, set AuthScheme to PingFederate, and set these properties:
To enable mutual SSL authentication for SSOLoginURL, the WS-Trust STS endpoint, configure these SSOProperties:
Example connection string:
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;
For users and roles that require multi-factor authentication, specify the following:
Note: If you want to control the duration of the temporary credentials, set the TemporaryTokenDuration property (default: 3600 seconds).
To authenticate using temporary credentials, specify the following:
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.
To authenticate using both temporary credentials and an IAM role, set all the parameters described above, and specify these additional parameters:
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:
This configuration requires two separate Azure AD applications:
To connect to Azure AD, set the AuthScheme to AzureAD, and set these properties:
To authenticate to Azure AD, set these SSOProperties:
Example connection string:
AuthScheme=AzureAD;OAuthClientId=3ea1c786-d527-4399-8c3b-2e3696ae4b48;OauthClientSecret=xxx;CallbackUrl=https://localhost:33333;SSOProperties='Resource=https://signin.aws.amazon.com/saml;AzureTenant=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx';
To obtain the credentials for an AzureBlob user, follow the steps below:
Set AzureStorageAccount to your Azure Blob Storage account name.
You can authenticate to Azure Blob Storage via Access Key, Shared Access Signatures (SAS), AzureAD user, Azure MSI, or Azure Service Principal.
Set the following to authenticate with an Azure Access Key:
AuthScheme must be set to AzureAD in all user account flows.
The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.
Create an AzureAD App and an Azure Service Principal
When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating a Custom OAuth Application for more details.
In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.
Assign a role to the application
To access resources in your subscription, you must assign a role to the application.
Client Secret
Set these connection properties:
Certificate
Set these connection properties:
You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.
If you are connecting from an Azure VM with permissions for Azure Data Lake Storage, set AuthScheme to AzureMSI.
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:
There are two types of custom AzureAD applications: AzureAD and AzureAD with an Azure Service Principal. Both are OAuth-based.
You may choose to use your own AzureAD Application Credentials when you want to
Follow the steps below to obtain the AzureAD values for your application, the OAuthClientId and OAuthClientSecret.
When authenticating using an Azure Service Principal, you must create both a custom AzureAD application and a service principal that can access the necessary resources. Follow the steps below to create a custom AzureAD application and obtain the connection properties for Azure Service Principal authentication.
Follow the steps below to obtain the AzureAD values for your application.
Set AzureStorageAccount to your Azure Data Lake Storage account name.
You can authenticate to Azure Data Lake Storage via Access Key, Shared Access Signature (SAS), AzureAD user, Azure MSI, or Azure Service Principal.
Set the following to authenticate with an Azure Access Key:
AuthScheme must be set to AzureAD in all user account flows.
The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.
Create an AzureAD App and an Azure Service Principal
When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating a Custom OAuth Application for more details.
In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.
Assign a role to the application
To access resources in your subscription, you must assign a role to the application.
Client Secret
Set these connection properties:
Certificate
Set these connection properties:
You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.
If you are connecting from an Azure VM with permissions for Azure Data Lake Storage, set AuthScheme to AzureMSI.
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:
There are two types of custom AzureAD applications: AzureAD and AzureAD with an Azure Service Principal. Both are OAuth-based.
You may choose to use your own AzureAD Application Credentials when you want to
Follow the steps below to obtain the AzureAD values for your application, the OAuthClientId and OAuthClientSecret.
When authenticating using an Azure Service Principal, you must create both a custom AzureAD application and a service principal that can access the necessary resources. Follow the steps below to create a custom AzureAD application and obtain the connection properties for Azure Service Principal authentication.
Follow the steps below to obtain the AzureAD values for your application.
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.
AuthScheme must be set to OAuth in all user account flows.
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:
You may choose to use your own OAuth Application Credentials when you want to:
At the Box Enterprise Developer Console:
Note: Box does not back up private keys for security reasons. Be careful to back up the Public/Private JSON file. If you lose your private key, you must reset the entire keypair.
openssl genrsa -des3 -out private.pem 2048 openssl rsa -in private.pem -outform PEM -pubout -out public.pem
Note: To run OpenSSL in a Windows environment, install the Cygwin package.
After your application is created and registered, click Configuration from the main menu to access your settings. Note the displayed Redirect URI, Client ID, and Client Secret. You will need these values later.
If you change the JWT access scopes, you must reauthorize the application in the enterprise admin console:
Dropbox uses the OAuth authentication standard.
You need to choose between using CData's embedded OAuth app or Create a Custom OAuth App.
The embedded app includes the following scopes:
You may choose to use your own OAuth Application Credentials when you want to
No further values need to be specified in the XML app settings.
Set the ProjectId property to the Id of the project you want to connect to.
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:
AuthScheme must be set to OAuth in all user account flows.
Get an OAuth Access Token
Set the following connection properties to obtain the OAuthAccessToken:
Then call stored procedures to complete the OAuth exchange:
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:
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:
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.
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.
On the headless machine, set the following connection properties to obtain the OAuth authentication values:
After the OAuth settings file is generated, you need to re-set the following properties to connect:
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 location 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:
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.
(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to XML".)
However, you must create a custom OAuth application to connect to XML via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.
Custom OAuth applications are useful if you want to:
The following sections describe how to enable the Directory API and create custom OAuth applications for user accounts (OAuth) and Service Accounts (OAuth/JWT).
For users whose AuthScheme is OAuth and who need to authenticate over a web application, you must always create a custom OAuth application. (For desktop and headless flows, creating a custom OAuth application is optional.)
Do the following:
Note: The client secret remains accessible from from the Google Cloud Console.
To create a new service account:
To complete the service account flow, generate a private key in the Google Cloud Console. In the service account flow, the driver exchanges a JSON Web token (JWT) for the OAuthAccessToken. The private key is required to sign the JWT. The driver will have the same permissions granted to the service account.
The Sync App supports using user accounts and GCP instance accounts for authentication.
The following sections discuss the available authentication schemes for Google Drive:
AuthScheme must be set to OAuth in all user account flows.
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.
(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to XML".)
However, you must create a custom OAuth application to connect to XML via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.
Custom OAuth applications are useful if you want to:
The following sections describe how to enable the Directory API and create custom OAuth applications for user accounts (OAuth) and Service Accounts (OAuth/JWT).
For users whose AuthScheme is OAuth and who need to authenticate over a web application, you must always create a custom OAuth application. (For desktop and headless flows, creating a custom OAuth application is optional.)
Do the following:
Note: The client secret remains accessible from from the Google Cloud Console.
To create a new service account:
To complete the service account flow, generate a private key in the Google Cloud Console. In the service account flow, the driver exchanges a JSON Web token (JWT) for the OAuthAccessToken. The private key is required to sign the JWT. The driver will have the same permissions granted to the service account.
The Sync App generically supports connecting to XML data stored on HTTP(S) streams.
Several authentication methods, such as user/password, digest access, OAuth, OAuthJWT, and OAuth PASSWORD flow are supported.
You can also connect to streams that have no authentication set up.
Connect to an HTTP(S) stream with no authentication by setting the AuthScheme connection property to None.
Set the following to connect:
Set the following to connect:
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 containing the XML data you want to work with.
Creating a custom application in most services requires registering as a developer and creating an app in the UI of the service.
This is not necessarily true for all services. In some you must contact the service provider to create the app for you. However it is done, you must obtain the values for OAuthClientId, OAuthClientSecret, and CallbackURL.
Set AuthScheme to OAuthJWT.
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:
Note that the JWT signature algorithm cannot be set directly. The Sync App only supports the RS256 algorithm.
The Sync App will then construct a JWT including the following fields, and submit it to OAuthAccessTokenURL for an access token.
AuthScheme: Set this to OAuthPassword.
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 containing the XML data you want to work with.
Creating a custom application in most services requires registering as a developer and creating an app in the UI of the service.
This is not necessarily true for all services. In some you must contact the service provider to create the app for you. However it is done, you must obtain the values for OAuthClientId, OAuthClientSecret, and CallbackURL.
After setting the following connection properties, you are ready to connect:
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:
To connect with IBM Cloud Object Storage, you will need an ApiKey. You can obtain this as follows:
Set Region to to your IBM instance region.
You can authenticate to IBM Cloud Object Storage using either HMAC or OAuth authentication.
Set the following properties to authenticate:
ConnectionType=IBM Object Storage Source;URI=ibmobjectstorage://bucket1/folder1; AccessKey=token1; SecretKey=secret1; Region=eu-gb;
Set the following to authenticate using OAuth authentication.
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.
You can connect to OneDrive using an AzureAD user, with MSI authentication, or using an Azure Service Principal.
AuthScheme must be set to AzureAD in all user account flows.
The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.
Create an AzureAD App and an Azure Service Principal
When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating a Custom OAuth Application for more details.
In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.
Assign a role to the application
To access resources in your subscription, you must assign a role to the application.
Client Secret
Set these connection properties:
Certificate
Set these connection properties:
You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.
If you are connecting from an Azure VM with permissions for Azure Data Lake Storage, set AuthScheme to AzureMSI.
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:
There are two types of custom AzureAD applications: AzureAD and AzureAD with an Azure Service Principal. Both are OAuth-based.
You may choose to use your own AzureAD Application Credentials when you want to
Follow the steps below to obtain the AzureAD values for your application, the OAuthClientId and OAuthClientSecret.
When authenticating using an Azure Service Principal, you must create both a custom AzureAD application and a service principal that can access the necessary resources. Follow the steps below to create a custom AzureAD application and obtain the connection properties for Azure Service Principal authentication.
Follow the steps below to obtain the AzureAD values for your application.
You can authenticate to SFTP using a user and password or an SSH certificate. Additionally, you can connect to an SFTP server that has no authentication enabled.
Set SSHAuthMode to None to connect without authentication, assuming your server supports doing so.
Provide user credentials associated with your SFTP server:
Set the following to connect.
| Service provider | Okta | OneLogin | ADFS | AzureAD |
| Amazon S3 | Y | Y | Y | |
| Azure Blob Storage | ||||
| Azure Data Lake Store Gen1 | ||||
| Azure Data Lake Store Gen2 | ||||
| Azure Data Lake Store Gen2 with SSL | ||||
| Google Drive | ||||
| OneDrive | ||||
| Box | ||||
| Dropbox | ||||
| SharePoint Online SOAP | Y | Y | Y | |
| SharePoint Online REST | ||||
| Wasabi | ||||
| Google Cloud Storage | ||||
| Oracle Cloud Storage | ||||
| Azure File |
Azure AD Configuration
The main theme behind this configuration is the OAuth 2.0 On-Behalf-Of flow. It requires two Azure AD applications:
Save the step "Assign the Azure AD test user" until after provisioning so that you can select the AWS roles when assigning the user.
CData Driver Common Properties
The following SSOProperties are needed to authenticate to Azure Active Directory and must be specified for every service provider.
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:
Amazon S3
In addition to the common properties, the following properties must be specified when connecting to Amazon S3 service provider:
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 Configuration
You must create an application used for the single sign-on process to a specific provider.
Sharepoint SOAP
The following properties must be specified when connecting to Sharepoint SOAP service provider:
AuthScheme='OneLogin';User=test;Password=test;SSOProperties='Domain=test.cdata;';
Okta Configuration
You must create an application used for the single sign-on process to a specific provider.
Sharepoint SOAP
The following properties must be specified when connecting to Sharepoint SOAP service provider:
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=Okta;User=OktaUser;Password=OktaPassword;SSOLoginURL='https://{subdomain}.okta.com/home/amazon_aws/0oan2hZLgQiy5d6/272';
ADFS Configuration
You must create an application used for the single sign-on process to a specific provider.
Sharepoint SOAP
The following properties must be specified when connecting to a Sharepoint SOAP service provider:
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=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';ADFS Integrated
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 kind of granular control that is 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.
Set the following properties to control how the Sync App models XML as tables:
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.
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.
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.
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.
To authenticate to XML with Kerberos, set AuthScheme to NEGOTIATE.
Authenticating to XML via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.
The Sync App provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.
MIT Kerberos Credential Cache File
This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.
This option requires that KRB5CCNAME has been created in your system.
To enable ticket retrieval via MIT Cerberos Credential Cache Files:
If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.
The Sync App uses the cache file to obtain the Kerberos ticket to connect to XML.
Note: If you would prefer not to edit KRB5CCNAME, you can use the KerberosTicketCache property to set the file path manually. After this is set, the Sync App uses the specified cache file to obtain the Kerberos ticket to connect to XML.
Keytab File
If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.
To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.
User and Password
If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.
To use this method, set the User and Password properties to the user/password combination that you use to authenticate with XML.
To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.
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:
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.
To flatten data, you only need to be familiar with two data structures:
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>
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.
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:
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.
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.
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.
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.
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.
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;'
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]
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.
| age | gender | first_name | last_name | source | type | model | ins_company | ins_policy_num | maint_date | maint_desc | |
| 20 | M | John | Doe | internet | car | Honda Civic | ABC Insurance | 12345 | 2017-07-17 | oil change | |
| 20 | M | John | Doe | internet | car | Honda Civic | ABC Insurance | 12345 | 2018-01-03 | new tires | |
| 20 | M | John | Doe | internet | truck | Dodge Ram | ABC Insurance | 12345 | 2017-08-27 | new tires | |
| 20 | M | John | Doe | internet | truck | Dodge Ram | ABC Insurance | 12345 | 2018-01-08 | oil change | |
| 24 | F | Jane | Roberts | phone | car | Toyota Camry | Car Insurance | 98765 | 2017-05-11 | tires rotated | |
| 24 | F | Jane | Roberts | phone | car | Toyota Camry | Car Insurance | 98765 | 2017-11-03 | oil change | |
| 24 | F | Jane | Roberts | phone | car | Honda Accord | Car Insurance | 98765 | 2017-10-07 | new air filter | |
| 24 | F | Jane | Roberts | phone | car | Honda Accord | Car Insurance | 98765 | 2018-01-13 | new brakes |
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.
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.
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".
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';
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]
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.
| age | gender | name_first | name_last | source | vehicles | |
| 20 | M | John | Doe | internet | <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> | |
| 24 | F | Jane | Roberts | phone | <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> |
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.
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.
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".
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;'
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]
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).
| age | gender | first_name | last_name | source | type | model | ins_company | ins_policy_num | maint_date | maint_desc | ||
| 20 | M | John | Doe | internet | car | Honda Civic | ABC Insurance | 12345 | 2017-07-17 | oil change | ||
| 20 | M | John | Doe | internet | car | Honda Civic | ABC Insurance | 12345 | 2018-01-03 | new tires | ||
| 20 | M | John | Doe | internet | truck | Dodge Ram | ABC Insurance | 12345 | 2017-08-27 | new tires | ||
| 20 | M | John | Doe | internet | truck | Dodge Ram | ABC Insurance | 12345 | 2018-01-08 | oil change | ||
| 24 | F | Jane | Roberts | phone | car | Toyota Camry | Car Insurance | 98765 | 2017-05-11 | tires rotated | ||
| 24 | F | Jane | Roberts | phone | car | Toyota Camry | Car Insurance | 98765 | 2017-11-03 | oil change | ||
| 24 | F | Jane | Roberts | phone | car | Honda Accord | Car Insurance | 98765 | 2017-10-07 | new air filter | ||
| 24 | F | Jane | Roberts | phone | car | Honda Accord | Car Insurance | 98765 | 2018-01-13 | new brakes |
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.
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.
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>
With the default object flattening, this document will be represented by the following columns:
| Column Name | Data Type | Example Value |
| id | Integer | 12 |
| name | String | Lohia Manufacturers Inc. |
| address.street | String | Main Street |
| address.city | String | Chapel Hill |
| address.state | String | NC |
| office | String | <office>Chapel Hill</office><office>London</office><office>New York</office> |
| annual_revenue | Double | 35,600,000 |
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>
The FlattenArrays property can be set to 2 to represent the array above as follows:
| Column Name | Data Type | Example Value |
| office.0 | String | London |
| office.1 | String | Los Angeles |
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.
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.
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>
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;'
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.
The preceding query returns the following results:
| Column Name | Data Type | Example Value |
| personal.name.first | String | Jane |
| personal.name.last | String | Roberts |
| vehicles.1.type | String | car |
| vehicles.1.model | String | Honda Accord |
Vertical flattening queries enable you to retrieve a nested element as if it were a separate table.
In the FROM clause, you can use dot notation to drill down to a nested element.
SELECT * FROM [people.vehicles]
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>
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;'
Vertical flattening will allow you to retrieve the vehicles element as a separate table:
SELECT * FROM [people.vehicles]This query returns the following data set:
| features | insurance.company | insurance.policy_num | maintenance | model | type |
<features>sunroof</features><features>rims</features> | ABC Insurance | 12345 | <maintenance><date>07-17-2017</date><desc>oil change</desc></maintenance><maintenance><date>01-03-2018</date><desc>new tires</desc></maintenance> | Honda Civic | car |
<features>lift kit</features><features>tow package</features> | ABC Insurance | 12345 | <maintenance><date>08-27-2017</date><desc>new tires</desc></maintenance><maintenance><date>01-08-2018</date><desc>oil change</desc></maintenance> | Dodge Ram | truck |
<features>upgraded stereo</features> | Car Insurance | 98765 | <maintenance><date>05-11-2017</date><desc>tires rotated</desc></maintenance><maintenance><date>11-03-2017</date><desc>oil change</desc></maintenance> | Toyota Camry | car |
<features>custom paint</features><features>custom wheels</features> | Car Insurance | 98765 | <maintenance><date>10-07-2017</date><desc>new air filter</desc></maintenance><maintenance><date>01-13-2018</date><desc>new brakes</desc></maintenance> | Honda Accord | car |
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>
SELECT Name, XML_EXTRACT(grades,'[0].grade') AS Grade, XML_EXTRACT(grades,'[0].score') AS Score FROM Students;
| Column Name | Example Value |
| Grade | A |
| Score | 2 |
SELECT Name, XML_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;
| Column Name | Example Value |
| NumberOfGrades | 5 |
SELECT Name, XML_SUM(score,'[x].score') AS TotalScore FROM Students;
| Column Name | Example Value |
| TotalScore | 41 |
SELECT Name, XML_MIN(score,'[x].score') AS LowestScore FROM Students;
| Column Name | Example Value |
| LowestScore | 2 |
SELECT Name, XML_MAX(score,'[x].score') AS HighestScore FROM Students;
| Column Name | Example Value |
| HighestScore | 14 |
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 allows you to persist and customize the dynamic schemas discovered through Automatic Schema Discovery.
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.
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.
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.
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.
| Name | Type | Description |
| TableName | String | The name of the table and also the name of the schema (RSD) file. |
| URI | String | The Uniform Resource Identifier (URI) of the XML resource. |
| XPath | String | The XPath of an element that repeats at the same height within the XML document. (This is used to split the document into multiple rows). You can specify multiple paths in a semicolon-separated list. |
| FileLocation | String | The folder path where the generated schema (RSD) file will be stored. When specified the TableName will be used as the schema file name. |
| FileName | String | The complete schema (RSD) file name of the generated schema. This input takes precedence over FileLocation. |
| Name | Type | Description |
| Result | String | Returns Success or Failure. |
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'
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>
The following sections provide more detail on using XPaths to extract columns and rows. To see the column definitions in a complete schema, refer to Customizing Schemas.
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:
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/*" />
You can set the other:valueFormat property to "aggregate" to identify a column as an aggregate.
<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>
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.
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>'and generate a request that appends the query parameter '&modifiedBefore=<url encoded datetime>' to the url.
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.
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.
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.
Set the URI attribute to a local file or an HTTP-accessible address.
<api:set attr="uri" value="NorthwindOData.xml" />
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/" />
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>
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:
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>
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:
<api:info>
...
<input name="Location" required="true"/>
</api:info>
<api:set attr='uri' value="http://api.wunderground.com/api/[_connection.APIKey]/hourly/q/[_input.Location].xml"/><api:script method="GET" >
<api:push op="xmlproviderGet"/>
</api:script> 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." />
Note that making this an input parameter instead of an attr parameter will prevent it from
showing up in column listings.
You will also need to set the 'EnablePaging' attribute to TRUE to turn off the driver's internal paging mechanism.
<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:
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.
<api:set attr="pageurlpath" value="/data/nextPage" /> <api:set attr="pageurlpath" value="header:Link" /> 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.
<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><api:set attr="pagetokenpath" value="/request/nextpagetoken" /> 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" />
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" />
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" />
You can then modify the URL where the request is made, provided the value is set. Use the api:check element to first check if the Rows@Next input has a value. The Rows@Next input can be accessed as an attribute of the _input item:
<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>
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:
| query | The SQL query. For example:
SELECT Id, Name FROM Accounts WHERE City LIKE '%New%' AND COUNTRY = 'US' GROUP BY CreatedDate ORDER BY Name LIMIT 10,50; |
| selectcolumns | A comma-separated list containing the columns specified in the SELECT statement. For example, the Id and Name columns in the example. |
| table | The table name specified in the SELECT statement. For example, Accounts in the example. |
| criteria | The WHERE clause of the statement.
City LIKE '%New%' AND COUNTRY = 'US' |
| orderby | The columns specified in the ORDER BY clause. For example, Name in the example. |
| groupby | The GROUP BY clause in the SELECT statement. For example, CreatedDate in the example. |
| limit | The limit specified in the LIMIT or TOP clauses of the SELECT statement. For example, 50 in the example. |
| offset | The offset specified in the LIMIT or TOP clauses of the SELECT statement. For example, 10 in the example. |
| isjoin | Whether the query is a join. |
| jointable | The table to be joined. |
| isschemaonly | Whether the query retrieves only schema information. |
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>
See the API Script Reference for more information on the keywords used in this section:
When an INSERT statement is executed, the Sync App executes the POST method of the schema, where you can build the HTTP insert request.
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>
In the example schema's corresponding POST method, the required columns are first validated and then used to define the POST data. You can check that a column was provided and alert the user otherwise with the api:validate keyword.
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>
Note that an INSERT statement can sometimes return data, for example, the generated Id of the new record. If you want to access values returned from an insert, use the api:push keyword instead of the api:call keyword.
In the POST method, the _query item contains the following attributes:
| query | The SQL statement, as in the following statement:
INSERT INTO Account (account_name, account_type) VALUES ('Contoso','Company') |
| table | The table in the SQL statement. For example, Account in the preceding query. |
See the API Script Reference for more information on the keywords used in this section:
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.
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>
In the example schema's corresponding MERGE method, the required column, the primary key, is validated and then used in the PUT data and the URL. You can check that an input was provided and alert the user otherwise with the api:validate keyword.
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>
In the MERGE method, the _query item contains the following attributes:
| query | The SQL statement, as in the following statement:
UPDATE Account SET Name='John' WHERE Id = @myId |
| table | The table in the SQL statement. For example, Account in the preceding query. |
| criteria | The WHERE clause of the statement. For example, the following WHERE clause in the example:
Id = @myId |
| nullupdates | The columns of an UPDATE statement, separated by commas, that contain a null value or a null parameter value. |
See the API Script Reference for more information on the keywords used in this section:
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.
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.
In the DELETE script, the _query item contains the following attributes:
| query | The SQL statement, as in the following statement:
DELETE FROM Account WHERE Id = @myId |
| table | The table in the SQL statement. For example, Account in the preceding query. |
| criteria | The WHERE clause of the statement. For example, the following WHERE clause in the example:
Id = @myId |
See the API Script Reference for more information on the keywords used in this section:
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.
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.
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>
See the API Script Reference for more information on the keywords used in this section:
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:
| Operation Name | Description | |
| xmlproviderGet | The xmlproviderGet operation is an APIScript operation that is used to process XML content. It allows you to split XML content into rows. | |
| oauthGetAccessToken | For OAuth 1.0, exchange a request token for an access token. For OAuth 2.0, get an access token or get a new access token with the refresh token. | |
| oauthGetUserAuthorizationURL | Generates the user authorization URL. OAuth 2.0 will not access the network in this operation. | |
| utiladoSleep | Suspends processes for a specified duration (in seconds). |
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.
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/*" />
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.
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.
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.
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/*" />
The allowed values are TRUE, FALSE. The default value is FALSE.
<api:call op="xmlproviderGet" out="output">
<api:map from="output" to="temp" map="* = *" />
<api:push item="temp" />
</api:call>
The items produced by this operation are required to have the same layout for performance reasons, which may not be the case if different documents are used in
calls to this operation. Performing this map converts the item into a more flexible form which does not have the same layout restriction (though it may be slower
to read from).
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.
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>
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>
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.
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>
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.
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.
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.
Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.
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.
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.
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.
The XML Sync App also supports setting client certificates. Set the following to connect using a client certificate.
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.
Set the following properties:
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="name" value="Bob" Attribute="address" value="123 Pleasant Lane" Attribute="phone" value="123-4567"
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.
In API Script items are created, named, and given attribute values through the api:set keyword:
<api:set item="input" attr="mask" value="*.txt" />
The line above sets the "mask" attribute to the value "*.txt" on the item named "input". In this case, the input item is like a variable in API Script.
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.
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:
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:
<api:set attr="path" value="." />
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.
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.
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.
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.
The _query item has the following attributes that describe the query that was issued to the Sync App:
| query | The SQL statement. For example:
SELECT Id, Name FROM Accounts WHERE City LIKE '%New%' AND COUNTRY = 'US' GROUP BY CreatedDate ORDER BY Name LIMIT 10,50; |
| selectcolumns | A comma-separated list of the columns in the SELECT clause. For example, the Id and Name columns in the example. If "*" is specified in the SELECT clause, the value of [_query.selectcolumns] is "*". |
| table | The table name. For example, Accounts in the example. |
| isjoin | Whether the query is a join. |
| jointable | The table in the JOIN clause. |
| criteria | The WHERE clause. For example, the following WHERE clause in the example:
City LIKE '%New%' AND COUNTRY = 'US' |
| orderby | The ORDER BY clause. For example, Name in the example. |
| groupby | The GROUP BY clause. For example, CreatedDate in the example. |
| limit | The limit specified in the LIMIT or TOP clauses of the SELECT statement. For example, 50 in the example. |
| offset | The offset specified in the LIMIT or TOP clauses of the SELECT statement. For example, 10 in the example. |
| insertselect | The SELECT statement nested in an INSERT statement. |
| updateselect | The SELECT statement nested in an UPDATE statement. |
| upsertselect | The SELECT statement nested in an UPSERT statement. |
| deleteselect | The SELECT statement nested in a DELETE statement. |
| bulkoperationcolumns | The columns of the table the bulk operation modifies, separated by commas. For example, consider the following query:
INSERT INTO Account(account_name, account_type) SELECT customer_name, customer_type FROM Customer#TEMP[_query.bulkoperationcolumns] returns the following: [account_name], [account_type] |
| temptablecolumns | The columns selected from the temp table in a bulk operation, separated by commas. For example, consider the following query:
DELETE FROM Account WHERE EXISTS SELECT customer_name, customer_type FROM Customer#TEMP[_query.temptablecolumns] returns the following: [customer_name], [customer_type] |
| nullupdates | The columns of an UPDATE statement, separated by commas, that contain a null value or a null parameter value. |
| isschemaonly | Whether the query retrieves only schema information. |
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) | ...]where formatter is the name of the formatter and parameters is an optional set of parameters to control formatter output. Formatter output can be provided as input to another formatter with the pipe character ("|").
<api:set attr="input1.id" value="[myid | replace('*', '-')]"/><api:call op="fileListDir">
<api:check attr="name" value="[filename|tolower | endswith('.log')]">
<api:push/>
</api:check>
</api:call>
Returns NULL if the attribute does not exist or the value if it does.
Returns the index at which the string is found in the attribute array. The index is 1 based.
Converts the attribute value to a base 64 decoded string.
Converts the attribute value to a base 64 encoded string.
Returns the original attribute value with only its first character capitalized.
Returns the original attribute value with the first character of all words capitalized.
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.
Returns true (or ifcontains) if the attribute value contains the parameter value, false (or ifnotcontains) otherwise.
Returns the number of occurrences in the attribute value of a substring specified by the first parameter.
Returns the numeric value formatted as currency.
Returns the numeric value formatted as a decimal number.
Checks for the existence of an attribute and returns the specified parameter value if it does not.
Returns the specified value if the attribute value is empty, otherwise the original attribute value.
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.
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.
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.
Evaluates the mathematical expression.
Returns the lowest zero-based index at which the substring is found in the attribute value.
Returns the number of characters in the attribute value.
Encrypt a string with the passed-in key and HmacSHAMD5 algorithm.
Encrypt a string with the passed-in key and HmacSHA1 algorithm.
Encrypt a string with the passed-in key and HmacSHA256 algorithm.
Encrypt a string with the passed-in key and HmacSHA512 algorithm.
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.
Returns true (or ifmatch) if the attribute value matches the first parameter, otherwise false (or ifnotmatch).
Checks the attribute value and returns true (or iftrue) if true and false (or iffalse) if false.
Implodes multiple values to a string separated by a separator.
Inserts the specified string at the specified index.
Returns true (or ifalpha) if all characters in the attribute value are alphabetic and there is at least one character, false (or ifnotalpha) otherwise.
Returns true (or ifalpha) if all characters in the attribute value are alphabetic and there is at least one character, false (or ifnotalpha) otherwise.
Returns true (or ifalphanum) if all characters in the attribute value are alphanumeric and there is at least one character, false (or ifnotalphanum) otherwise.
Returns true (or ifalphanum) if all characters in the attribute value are alphanumeric and there is at least one character, false (or ifnotalphanum) otherwise.
Returns true (or ifnum) if all characters in the attribute value are digits and there is at least one character, false (or ifnotnum) otherwise.
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.
Returns true (or ifnum) if all characters in the attribute value are digits and there is at least one character, false (or ifnotnum) otherwise.
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.
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.
Implodes multiple values to a string separated by a separator.
Converts the attribute value to a JSON-escaped, single-line string.
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.
Returns the lowest zero-based index at which the substring is found in the attribute value.
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.
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.
Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.
Computes the MD5 hash of the attribute value.
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.
Removes the white space from the string represented by the atttribute value.
Returns the numeric value formatted as a percentage.
Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.
Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.
Replaces all occurrences of the regular expression pattern found in the attribute value with replacewith.
Deletes characters from the attribute value; begins at the zero-based index specified by the first parameter.
Replaces all occurrences of the first parameter in the string represented by the attribute value with the value of the second parameter.
Returns the highest zero-based index at which the substring is found in the attribute value.
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.
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.
Computes the SHA-1 hash of the attribute value.
Returns a substring of the attribute value; starts at the index specified by the parameter and counts to the right.
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.
Converts the attribute value to an SQL-escaped, single-line string.
Returns true (or iftrue) if the attribute value starts with the specified parameter, false (or iffalse) otherwise.
Returns the string with any HTML markup removed.
Returns a substring of the attribute value; starts at the index specified by the parameter.
Returns only the letters in a string.
Returns only the alphanumeric characters in a string.
Returns the string represented by the attribute value with all characters converted to lowercase.
Returns the string represented by the attribute value with all characters converted to uppercase.
Trims leading and trailing white space from an attribute.
Trims trailing white space from an attribute.
Trims leading white space from an attribute.
Truncates the attribute value to the number of characters specified by the parameter.
Wraps a string to a given number of characters.
Returns a string with all the values of the attribute concatenated using the specified delimiter.
Converts the attribute value to a XML decoded string.
Converts the attribute value to a XML encoded string.
Returns a signed number indicating the relative values of dates represented by the attribute value and parameter value.
Returns the current system date and time in the format specified by the parameter if one was provided.
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.
Returns the difference (in units specified by the first parameter) between now and the date specified by the second parameter.
Returns the day component, expressed as a value between 1 and 31, of the date represented by the attribute value.
Returns the day of week for the date represented by the attribute value.
Returns the day of year expressed as a value between 1 and 366 for the date represented by the attribute value.
Returns the date and time for the current system file time.
Converts a valid file time to a valid datetime value formatted as specified by the parameter if one was provided.
Returns true (or ifleap) if the 4-digit year represented by the attribute value is a leap year, false (or ifnotleap) otherwise.
Returns the month component expressed as a value between 1 and 12 of the date represented by the attribute value.
Returns the current system date and time in the format specified by the parameter if one was provided.
Returns the date specified by the attribute value formatted as specified by the parameter if one was provided.
Converts a valid datetime to a valid file time value.
Returns the name of the month for the numeric value specified by the attribute value.
Returns the date specified by the attribute value converted to UTC and formatted as specified by the outputformat parameter if one was provided.
Returns the current system UTC date and time.
Returns the day of the week as an integer where Monday is 0 and Sunday is 6.
Returns the year component of the date represented by the attribute value.
Returns the absolute value of the numeric attribute value.
Returns the sum of the numeric attribute value and the value specified by the parameter.
Returns the AND of two values. The values provided on each side must be 1/0, yes/no or true/false.
Returns the smallest integer greater than or equal to a numeric attribute value.
Returns the result of dividing the numeric attribute value by the specified value of the parameter.
Returns the result of dividing the numeric attribute value by the specified value of the parameter.
Returns the largest integer less than or equal to the numeric attribute value.
Returns true (or ifgreater) if the attribute value is greater than the parameter value, false (or ifnotgreater) otherwise.
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.
Returns true (or ifequal) if the attribute value is equal to the parameter value, false (or ifnotequal) otherwise.
Returns true (or ifgreater) if the attribute value is greater than the parameter value, false (or ifnotgreater) otherwise.
Returns true (or ifless) if the attribute value is less than the parameter value, false (or ifnotless) otherwise.
Returns true (or ifless) if the attribute value is less than the parameter value, false (or ifnotless) otherwise.
Returns the sum of the numeric attribute value and the value specified by the parameter.
Returns the modulus of the numeric attribute value divided by the specified parameter value.
Returns the numeric attribute value raised to the power specified by the parameter value.
Returns the numeric attribute value rounded to the number of decimal places specified by the parameter.
Returns the difference between the numeric attribute value and the value specified by the parameter.
Returns the modulus of the numeric attribute value divided by the specified parameter value.
Returns the result of multiplying the numeric attribute value with the specified value of the parameter.
Returns the OR of two values. The values provided on each side must be 1/0, yes/no or true/false.
Returns the numeric attribute value raised to the power specified by the parameter value.
Returns a random integer between 0 and the parameter value.
Returns a random integer between 0 and the parameter value.
Returns the numeric attribute value rounded to the number of decimal places specified by the parameter.
Returns the square root of the numeric attribute value.
Returns the difference between the numeric attribute value and the value specified by the parameter.
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.
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:
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.
None
None
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>
The api:call keyword is used to call operations. Valid operations are the following:
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.
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>
There are 3 ways to provide input to a call:
<api:set attr="mask" value="*.txt"/>
<api:set attr="path" value="C:\\"/>
<api:call op="fileListDir">
...
</api:call>
<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>
<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>
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.
Call an operation using the default item as input:
<api:set attr="path" value="C:\myfiles"/>
<api:call op="fileListDir">
<api:push/>
</api:call>
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.
None
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>
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:
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>
Catch all exceptions:
<api:catch code="*">
An exception occurred. Code: [_code], Message: [_desc]
</api:catch>
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.
<api:check attr="_input.In_Stock">
...
</api:check>
The api:continue keyword can be used to move to the next iterations of api:call or api:enum.
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>
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.
<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>
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.
<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>
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.
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
To enumerate over a list of values use the list and separator parameters of api:enum. For example, the code below lists the colors specified in the colors attribute:
<api:set attr="colors" value="violet, indigo, blue, green, yellow, orange, red"/>
<api:enum list="[colors]" separator=",">
[_value]
</api:enum>
To enumerate over a range of values, use the range attribute. For example, the code below lists the character set from a to z, from Z to A, and the numbers from 1 to 25:
<api:enum range="a..z">
[_value]
</api:enum>
<api:enum range="Z..A">
[_value]
</api:enum>
<api:enum range="1..25">
[_value]
</api:enum>
To enumerate over all the values of a multivalued attribute, use the attr argument to specify a multivalued attribute and set expand to true:
<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>
The example above results in the following output:
(1) email#1 -> [email protected] (2) email#2 -> [email protected]
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>
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>
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.
<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>
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.
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>
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.
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>
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.
None
Evaluate a simple comparison of two values:
<api:if exp="[attr] == 10">
Evaluate the equality of a given value and the value of a given attribute:
<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>
Evaluate whether an attribute exists:
<api:set attr="exists" value="true"/>
<api:if attr="exists"> <!-- Evaluates to true -->
[exists]
</api:if>
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.
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:
The following parameters can be defined for the table itself:
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.
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."
/>
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"
/>
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"
/>
<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>
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.
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>
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.
customer:* = soap:*Any characters that are not valid attribute names are ignored and are used to demarcate the end of a name.
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"/>
In this example, the var1 and var2 attributes of item1 are mapped respectively to the attr1 and attr2 attributes of item2. The attr1 attribute, which is set in item2 with the value z, is overwritten with x, the value of var1 from item1. The attr2 attribute, which does not exist in item2, is created and set with y, the value of var2 in item1.
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:*"/>
Copy all attributes from one item to another item:
<api:map from="copyfrom" to="copyto" map="* = *"/>
The api:match keyword is similar to the api:equals keyword; however, it permits complex matching rules.
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.
None
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>
The api:notequals keyword verifies that the attribute does not match the specified value. It has a similar behavior to the api:equals keyword.
<api:call op="fileListDir">
<api:notequals attr="file:extension" value=".err">
<api:push/>
</api:notequals>
</api:call>
The api:null keyword checks that an attribute does not exist in the specified item.
<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>
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.
<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>
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.
<api:push op="myOp"/>
This is a shorthand for the following:
<api:call op="myOp">
<api:push/>
</api:call>
<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.
None
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.
None
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>
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.
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>
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.
None
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.
None
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>
The api:throw keyword is used to raise an error (exception) from within a script.
None
The example below explicitly defines both the error code and description:
<api:throw code="myerror" desc="thedescription" />
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.
None
None
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>
The api:unset keyword is used to delete attributes from an item or delete the item itself.
Remove an attribute from an item before it is pushed out:
<api:call op="fileListDir">
<api:unset attr="file:size"/>
<api:push/>
</api:call>
You can use api:validate to throw an error if a required value is not provided.
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."/>
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.
For more information on establishing a connection, see Establishing a Connection.
| Property | Description |
| AuthScheme | The type of authentication to use when connecting to remote services. |
| AccessKey | Your account access key. This value is accessible from your security credentials page. |
| SecretKey | Your account secret key. This value is accessible from your security credentials page. |
| ApiKey | The API Key used to identify the user to IBM Cloud. |
| User | The user account used to authenticate. |
| Password | The password used to authenticate the user. |
| SharePointEdition | The edition of SharePoint being used. Set either SharePointOnline or SharePointOnPremise. |
| Property | Description |
| ConnectionType | Specifies the file storage service, server, or file access protocol through which your XML files are stored and retreived. |
| URI | The Uniform Resource Identifier (URI) for the XML resource location. |
| XPath | The XPath of an element that repeats at the same height within the XML document (used to split the document into multiple rows). |
| DataModel | Specifies the data model to use when parsing XML documents and generating the database metadata. |
| XMLFormat | Specifies the format of the XML document. |
| Region | The hosting region for your S3-like Web Services. |
| ProjectId | The Id of the project where your Google Cloud Storage instance resides. |
| OracleNamespace | The Oracle Cloud Object Storage namespace to use. |
| StorageBaseURL | The URL of a cloud storage service provider. |
| SimpleUploadLimit | This setting specifies the threshold, in bytes, above which the provider will choose to perform a multipart upload rather than uploading everything in one request. |
| UseVirtualHosting | If true (default), buckets will be referenced in the request using the hosted-style request: http://yourbucket.s3.amazonaws.com/yourobject. If set to false, the bean will use the path-style request: http://s3.amazonaws.com/yourbucket/yourobject. Note that this property will be set to false, in case of an S3 based custom service when the CustomURL is specified. |
| UseLakeFormation | When this property is set to true, AWSLakeFormation service will be used to retrieve temporary credentials, which enforce access policies against the user based on the configured IAM role. The service can be used when authenticating through OKTA, ADFS, AzureAD, PingFederate, while providing a SAML assertion. |
| Property | Description |
| AWSAccessKey | Your AWS account access key. This value is accessible from your AWS security credentials page. |
| AWSSecretKey | Your AWS account secret key. This value is accessible from your AWS security credentials page. |
| AWSRoleARN | The Amazon Resource Name of the role to use when authenticating. |
| AWSPrincipalARN | The ARN of the SAML Identity provider in your AWS account. |
| AWSRegion | The hosting region for your Amazon Web Services. |
| AWSCredentialsFile | The path to the AWS Credentials File to be used for authentication. |
| AWSCredentialsFileProfile | The name of the profile to be used from the supplied AWSCredentialsFile. |
| AWSSessionToken | Your AWS session token. |
| AWSExternalId | A unique identifier that might be required when you assume a role in another account. |
| MFASerialNumber | The serial number of the MFA device if one is being used. |
| MFAToken | The temporary token available from your MFA device. |
| TemporaryTokenDuration | The amount of time (in seconds) a temporary token will last. |
| ServerSideEncryption | When activated, file uploads into Amazon S3 buckets will be server-side encrypted. |
| SSEContext | A BASE64-encoded UTF-8 string holding JSON which represents a string-string (key-value) map. |
| SSEEnableS3BucketKeys | Configuration to use an S3 Bucket Key at the object level when encrypting data with AWS KMS. Enabling this will reduce the cost of server-side encryption by lowering calls to AWS KMS. |
| SSEKey | A symmetric encryption KeyManagementService key, that is used to protect the data when using ServerSideEncryption. |
| Property | Description |
| AzureStorageAccount | The name of your Azure storage account. |
| AzureAccessKey | The storage key associated with your Azure account. |
| AzureSharedAccessSignature | A shared access key signature that may be used for authentication. |
| AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
| AzureEnvironment | The Azure Environment to use when establishing a connection. |
| Property | Description |
| SSOLoginURL | The identity provider's login URL. |
| SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
| SSOExchangeUrl | The URL used for consuming the SAML response and exchanging it for service specific credentials. |
| Property | Description |
| OAuthVersion | The version of OAuth being used. |
| OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
| OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
| Scope | Specify scope to obtain the initial access and refresh token. |
| OAuthGrantType | The grant type for the OAuth flow. |
| OAuthPasswordGrantMode | How to pass Client Id and Secret with OAuthGrantType is set to Password. |
| OAuthIncludeCallbackURL | Whether to include the callback URL in an access token request. |
| OAuthAuthorizationURL | The authorization URL for the OAuth service. |
| OAuthAccessTokenURL | The URL to retrieve the OAuth access token from. |
| OAuthRefreshTokenURL | The URL to refresh the OAuth token from. |
| OAuthRequestTokenURL | The URL the service provides to retrieve request tokens from. This is required in OAuth 1.0. |
| AuthToken | The authentication token used to request and obtain the OAuth Access Token. |
| AuthKey | The authentication secret used to request and obtain the OAuth Access Token. |
| OAuthParams | A comma-separated list of other parameters to submit in the request for the OAuth access token in the format paramname=value. |
| Property | Description |
| OAuthJWTCert | The JWT Certificate store. |
| OAuthJWTCertType | The type of key store containing the JWT Certificate. |
| OAuthJWTCertPassword | The password for the OAuth JWT certificate. |
| OAuthJWTCertSubject | The subject of the OAuth JWT certificate. |
| OAuthJWTAudience | A space-separated list of entities that may use the JWT. |
| OAuthJWTValidityTime | How long the JWT should remain valid, in seconds. |
| Property | Description |
| KerberosKDC | The Kerberos Key Distribution Center (KDC) service used to authenticate the user. |
| KerberosRealm | The Kerberos Realm used to authenticate the user. |
| KerberosSPN | The service principal name (SPN) for the Kerberos Domain Controller. |
| KerberosKeytabFile | The Keytab file containing your pairs of Kerberos principals and encrypted keys. |
| KerberosServiceRealm | The Kerberos realm of the service. |
| KerberosServiceKDC | The Kerberos KDC of the service. |
| KerberosTicketCache | The full file path to an MIT Kerberos credential cache file. |
| Property | Description |
| SSLClientCert | The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). |
| 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. |
| SSLMode | The authentication mechanism to be used when connecting to the FTP or FTPS server. |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
| Property | Description |
| SSHAuthMode | The authentication method used when establishing an SSH Tunnel to the service. |
| SSHClientCert | A certificate to be used for authenticating the SSHUser. |
| SSHClientCertPassword | The password of the SSHClientCert key if it has one. |
| SSHClientCertSubject | The subject of the SSH client certificate. |
| SSHClientCertType | The type of SSHClientCert private key. |
| SSHUser | The SSH user. |
| SSHPassword | The SSH password. |
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
| Property | Description |
| LogModules | Core modules to be included in the log file. |
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
| PushAttributes | Set PushAttributes to true to push any identified attributes as columns. |
| FlattenArrays | By default, nested arrays are returned as strings of XML. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. Set FlattenArrays to the number of elements you want to return from nested arrays. |
| FlattenObjects | Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of XML. |
| QualifyColumns | Controls whether the provider will use relative column names. |
| Property | Description |
| BackwardsCompatibilityMode | Set BackwardsCompatibilityMode to true to use the XML functionality and features available in the 2017 version. |
| Charset | Specifies the session character set for encoding and decoding character data transferred to and from the XML file. The default value is UTF-8. |
| ClientCulture | This property can be used to specify the format of data (e.g., currency values) that is accepted by the client application. This property can be used when the client application does not support the machine's culture settings. For example, Microsoft Access requires 'en-US'. |
| Culture | This setting can be used to specify culture settings that determine how the provider interprets certain data types that are passed into the provider. For example, setting Culture='de-DE' will output German formats even on an American machine. |
| CustomHeaders | Other headers as determined by the user (optional). |
| CustomUrlParams | The custom query string to be included in the request. |
| ExcludeFiles | Comma-separated list of file extensions to exclude from the set of the files modeled as tables. |
| FlattenRowLimit | The maximum number of rows that can result from a single flattened element. |
| FolderId | The ID of a folder in Google Drive. If set, the resource location specified by the URI is relative to the Folder ID for all operations. |
| GenerateSchemaFiles | Indicates the user preference as to when schemas should be generated and saved. |
| IncludeDropboxTeamResources | Indicates if you want to include Dropbox team files and folders. |
| IncludeFiles | Comma-separated list of file extensions to include into the set of the files modeled as tables. |
| IncludeItemsFromAllDrives | Whether Google Drive shared drive items should be included in results. If not present or set to false, then shared drive items are not returned. |
| MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
| MetadataDiscoveryURI | Used when aggregating multiple files into one table, this property specifies a specific file to read to determined the aggregated table schema. |
| Other | These hidden properties are used only in specific use cases. |
| Pagesize | The maximum number of results to return per page from XML. |
| PathSeparator | Determines the character which will be used to replace the file separator. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| RowScanDepth | The number of rows to scan when dynamically determining columns for the table. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| TypeDetectionScheme | Determines how to determine the data types of columns. |
| URISeparator | A delimiter used to separate different values in the URI property. |
| UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| AuthScheme | The type of authentication to use when connecting to remote services. |
| AccessKey | Your account access key. This value is accessible from your security credentials page. |
| SecretKey | Your account secret key. This value is accessible from your security credentials page. |
| ApiKey | The API Key used to identify the user to IBM Cloud. |
| User | The user account used to authenticate. |
| Password | The password used to authenticate the user. |
| SharePointEdition | The edition of SharePoint being used. Set either SharePointOnline or SharePointOnPremise. |
The type of authentication to use when connecting to remote services.
The following options are available when ConnectionType is set to Amazon S3:
The following options are available when ConnectionType is set to Azure Blob Storage, Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, Azure Data Lake Storage Gen2 SSL, or OneDrive:
The following options are available when ConnectionType is set to Box:
Only the following option is available when ConnectionType is set to Dropbox:
OAuth: Uses either OAuth1 or OAuth2, with the specific flow being determined by the OAuthGrantType. OAuthVersion must be set to determine what version of OAuth is used.
Only the following option is available when ConnectionType is set to FTP or FTPS:
Basic: Basic user credentials (user/password).
The following options are available when ConnectionType points Google Cloud Storage or Google Drive:
The following options are available when ConnectionType is set to HDFS or HDFS Secure:
The following options are available when ConnectionType is set to HTTP or HTTPS:
The following options are also available when ConnectionType is set to IBM Object Storage Source:
Only the following option is available when ConnectionType is set to Oracle Cloud Storage:
HMAC: Uses AccessKey and SecretKey to authenticate to the Oracle Cloud Storage.
This ConnectionType defaults to using an AuthScheme called SFTP, but the authentication method is actually controlled using the SSHAuthMode property. See this property's documentation for further information.
The following options are also available when ConnectionType is set to SharePoint REST:
The following options are also available when ConnectionType is set to SharePoint SOAP:
Your account access key. This value is accessible from your security credentials page.
Your account access key. This value is accessible from your security credentials page depending on the service you are using.
Your account secret key. This value is accessible from your security credentials page.
Your account secret key. This value is accessible from your security credentials page depending on the service you are using.
The API Key used to identify the user to IBM Cloud.
Access to resources in the XML REST API is governed by an API key in order to retrieve token. An API Key can be created by navigating to Manage --> Access (IAM) --> Users and clicking 'Create'.
The user account used to authenticate.
Together with Password, this field is used to authenticate against the server.
This property will refer to different things based on the context, namely the value of ConnectionType and AuthScheme:
The password used to authenticate the user.
The User and Password are together used to authenticate with the server.
This property will refer to different things based on the context, namely the value of ConnectionType and AuthScheme:
This section provides a complete list of the Connection properties you can configure in the connection string for this provider.
| Property | Description |
| ConnectionType | Specifies the file storage service, server, or file access protocol through which your XML files are stored and retreived. |
| URI | The Uniform Resource Identifier (URI) for the XML resource location. |
| XPath | The XPath of an element that repeats at the same height within the XML document (used to split the document into multiple rows). |
| DataModel | Specifies the data model to use when parsing XML documents and generating the database metadata. |
| XMLFormat | Specifies the format of the XML document. |
| Region | The hosting region for your S3-like Web Services. |
| ProjectId | The Id of the project where your Google Cloud Storage instance resides. |
| OracleNamespace | The Oracle Cloud Object Storage namespace to use. |
| StorageBaseURL | The URL of a cloud storage service provider. |
| SimpleUploadLimit | This setting specifies the threshold, in bytes, above which the provider will choose to perform a multipart upload rather than uploading everything in one request. |
| UseVirtualHosting | If true (default), buckets will be referenced in the request using the hosted-style request: http://yourbucket.s3.amazonaws.com/yourobject. If set to false, the bean will use the path-style request: http://s3.amazonaws.com/yourbucket/yourobject. Note that this property will be set to false, in case of an S3 based custom service when the CustomURL is specified. |
| UseLakeFormation | When this property is set to true, AWSLakeFormation service will be used to retrieve temporary credentials, which enforce access policies against the user based on the configured IAM role. The service can be used when authenticating through OKTA, ADFS, AzureAD, PingFederate, while providing a SAML assertion. |
Specifies the file storage service, server, or file access protocol through which your XML files are stored and retreived.
Set the ConnectionType to one of the following:
Set the ConnectionType to one of the following:
The Uniform Resource Identifier (URI) for the XML resource location.
Set the URI property to specify a path to a file or stream.
NOTE:
See Fine-Tuning Data Access for more advanced features available for parsing and merging multiple files.
Below are examples of the URI formats for the available data sources:
| Service provider | URI formats | |
| Local | Single File Path One table
localPath/file.xml file://localPath/file.xml Directory Path (One aggregated table from all files) localPath file://localPath | |
| HTTP or HTTPS | http://remoteStream
https://remoteStream | |
| Amazon S3 | Single File Path One table
s3://remotePath/file.xml Directory Path (One aggregated table from all files) s3://remotePath | |
| Azure Blob Storage | Single File Path One table
azureblob://mycontainer/myblob//file.xml Directory Path (One aggregated table from all files) azureblob://mycontainer/myblob/ | |
| OneDrive | Single File Path One table
onedrive://remotePath/file.xml Directory Path (One aggregated table from all files) onedrive://remotePath | |
| Google Cloud Storage | Single File Path One table
gs://bucket/remotePath/file.xml Directory Path (One aggregated table from all files) gs://bucket/remotePath | |
| Google Drive | Single File Path One table
gdrive://remotePath/file.xml Directory Path (One aggregated table from all files) gdrive://remotePath | |
| Box | Single File Path One table
box://remotePath/file.xml Directory Path (One aggregated table from all files) box://remotePath | |
| FTP or FTPS | Single File Path One table
ftp://server:port/remotePath/file.xml Directory Path (One aggregated table from all files) ftp://server:port/remotePath | |
| SFTP | Single File Path One table
sftp://server:port/remotePath/file.xml Directory Path (One aggregated table from all files) sftp://server:port/remotePath | |
| Sharepoint | Single File Path One table
sp://https://server/remotePath/file.xml Directory Path (One aggregated table from all files) sp://https://server/remotePath |
Below are example connection strings to XML files or streams.
| Service provider | URI formats | Connection example |
| Local | Single File Path One table
localPath file://localPath/file.xml Directory Path (One aggregated table from all files) localPath file://localPath | URI=C:\folder1/file.xml |
| Amazon S3 | Single File Path One table
s3://bucket1/folder1/file.xml Directory Path (One aggregated table from all files) s3://bucket1/folder1 | URI=s3://bucket1/folder1/file.xml; AWSAccessKey=token1; AWSSecretKey=secret1; AWSRegion=OHIO; |
| Azure Blob Storage | Single File Path One table
azureblob://mycontainer/myblob//file.xml Directory Path (One aggregated table from all files) azureblob://mycontainer/myblob/ | URI=azureblob://mycontainer/myblob/; AzureStorageAccount=myAccount; AzureAccessKey=myKey;
URI=azureblob://mycontainer/myblob/; AzureStorageAccount=myAccount; AuthScheme=OAuth; |
| OneDrive | Single File Path One table
onedrive://remotePath/file.xml Directory Path (One aggregated table from all files) onedrive://remotePath | URI=onedrive://folder1/file.xml; AuthScheme=OAuth;
URI=onedrive://SharedWithMe/folder1/file.xml; AuthScheme=OAuth; |
| Google Cloud Storage | Single File Path One table
gs://bucket/remotePath/file.xml Directory Path (One aggregated table from all files) gs://bucket/remotePath | URI=gs://bucket/folder1/file.xml; AuthScheme=OAuth; ProjectId=test; |
| Google Drive | Single File Path One table
gdrive://remotePath/file.xml Directory Path (One aggregated table from all files) gdrive://remotePath | URI=gdrive://folder1/file.xml; |
| Box | Single File Path One table
box://remotePath/file.xml Directory Path (One aggregated table from all files) box://remotePath | URI=box://folder1/file.xml; OAuthClientId=oauthclientid1; OAuthClientSecret=oauthcliensecret1; CallbackUrl=http://localhost:12345; |
| FTP or FTPS | Single File Path One table
ftp://server:port/remotePath/file.xml Directory Path (One aggregated table from all files) ftp://server:port/remotePath | URI=ftps://localhost:990/folder1/file.xml; User=user1; Password=password1; |
| SFTP | sftp://server:port/remotePath | URI=sftp://127.0.0.1:22/remotePath/file.xml; User=user1; Password=password1; |
| Sharepoint | sp://https://server/remotePath | URI=sp://https://domain.sharepoint.com/Documents/file.xml; User=user1; Password=password1; |
The XPath of an element that repeats at the same height within the XML document (used to split the document into multiple rows).
The value of this option depends on the current XMLFormat. By default Sync App automatically finds the object arrays in the document and models them as rows. This parameter allows you to explicitly define the object arrays using XPaths.
Multiple paths can be specified using a semicolon-separated list. The DataModel property governs how the nested object arrays are modeled as tables.
When using the XMLTable XMLFormat, this option uses a special format so that both column and row paths may be provided. Refer to the XMLFormat documentation for more details.
This property will be used to generate the schema definition when a schema file is not present (see Customizing Schemas).
Example XPath for the people example in Raw Data: $.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:
<rsb:set attr="XPath" value="/feed/*" />
Specifies the data model to use when parsing XML documents and generating the database metadata.
The Sync App splits XML documents into rows based on elements that repeat at the same level.
By default, the Sync App projects columns over the properties of objects and returns arrays as XML aggregates.
In the following example, jobs is a primitive array:
<jobs>sales</jobs>
<jobs>marketing</jobs>
In the following example, 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>
The following DataModel configurations are available. See Parsing Hierarchical Data for examples of querying the data in the different configurations.
Document
Returns a single table representing a row for each top-level object. In this data model, any nested object arrays will not be flattened and will be returned as aggregates. Unless an XPath value is explicitly specified, the Sync App will identify and use the top-most object array found as the XPath.
FlattenedDocuments
Returns a single table representing a JOIN of the available documents in the file. In this data model, nested XPath values will act in the same manner as a SQL JOIN. Additionally, nested sibling XPath values (child paths at the same height), will be treated as a SQL CROSS JOIN. Unless explicitly specified, the Sync App will identify the XPath values available by parsing the file and identifying the available documents, including nested documents.
Relational
Returns multiple tables, one for each XPath value specified. In this data model, any nested documents (object arrays) will be returned as relational tables that contain a primary key and a foreign key that links to the parent table. Unless explicitly specified, the Sync App will identify the XPath values available by parsing the file and identifying the available documents (including nested documents).
Specifies the format of the XML document.
The following XMLFormat configurations are available.
XML
This is the default format and should be used in the majority of cases. The element or attribute name containing each value is used as the column name for that value.
XMLTable
This format is for when the column name is separate from the data contained in that column. This is useful when the element or attribute containing the data has a generic name (like "Value") instead of being specific to each column.
Note: DataModel does not apply when using this XMLFormat.
Example:
<Report>
<Table>
<Row>
<Value label="Customer">Mark Rodgers</Value>
<Value label="SupportCost">89.28</Value>
<Value label="ContractValue">299.99</Value>
</Row>
<Row>
<Value label="Customer">Hank Howards</Value>
<Value label="SupportCost">225.63</Value>
<Value label="ContractValue">0.00</Value>
</Row>
</Table>
</Report>
The XPath property requires special syntax to identify the column and row paths. Each path is given with a prefix depending on the type of path. All of the following are required:
Each of these paths is separated by a semicolon, so the complete XPath for the above example is: row:/Report/Table/Row;name:/Report/Table/Row/Value@label;value:/Report/Table/Row/Value
The hosting region for your S3-like Web Services.
The hosting region for your S3-like Web Services.
| Value | Region |
| Commercial Cloud Regions | |
| ap-hyderabad-1 | India South (Hyderabad) |
| ap-melbourne-1 | Australia Southeast (Melbourne) |
| ap-mumbai-1 | India West (Mumbai) |
| ap-osaka-1 | Japan Central (Osaka) |
| ap-seoul-1 | South Korea Central (Seoul) |
| ap-sydney-1 | Australia East (Sydney) |
| ap-tokyo-1 | Japan East (Tokyo) |
| ca-montreal-1 | Canada Southeast (Montreal) |
| ca-toronto-1 | Canada Southeast (Toronto) |
| eu-amsterdam-1 | Netherlands Northwest (Amsterdam) |
| eu-frankfurt-1 | Germany Central (Frankfurt) |
| eu-zurich-1 | Switzerland North (Zurich) |
| me-jeddah-1 | Saudi Arabia West (Jeddah) |
| sa-saopaulo-1 | Brazil East (Sao Paulo) |
| uk-london-1 | UK South (London) |
| us-ashburn-1 (default) | US East (Ashburn, VA) |
| us-phoenix-1 | US West (Phoenix, AZ) |
| US Gov FedRAMP High Regions | |
| us-langley-1 | US Gov East (Ashburn, VA) |
| us-luke-1 | US Gov West (Phoenix, AZ) |
| US Gov DISA IL5 Regions | |
| us-gov-ashburn-1 | US DoD East (Ashburn, VA) |
| us-gov-chicago-1 | US DoD North (Chicago, IL) |
| us-gov-phoenix-1 | US DoD West (Phoenix, AZ) |
| Value | Region |
| eu-central-1 | Europe (Amsterdam) |
| us-east-1 (Default) | US East (Ashburn, VA) |
| us-east-2 | US East (Manassas, VA) |
| us-west-1 | US West (Hillsboro, OR) |
The Id of the project where your Google Cloud Storage instance resides.
The Id of the project where your Google Cloud Storage instance resides. You can find this value by going to Google Cloud Console and clicking the project name at the top left screen. The ProjectId is displayed on the Id column of the matching project.
The Oracle Cloud Object Storage namespace to use.
The Oracle Cloud Object Storage namespace to use. This setting must be set to the Oracle Cloud Object Storage namespace associated with the Oracle Cloud account before any requests can be made. Refer to the Understanding Object Storage Namespaces page of the Oracle Cloud documentation for instructions on how to find your account's Object Storage namespace.
The URL of a cloud storage service provider.
This connection property is used to specify:
If the domain for this option ends in -my (for example, https://bigcorp-my.sharepoint.com) then you may need to use the onedrive:// scheme instead of the sp:// or sprest:// scheme.
This setting specifies the threshold, in bytes, above which the provider will choose to perform a multipart upload rather than uploading everything in one request.
This setting specifies the threshold, in bytes, above which the Sync App will choose to perform a multipart upload rather than uploading everything in one request.
If true (default), buckets will be referenced in the request using the hosted-style request: http://yourbucket.s3.amazonaws.com/yourobject. If set to false, the bean will use the path-style request: http://s3.amazonaws.com/yourbucket/yourobject. Note that this property will be set to false, in case of an S3 based custom service when the CustomURL is specified.
If true (default), buckets will be referenced in the request using the hosted-style request: http://yourbucket.s3.amazonaws.com/yourobject. If set to false, the bean will use the path-style request: http://s3.amazonaws.com/yourbucket/yourobject. Note that this property will be set to false, in case of an S3 based custom service when the CustomURL is specified.
When this property is set to true, AWSLakeFormation service will be used to retrieve temporary credentials, which enforce access policies against the user based on the configured IAM role. The service can be used when authenticating through OKTA, ADFS, AzureAD, PingFederate, while providing a SAML assertion.
When this property is set to true, AWSLakeFormation service will be used to retrieve temporary credentials, which enforce access policies against the user based on the configured IAM role. The service can be used when authenticating through OKTA, ADFS, AzureAD, PingFederate, while providing a SAML assertion.
This section provides a complete list of the AWS Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| AWSAccessKey | Your AWS account access key. This value is accessible from your AWS security credentials page. |
| AWSSecretKey | Your AWS account secret key. This value is accessible from your AWS security credentials page. |
| AWSRoleARN | The Amazon Resource Name of the role to use when authenticating. |
| AWSPrincipalARN | The ARN of the SAML Identity provider in your AWS account. |
| AWSRegion | The hosting region for your Amazon Web Services. |
| AWSCredentialsFile | The path to the AWS Credentials File to be used for authentication. |
| AWSCredentialsFileProfile | The name of the profile to be used from the supplied AWSCredentialsFile. |
| AWSSessionToken | Your AWS session token. |
| AWSExternalId | A unique identifier that might be required when you assume a role in another account. |
| MFASerialNumber | The serial number of the MFA device if one is being used. |
| MFAToken | The temporary token available from your MFA device. |
| TemporaryTokenDuration | The amount of time (in seconds) a temporary token will last. |
| ServerSideEncryption | When activated, file uploads into Amazon S3 buckets will be server-side encrypted. |
| SSEContext | A BASE64-encoded UTF-8 string holding JSON which represents a string-string (key-value) map. |
| SSEEnableS3BucketKeys | Configuration to use an S3 Bucket Key at the object level when encrypting data with AWS KMS. Enabling this will reduce the cost of server-side encryption by lowering calls to AWS KMS. |
| SSEKey | A symmetric encryption KeyManagementService key, that is used to protect the data when using ServerSideEncryption. |
Your AWS account access key. This value is accessible from your AWS security credentials page.
Your AWS account access key. This value is accessible from your AWS security credentials page:
Your AWS account secret key. This value is accessible from your AWS security credentials page.
Your AWS account secret key. This value is accessible from your AWS security credentials page:
The Amazon Resource Name of the role to use when authenticating.
When authenticating outside of AWS, it is common to use a Role for authentication instead of your direct AWS account credentials. Entering the AWSRoleARN will cause the CData Sync App to perform a role based authentication instead of using the AWSAccessKey and AWSSecretKey directly. The AWSAccessKey and AWSSecretKey must still be specified to perform this authentication. You cannot use the credentials of an AWS root user when setting RoleARN. The AWSAccessKey and AWSSecretKey must be those of an IAM user.
The ARN of the SAML Identity provider in your AWS account.
The ARN of the SAML Identity provider in your AWS account.
The hosting region for your Amazon Web Services.
The hosting region for your Amazon Web Services. Available values are OHIO, NORTHERNVIRGINIA, NORTHERNCALIFORNIA, OREGON, CAPETOWN, HONGKONG, JAKARTA, MUMBAI, OSAKA, SEOUL, SINGAPORE, SYDNEY, TOKYO, CENTRAL, BEIJING, NINGXIA, FRANKFURT, IRELAND, LONDON, MILAN, PARIS, STOCKHOLM, ZURICH, BAHRAIN, UAE, SAOPAULO, GOVCLOUDEAST, and GOVCLOUDWEST.
The path to the AWS Credentials File to be used for authentication.
The path to the AWS Credentials File to be used for authentication. See https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-files.html for more information.
The name of the profile to be used from the supplied AWSCredentialsFile.
The name of the profile to be used from the supplied AWSCredentialsFile. See https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-files.html for more information.
Your AWS session token.
Your AWS session token. This value can be retrieved in different ways. See this link for more info.
A unique identifier that might be required when you assume a role in another account.
A unique identifier that might be required when you assume a role in another account.
The serial number of the MFA device if one is being used.
You can find the device for an IAM user by going to the AWS Management Console and viewing the user's security credentials. For virtual devices, this is actually an Amazon Resource Name (such as arn:aws:iam::123456789012:mfa/user).
The temporary token available from your MFA device.
If MFA is required, this value will be used along with the MFASerialNumber to retrieve temporary credentials to login. The temporary credentials available from AWS will only last up to 1 hour by default (see TemporaryTokenDuration). Once the time is up, the connection must be updated to specify a new MFA token so that new credentials may be obtained. %AWSpSecurityToken; %AWSpTemporaryTokenDuration;
The amount of time (in seconds) a temporary token will last.
Temporary tokens are used with both MFA and Role based authentication. Temporary tokens will eventually time out, at which time a new temporary token must be obtained. For situations where MFA is not used, this is not a big deal. The CData Sync App will internally request a new temporary token once the temporary token has expired.
However, for MFA required connection, a new MFAToken must be specified in the connection to retrieve a new temporary token. This is a more intrusive issue since it requires an update to the connection by the user. The maximum and minimum that can be specified will depend largely on the connection being used.
For Role based authentication, the minimum duration is 900 seconds (15 minutes) while the maximum if 3600 (1 hour). Even if MFA is used with role based authentication, 3600 is still the maximum.
For MFA authentication by itself (using an IAM User or root user), the minimum is 900 seconds (15 minutes), the maximum is 129600 (36 hours).
When activated, file uploads into Amazon S3 buckets will be server-side encrypted.
Server-side encryption is the encryption of data at its destination by the application or service that receives it. Amazon S3 encrypts your data at the object level as it writes it to disks in its data centers and decrypts it for you when you access it. Learn more: https://docs.aws.amazon.com/AmazonS3/latest/userguide/serv-side-encryption.html
A BASE64-encoded UTF-8 string holding JSON which represents a string-string (key-value) map.
Example of what the JSON may look decoded: {"aws:s3:arn": "arn:aws:s3:::_bucket_/_object_"}.
Configuration to use an S3 Bucket Key at the object level when encrypting data with AWS KMS. Enabling this will reduce the cost of server-side encryption by lowering calls to AWS KMS.
Configuration to use an S3 Bucket Key at the object level when encrypting data with AWS KMS. Enabling this will reduce the cost of server-side encryption by lowering calls to AWS KMS.
A symmetric encryption KeyManagementService key, that is used to protect the data when using ServerSideEncryption.
A symmetric encryption KeyManagementService key, that is used to protect the data when using ServerSideEncryption.
This section provides a complete list of the Azure Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| AzureStorageAccount | The name of your Azure storage account. |
| AzureAccessKey | The storage key associated with your Azure account. |
| AzureSharedAccessSignature | A shared access key signature that may be used for authentication. |
| AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
| AzureEnvironment | The Azure Environment to use when establishing a connection. |
The name of your Azure storage account.
The name of your Azure storage account.
The storage key associated with your Azure account.
The storage key associated with your XML account. You can retrieve it as follows:
The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.
Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.
The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.
The Azure Environment to use when establishing a connection.
In most cases, leaving the environment set to global will work. However, if your Azure Account has been added to a different environment, the AzureEnvironment may be used to specify which environment. The available values are GLOBAL, CHINA, USGOVT, USGOVTDOD.
This section provides a complete list of the SSO properties you can configure in the connection string for this provider.
| Property | Description |
| SSOLoginURL | The identity provider's login URL. |
| SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
| SSOExchangeUrl | The URL used for consuming the SAML response and exchanging it for service specific credentials. |
The identity provider's login URL.
The identity provider's login URL.
Additional properties required to connect to the identity provider in a semicolon-separated list.
Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.
SSO configuration is discussed further in .
The URL used for consuming the SAML response and exchanging it for service specific credentials.
The CData Sync App will use the URL specified here to consume a SAML response and exchange it for service specific credentials. The retrieved credentials are the final piece during the SSO connection that are used to communicate with XML.
This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.
| Property | Description |
| OAuthVersion | The version of OAuth being used. |
| OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
| OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
| Scope | Specify scope to obtain the initial access and refresh token. |
| OAuthGrantType | The grant type for the OAuth flow. |
| OAuthPasswordGrantMode | How to pass Client Id and Secret with OAuthGrantType is set to Password. |
| OAuthIncludeCallbackURL | Whether to include the callback URL in an access token request. |
| OAuthAuthorizationURL | The authorization URL for the OAuth service. |
| OAuthAccessTokenURL | The URL to retrieve the OAuth access token from. |
| OAuthRefreshTokenURL | The URL to refresh the OAuth token from. |
| OAuthRequestTokenURL | The URL the service provides to retrieve request tokens from. This is required in OAuth 1.0. |
| AuthToken | The authentication token used to request and obtain the OAuth Access Token. |
| AuthKey | The authentication secret used to request and obtain the OAuth Access Token. |
| OAuthParams | A comma-separated list of other parameters to submit in the request for the OAuth access token in the format paramname=value. |
The version of OAuth being used.
The version of OAuth being used. The following options are available: 1.0,2.0
The client Id assigned when you register your application with an OAuth authorization server.
As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
The client secret assigned when you register your application with an OAuth authorization server.
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.
Specify scope to obtain the initial access and refresh token.
Specify scope to obtain the initial access and refresh token.
Specify scope to obtain the initial access and refresh token.
The grant type for the OAuth flow.
The following options are available: CODE,CLIENT,PASSWORD
How to pass Client Id and Secret with OAuthGrantType is set to Password.
The OAuth RFC specifies two methods of passing the OAuthClientId and OAuthClientSecret when using the Password OAuthGrantType. The most commonly used is to pass them via post data to the service. However, some services may require that you pass them via the Authorize header as to be used in BASIC authorization. Change this property to Basic to submit the parameters as part of the Authorize header instead of the post data.
Whether to include the callback URL in an access token request.
This defaults to true since standards-compliant OAuth services will ignore the redirect_uri parameter for grant types like CLIENT or PASSWORD that do not require it.
This option should only be enabled for OAuth services that report errors when redirect_uri is included.
The authorization URL for the OAuth service.
The authorization URL for the OAuth service. At this URL, the user logs into the server and grants permissions to the application. In OAuth 1.0, if permissions are granted, the request token is authorized.
The URL to retrieve the OAuth access token from.
The URL to retrieve the OAuth access token from. In OAuth 1.0, the authorized request token is exchanged for the access token at this URL.
The URL to refresh the OAuth token from.
The URL to refresh the OAuth token from. In OAuth 2.0, this URL is where the refresh token is exchanged for a new access token when the old access token expires.
The URL the service provides to retrieve request tokens from. This is required in OAuth 1.0.
The URL the service provides to retrieve request tokens from. This is required in OAuth 1.0. In OAuth 1.0, this is the URL where the app makes a request for the request token.
The authentication token used to request and obtain the OAuth Access Token.
This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.
It can be supplied alongside the AuthKey in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.
The authentication secret used to request and obtain the OAuth Access Token.
This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.
It can be supplied alongside the AuthToken in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.
A comma-separated list of other parameters to submit in the request for the OAuth access token in the format paramname=value.
A comma-separated list of other parameters to submit in the request for the OAuth access token in the format paramname=value.
This section provides a complete list of the JWT OAuth properties you can configure in the connection string for this provider.
| Property | Description |
| OAuthJWTCert | The JWT Certificate store. |
| OAuthJWTCertType | The type of key store containing the JWT Certificate. |
| OAuthJWTCertPassword | The password for the OAuth JWT certificate. |
| OAuthJWTCertSubject | The subject of the OAuth JWT certificate. |
| OAuthJWTAudience | A space-separated list of entities that may use the JWT. |
| OAuthJWTValidityTime | How long the JWT should remain valid, in seconds. |
The JWT Certificate store.
The name of the certificate store for the client certificate.
The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert. If the store is password protected, specify the password in OAuthJWTCertPassword.
OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject field in order to specify client certificates. If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
| MY | A certificate store holding personal certificates with their associated private keys. |
| CA | Certifying authority certificates. |
| ROOT | Root certificates. |
| SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).
The type of key store containing the JWT Certificate.
This property can take one of the following values:
| USER | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java. |
| MACHINE | For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java. |
| PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
| PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
| JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java. |
| JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java. |
| PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
| PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
| PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
| PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
| SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
| SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
| P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
| PPKFILE | The certificate store is the name of a file that contains a PPK (PuTTY Private Key). |
| XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
| XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
| GOOGLEJSON | The certificate store is the name of a JSON file containing the service account information. Only valid when connecting to a Google service. |
| GOOGLEJSONBLOB | The certificate store is a string that contains the service account JSON. Only valid when connecting to a Google service. |
The password for the OAuth JWT certificate.
If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.
This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys are not encrypted.
The subject of the OAuth JWT certificate.
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property.
If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, [email protected]". Common fields and their meanings are displayed below.
| Field | Meaning |
| CN | Common Name. This is commonly a host name like www.server.com. |
| O | Organization |
| OU | Organizational Unit |
| L | Locality |
| S | State |
| C | Country |
| E | Email Address |
If a field value contains a comma it must be quoted.
A space-separated list of entities that may use the JWT.
This corresponds to the aud field in the JWT. The entries in this list are typically URLs, but the exact values depend on the API being used.
How long the JWT should remain valid, in seconds.
This is used to calculate the exp field in the JWT. By default this is set to 3600 which means the JWT is valid for 1 hour after it is generated. Some APIs may require lower values than this.
This section provides a complete list of the Kerberos properties you can configure in the connection string for this provider.
| Property | Description |
| KerberosKDC | The Kerberos Key Distribution Center (KDC) service used to authenticate the user. |
| KerberosRealm | The Kerberos Realm used to authenticate the user. |
| KerberosSPN | The service principal name (SPN) for the Kerberos Domain Controller. |
| KerberosKeytabFile | The Keytab file containing your pairs of Kerberos principals and encrypted keys. |
| KerberosServiceRealm | The Kerberos realm of the service. |
| KerberosServiceKDC | The Kerberos KDC of the service. |
| KerberosTicketCache | The full file path to an MIT Kerberos credential cache file. |
The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
The Kerberos properties are used when using SPNEGO or Windows Authentication. The Sync App will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.
If Kerberos KDC is not specified, the Sync App will attempt to detect these properties automatically from the following locations:
The Kerberos Realm used to authenticate the user.
The Kerberos properties are used when using SPNEGO or Windows Authentication. The Kerberos Realm is used to authenticate the user with the Kerberos Key Distribution Service (KDC). The Kerberos Realm can be configured by an administrator to be any string, but conventionally it is based on the domain name.
If Kerberos Realm is not specified, the Sync App will attempt to detect these properties automatically from the following locations:
The service principal name (SPN) for the Kerberos Domain Controller.
If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN.
The Keytab file containing your pairs of Kerberos principals and encrypted keys.
The Keytab file containing your pairs of Kerberos principals and encrypted keys.
The Kerberos realm of the service.
The KerberosServiceRealm is the specify the service Kerberos realm when using cross-realm Kerberos authentication.
In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.
This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).
The Kerberos KDC of the service.
The KerberosServiceKDC is used to specify the service Kerberos KDC when using cross-realm Kerberos authentication.
In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.
This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).
The full file path to an MIT Kerberos credential cache file.
This property can be set if you wish to use a credential cache file that was created using the MIT Kerberos Ticket Manager or kinit command.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
| Property | Description |
| SSLClientCert | The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). |
| 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. |
| SSLMode | The authentication mechanism to be used when connecting to the FTP or FTPS server. |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).
The name of the certificate store for the client certificate.
The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert. If the store is password protected, specify the password in SSLClientCertPassword.
SSLClientCert is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
| MY | A certificate store holding personal certificates with their associated private keys. |
| CA | Certifying authority certificates. |
| ROOT | Root certificates. |
| SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).
The type of key store containing the TLS/SSL client certificate.
This property can take one of the following values:
| USER - default | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note that this store type is not available in Java. |
| MACHINE | For Windows, this specifies that the certificate store is a machine store. Note that this store type is not available in Java. |
| PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
| PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
| JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note that this store type is only available in Java. |
| JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in JKS format. Note that this store type is only available in Java. |
| PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
| PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
| PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
| PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
| SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
| SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
| P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
| PPKFILE | The certificate store is the name of a file that contains a PuTTY Private Key (PPK). |
| XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
| XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
The password for the TLS/SSL client certificate.
If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.
The subject of the TLS/SSL client certificate.
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, [email protected]". The common fields and their meanings are shown below.
| Field | Meaning |
| CN | Common Name. This is commonly a host name like www.server.com. |
| O | Organization |
| OU | Organizational Unit |
| L | Locality |
| S | State |
| C | Country |
| E | Email Address |
If a field value contains a comma, it must be quoted.
The authentication mechanism to be used when connecting to the FTP or FTPS server.
If SSLMode is set to NONE, default plaintext authentication is used to log in to the server. If SSLMode is set to IMPLICIT, the SSL negotiation will start immediately after the connection is established. If SSLMode is set to EXPLICIT, the Sync App will first connect in plaintext, and then explicitly start SSL negotiation through a protocol command such as STARTTLS. If SSLMode is set to AUTOMATIC, if the remote port is set to the standard plaintext port of the protocol (where applicable), the component will behave the same as if SSLMode is set to EXPLICIT. In all other cases, SSL negotiation will be IMPLICIT.
The certificate to be accepted from the server when connecting using TLS/SSL.
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
| Description | Example |
| A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
| A path to a local file containing the certificate | C:\cert.cer |
| The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
| The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
| The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of the SSH properties you can configure in the connection string for this provider.
| Property | Description |
| SSHAuthMode | The authentication method used when establishing an SSH Tunnel to the service. |
| SSHClientCert | A certificate to be used for authenticating the SSHUser. |
| SSHClientCertPassword | The password of the SSHClientCert key if it has one. |
| SSHClientCertSubject | The subject of the SSH client certificate. |
| SSHClientCertType | The type of SSHClientCert private key. |
| SSHUser | The SSH user. |
| SSHPassword | The SSH password. |
The authentication method used when establishing an SSH Tunnel to the service.
A certificate to be used for authenticating the SSHUser.
SSHClientCert must contain a valid private key in order to use public key authentication. A public key is optional, if one is not included then the Sync App generates it from the private key. The Sync App sends the public key to the server and the connection is allowed if the user has authorized the public key.
The SSHClientCertType field specifies the type of the key store specified by SSHClientCert. If the store is password protected, specify the password in SSHClientCertPassword.
Some types of key stores are containers which may include multiple keys. By default the Sync App will select the first key in the store, but you can specify a specific key using SSHClientCertSubject.
The password of the SSHClientCert key if it has one.
This property is only used when authenticating to SFTP servers with SSHAuthMode set to PublicKey and SSHClientCert set to a private key.
The subject of the SSH client certificate.
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property.
If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, [email protected]". Common fields and their meanings are displayed below.
| Field | Meaning |
| CN | Common Name. This is commonly a host name like www.server.com. |
| O | Organization |
| OU | Organizational Unit |
| L | Locality |
| S | State |
| C | Country |
| E | Email Address |
If a field value contains a comma it must be quoted.
The type of SSHClientCert private key.
This property can take one of the following values:
| Types | Description | Allowed Blob Values |
| MACHINE/USER | Blob values are not supported. | |
| JKSFILE/JKSBLOB | base64-only | |
| PFXFILE/PFXBLOB | A PKCS12-format (.pfx) file. Must contain both a certificate and a private key. | base64-only |
| PEMKEY_FILE/PEMKEY_BLOB | A PEM-format file. Must contain an RSA, DSA, or OPENSSH private key. Can optionally contain a certificate matching the private key. | base64 or plain text. Newlines may be replaced with spaces when providing the blob as text. |
| PPKFILE/PPKBLOB | A PuTTY-format private key created using the puttygen tool. | base64-only |
| XMLFILE/XMLBLOB | An XML key in the format generated by the .NET RSA class: RSA.ToXmlString(true). | base64 or plain text. |
The SSH user.
The SSH user.
The SSH password.
The SSH password.
This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
The protocol used by a proxy-based firewall.
This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.
| Type | Default Port | Description |
| TUNNEL | 80 | When this is set, the Sync App opens a connection to XML and traffic flows back and forth through the proxy. |
| SOCKS4 | 1080 | When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted. |
| SOCKS5 | 1080 | When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes. |
To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.
The name or IP address of a proxy-based firewall.
This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.
Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.
The TCP port for a proxy-based firewall.
This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.
The user name to use to authenticate with a proxy-based firewall.
The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.
A password used to authenticate to a proxy-based firewall.
This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.
This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
This indicates whether to use the system proxy settings or not.
This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.
The hostname or IP address of a proxy to route HTTP traffic through.
The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.
If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.
By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.
The TCP port the ProxyServer proxy is running on.
The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.
The authentication type to use to authenticate to the ProxyServer proxy.
This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.
Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
The authentication type can be one of the following:
If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.
A user name to be used to authenticate to the ProxyServer proxy.
The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.
You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:
user@domain domain\user
A password to be used to authenticate to the ProxyServer proxy.
This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.
If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.
If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.
For SOCKS 5 authentication or tunneling, see FirewallType.
By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.
The SSL type to use when connecting to the ProxyServer proxy.
This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:
| AUTO | Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option. |
| ALWAYS | The connection is always SSL enabled. |
| NEVER | The connection is not SSL enabled. |
| TUNNEL | The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy. |
A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .
The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.
Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| LogModules | Core modules to be included in the log file. |
Core modules to be included in the log file.
Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.
See the Logging page for an overview.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
| PushAttributes | Set PushAttributes to true to push any identified attributes as columns. |
| FlattenArrays | By default, nested arrays are returned as strings of XML. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. Set FlattenArrays to the number of elements you want to return from nested arrays. |
| FlattenObjects | Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of XML. |
| QualifyColumns | Controls whether the provider will use relative column names. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\\CData\\XML Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:
| Platform | %APPDATA% |
| Windows | The value of the APPDATA environment variable |
| Linux | ~/.config |
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Set PushAttributes to true to push any identified attributes as columns.
When set to true (default), the Sync App will push attributes as individual columns (where applicable). Attributes will also be included in any aggregates generated.
When set to false, attributes will not be pushed as columns or in aggregates (e.g., the Sync App strips them from the output).
By default, nested arrays are returned as strings of XML. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. Set FlattenArrays to the number of elements you want to return from nested arrays.
In XML, arrays are identified as repeating value elements. By default, nested arrays are returned as strings of XML. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. This is only recommended for arrays that are expected to be short.
Set FlattenArrays to the number of elements you want to return from nested arrays. The specified elements are returned as columns. The zero-based index is concatenated to the column name. Other elements are ignored.
For example, you can return an arbitrary number of elements from an array of strings:
<languages>FLOW-MATIC</languages> <languages>LISP</languages> <languages>COBOL</languages>When FlattenArrays is set to 1, the preceding array is flattened into the following table:
| Column Name | Column Value |
| languages.0 | FLOW-MATIC |
Setting FlattenArrays to -1 will flatten all the elements of nested arrays.
Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of XML.
Set FlattenObjects to true to flatten object properties into columns of their own.
For example, you can use this property to flatten the nested objects below at connection time:
<grades> <grade>A</grade> <score>2</score> </grades> <grades> <grade>A</grade> <score>6</score> </grades> <grades> <grade>A</grade> <score>10</score> </grades> <grades> <grade>A</grade> <score>9</score> </grades> <grades> <grade>B</grade> <score>14</score> </grades>To generate the column name, the Sync App concatenates the property name onto the object name with a dot. When FlattenObjects is set to true and FlattenArrays is set to 1, the preceding array is flattened into the following table:
| Column Name | Column Value |
| grades.0.grade | A |
| grades.0.score | 2 |
Controls whether the provider will use relative column names.
By default the Sync App will only qualify a column name as much as is necessary to make it unique.
For example, in this document the Sync App will produce the columns id (referring to the company id) and employee.id.
<company>
<id>Smith Holdings</id>
<employees>
<employee>
<id>George Smith</id>
</employee>
<employee>
<id>Mike Johnson</id>
</employee>
</employees>
</company>
When this option is set to Parent, the Sync App uses a similar procedure to the one above. However, the Sync App will always qualify columns by one level so that their table name is included, even if the column name is unique. For example, the above document would generate the columns company.id and employee.id because both are unique when including their parent.
When this option is set to Full, the Sync App will qualify all column names with their full XPath. This generates longer column names but ensures that it is clear where each column name comes from within the document. For the example above, the Sync App would generate the columns company.id and company.employees.employee.id.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| Property | Description |
| BackwardsCompatibilityMode | Set BackwardsCompatibilityMode to true to use the XML functionality and features available in the 2017 version. |
| Charset | Specifies the session character set for encoding and decoding character data transferred to and from the XML file. The default value is UTF-8. |
| ClientCulture | This property can be used to specify the format of data (e.g., currency values) that is accepted by the client application. This property can be used when the client application does not support the machine's culture settings. For example, Microsoft Access requires 'en-US'. |
| Culture | This setting can be used to specify culture settings that determine how the provider interprets certain data types that are passed into the provider. For example, setting Culture='de-DE' will output German formats even on an American machine. |
| CustomHeaders | Other headers as determined by the user (optional). |
| CustomUrlParams | The custom query string to be included in the request. |
| ExcludeFiles | Comma-separated list of file extensions to exclude from the set of the files modeled as tables. |
| FlattenRowLimit | The maximum number of rows that can result from a single flattened element. |
| FolderId | The ID of a folder in Google Drive. If set, the resource location specified by the URI is relative to the Folder ID for all operations. |
| GenerateSchemaFiles | Indicates the user preference as to when schemas should be generated and saved. |
| IncludeDropboxTeamResources | Indicates if you want to include Dropbox team files and folders. |
| IncludeFiles | Comma-separated list of file extensions to include into the set of the files modeled as tables. |
| IncludeItemsFromAllDrives | Whether Google Drive shared drive items should be included in results. If not present or set to false, then shared drive items are not returned. |
| MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
| MetadataDiscoveryURI | Used when aggregating multiple files into one table, this property specifies a specific file to read to determined the aggregated table schema. |
| Other | These hidden properties are used only in specific use cases. |
| Pagesize | The maximum number of results to return per page from XML. |
| PathSeparator | Determines the character which will be used to replace the file separator. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| RowScanDepth | The number of rows to scan when dynamically determining columns for the table. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| TypeDetectionScheme | Determines how to determine the data types of columns. |
| URISeparator | A delimiter used to separate different values in the URI property. |
| UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Set BackwardsCompatibilityMode to true to use the XML functionality and features available in the 2017 version.
When set to true, the Sync App will function the same as the 2017 version and will continue to be supported/improved.
When set to false (default), the new flattening features will be available. This includes DataModel, FlattenArrays, and FlattenObjects as well as the dynamic flattening of tables and columns via a SQL query.
Specifies the session character set for encoding and decoding character data transferred to and from the XML file. The default value is UTF-8.
Specifies the session character set for encoding and decoding character data transferred to and from the XML file. The default value is UTF-8.
This property can be used to specify the format of data (e.g., currency values) that is accepted by the client application. This property can be used when the client application does not support the machine's culture settings. For example, Microsoft Access requires 'en-US'.
This option affects the format of Sync App output. To specify the format that defines how input should be interpreted, use the Culture option. By default the Sync App uses the current locale settings of the machine to interpret input and format output.
This setting can be used to specify culture settings that determine how the provider interprets certain data types that are passed into the provider. For example, setting Culture='de-DE' will output German formats even on an American machine.
This property affects the Sync App input. To interpret values in a different cultural format, use the Client Culture property. By default the Sync App uses the current locale settings of the machine to interpret input and format output.
Other headers as determined by the user (optional).
This property can be set to a string of headers to be appended to the HTTP request headers created from other properties, like ContentType, From, and so on.
The headers must be of the format "header: value" as described in the HTTP specifications. Header lines should be separated by the carriage return and line feed (CRLF) characters.
Use this property with caution. If this property contains invalid headers, HTTP requests may fail.
This property is useful for fine-tuning the functionality of the Sync App to integrate with specialized or nonstandard APIs.
The custom query string to be included in the request.
The CustomUrlParams allow you to specify custom query string parameters that are included with the HTTP request. The parameters must be encoded as a query string in the form field1=value1&field2=value2&field3=value3. The values in the query string must be URL encoded.
Comma-separated list of file extensions to exclude from the set of the files modeled as tables.
It is also possible to specify datetime filters. We currently support CreatedDate and ModifiedDate. All extension filters are evaluated in disjunction (using OR operator), and then the resulting filter is evaluated in conjunction (using AND operator) with the datetime filters.
Examples:
ExcludeFiles="TXT,CreatedDate<='2020-11-26T07:39:34-05:00'"
ExcludeFiles="TXT,ModifiedDate<=DATETIMEFROMPARTS(2020, 11, 26, 7, 40, 50, 000)"
ExcludeFiles="ModifiedDate>=DATETIMEFROMPARTS(2020, 11, 26, 7, 40, 49, 000),ModifiedDate<=CURRENT_TIMESTAMP()"
The maximum number of rows that can result from a single flattened element.
In some cases the FlattenedDocuments DataModel may try to output more rows than intended, either because of the shape of the data or the table identification options. If the number of rows to be generated is large enough this can lead to memory issues as the Sync App has to store all the generated rows in memory before returning them.
To avoid this the Sync App will check that the number of rows to be output does not exceed this limit (250000 by default). If the Sync App would output more rows than this for a single flattened element it will instead fail the query and report an error.
The ID of a folder in Google Drive. If set, the resource location specified by the URI is relative to the Folder ID for all operations.
The ID of a folder in Google Drive. If set, the resource location specified by the URI is relative to the Folder ID for all operations.
Indicates the user preference as to when schemas should be generated and saved.
This property outputs schemas to .rsd files in the path specified by Location.
Available settings are the following:
When you set GenerateSchemaFiles to OnUse, the Sync App generates schemas as you execute SELECT queries. Schemas are generated for each table referenced in the query.
When you set GenerateSchemaFiles to OnCreate, schemas are only generated when a CREATE TABLE query is executed.
Another way to use this property is to obtain schemas for every table in your database when you connect. To do so, set GenerateSchemaFiles to OnStart and connect.
Indicates if you want to include Dropbox team files and folders.
In order to access Dropbox team folders and files, please set this connection property to True.
Comma-separated list of file extensions to include into the set of the files modeled as tables.
Comma-separated list of file extensions to include into the set of the files modeled as tables. For example, IncludeFiles=XML,TXT. The default is XML,TXT.
The following archive types are also supported: ZIP, TAR, and GZ. Files of these types are modeled as an aggregated table.
A '*' value can be specified to include all files. A 'NOEXT' value can be specified to include files without an extension.
It is also possible to specify datetime filters. We currently support CreatedDate and ModifiedDate. All extension filters are evaluated in disjunction (using OR operator), and then the resulting filter is evaluated in conjunction (using AND operator) with the datetime filters.
Examples:
IncludeFiles="TXT,CreatedDate<='2020-11-26T07:39:34-05:00'"
IncludeFiles="TXT,ModifiedDate<=DATETIMEFROMPARTS(2020, 11, 26, 7, 40, 50, 000)"
IncludeFiles="ModifiedDate>=DATETIMEFROMPARTS(2020, 11, 26, 7, 40, 49, 000),ModifiedDate<=CURRENT_TIMESTAMP()"
Whether Google Drive shared drive items should be included in results. If not present or set to false, then shared drive items are not returned.
If this property is set to 'True', files will be retrieved from all drives, including shared drives. The file retrieval can be limited a specific shared drive or a specific folder in that shared drive by setting the start of the URI to the path of the shared drive and optionally any folder within, for example: 'gdrive://SharedDriveA/FolderA/...'. Additionally, the FolderId property can be used to limit the search to an exact subdirectory.
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Used when aggregating multiple files into one table, this property specifies a specific file to read to determined the aggregated table schema.
Used when aggregating multiple files into one table, this property specifies a specific file to read to determined the aggregated table schema.
These hidden properties are used only in specific use cases.
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
| DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
| ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
| RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
The maximum number of results to return per page from XML.
The Pagesize property affects the maximum number of results to return per page from XML. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.
Determines the character which will be used to replace the file separator.
Determines the character which will be used to replace the file separator. If there is a XML file located in "Test/Files/Test.xml" and if this property is set to "_", then the table name for this file would be "Test_Files_Test.xml".
This property indicates whether or not to include pseudo columns as columns to the table.
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
The number of rows to scan when dynamically determining columns for the table.
The number of rows (objects) to scan when dynamically determining columns for the table -- the row scan follows nested objects, counting 1 object array as 1 row. Columns are dynamically determined when a schema (RSD) file is not available for the table, such as when using GenerateSchemaFiles.
Higher values will result in a longer request, but will be more accurate.
Setting this value to 0 (zero) will parse the entire XML document.
The value in seconds until the timeout error is thrown, canceling the operation.
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the Sync App throws an exception.
Determines how to determine the data types of columns.
| None | Setting TypeDetectionScheme to None will return all columns as the string type. |
| RowScan | Setting TypeDetectionScheme to RowScan will scan rows to heuristically determine the data type. The RowScanDepth determines the number of rows to be scanned. |
A delimiter used to separate different values in the URI property.
By default the delimiter is a comma which means that multiple URIs can be
joined together like this:
URI=c:/data/data1.xml,c:/data/data2.xml,c:/data/data3.xml
A filepath pointing to the JSON configuration file containing your custom views.
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.
This User Defined View configuration file is formatted as follows:
For example:
{
"MyView": {
"query": "SELECT * FROM NorthwindOData WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.jsonNote that the specified path is not embedded in quotation marks.