Skip to Content

Xero Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8852 of the connector.

Get Started

Xero Version Support

The Xero connector models Xero Accounting, Australian Payroll, Files, Assets and Projects objects as a relational database.

Establish a Connection

Connect to Xero

The connector supports using PKCE applications and OAuth applications. Both PKCE and OAuth connections to Xero are authenticated using OAuth. When the connector starts, it opens a browser and requests your login information. The connector uses the credentials you provide to access your Xero data. These credentials are saved and automatically refreshed as needed.

To enable this authentication from all OAuth flows, you must set AuthScheme to OAuth, and you must create a custom OAuth application.

The following subsections describe how to authenticate to Xero from three common authentication flows:

  • Desktop: a connection to a server on the user's local machine, frequently used for testing and prototyping.
  • Web: access to data via a shared website.
  • Headless Server: a dedicated computer that provides services to other computers and their users, which is configured to operate without a monitor and keyboard.

For information about how to create a custom OAuth application, see Creating a Custom Authentication Application.

For a complete list of connection string properties available in Xero, see Connection.

Desktop Applications

To authenticate with the credentials for a custom OAuth application, you must get and refresh the OAuth access token. After you do that, you are ready to connect.

Get and refresh the OAuth access token:

  • InitiateOAuth = GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
  • OAuthClientId = the client ID assigned when you registered your application.
  • OAuthClientSecret = the client secret that was assigned when you registered your application.
  • CallbackURL = the redirect URI that was defined when you registered your application.

When you connect, the connector opens Xero's OAuth endpoint in your default browser. Log in and grant permissions to the application.

After you grant permissions to the application, the connector then completes the OAuth process:

  1. The connector obtains an access token from Xero and uses it to request data.
  2. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the connector refreshes it automatically.

Automatic refresh of the OAuth access token:

To have the connector automatically refresh the OAuth access token, do the following:

  1. Before connecting to data for the first time, set the following connection parameters:
    • InitiateOAuth = REFRESH.
    • OAuthClientId = the client ID in your application settings.
    • OAuthClientSecret = the client secret in your application settings.
    • OAuthAccessToken = the access token returned by GetOAuthAccessToken.
    • OAuthSettingsLocation = the path where you want the connector to save the OAuth values, which persist across connections.
  2. On subsequent data connections, set the following:
    • InitiateOAuth
    • OAuthSettingsLocation

Manual refresh of the OAuth access token:

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

  1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the GetOAuthAccessToken stored procedure.
  2. Set the following connection properties:
    • OAuthClientId = the Client ID in your application settings.
    • OAuthClientSecret = the Client Secret in your application settings.
  3. Call GetOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
  4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by GetOAuthAccessToken. This opens a new connection.

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

Connect to a Xero API

The connector supports several Xero APIs. To connect to them, set the appropriate connection property:

  • For the Accounting API, set Schema to ACCOUNTING.
  • For the Australian Payroll API, set Schema to PAYROLLAUS.
  • For the Files API, set Schema to FILES.
  • For the Fixed Assets API set Schema to ASSETS.
  • For the Projects API, set Schema to PROJECTS.

Specifying Permitted Organization Access

For any of the above APIs, we also recommend setting the Tenant property, which enables you to authorize the connector to access multiple organizations. Tenant is normally set to identify one or more organizations that the connector can access. Legal entries for Tenant can be either the name of the organization or its ID. (Since multiple organizations can have the same name, we recommend using the ID to set the Tenant property.)

Create a Custom Authentication Application

Create a Custom Authentication Application

This section describes how to create custom authentication applications for use with PKCE, OAuth, and custom connections. Developers writing client side applications can use PKCE to test applications locally, because PKCE does not require a client secret.

OAuth

To create and register an OAuth application for desktop, web, or headless servers, and obtain the OAuthClientId and OAuthClientSecret:

  1. Log in to the Xero developer portal.
  2. Navigate to My Apps > Add Application.
  3. Choose the application type Auth Code.
  4. Enter a name for your application and the URL of your company. This information is displayed to users when they connect.
  5. Add a Redirect URI. The same URI should be set in CallbackURL.
  6. Click Save. Xero displays the OAuth Client ID of the new application.
  7. Create a new OAuth Client Secret.
  8. Set OAuthClientId and OAuthClientSecret to the credentials you just obtained.

Custom Connections

Xero also supports server-to-server OAuth integrations using Custom Connections. Licenses for these connections must be purchased from Xero before they can be used in production organizations. They can also be linked to demo organizations for free.

To register a custom connection application in Xero:

  1. Log in to the Xero developer portal.

  2. Navigate to My Apps > Add Application.

  3. Specify an application type of Custom Connection.

  4. Select a list of scopes to which this application should have access. To avoid permission conflicts when connecting the connector, we recommend

    that you select all the available scopes. You can also select scopes individually, but if you do this you must update the Scope connection property to match.

  5. If desired, choose a user to authorize the connection. If you do this, the application can only be linked to an organization that this user can access.

  6. Click Save and Connect. Xero sends an email to the user you selected, which contains instructions on how to link the application to an organization.

  7. Wait for the user to link the application.

  8. Return to the developer portal and open the application settings.

  9. Click the Configuration tab and scroll to the Client Id. The CLient ID is used to set the OAuthClientId application property.

  10. Click Generate a Secret.

  11. Copy the secret you just generated, and apply it to the OAuthClientSecret property.

Xero API Limits

Xero API Limits

The Xero API has usage limitations that may be encountered while using the Xero connector. Note that all of these apply on a per-application and per-organization basis. An application may exceed these limits if it is querying multiple organizations as long as it obeys the limits within each organization.

Concurrent Limit

At most 5 API calls from the application may be active against the same organization at once. Typically this limit is not an issue because the connector will automatically retry the current request if this occurs.

Per-Minute Limit

At most 60 API calls per minute may be made from the application to the same organization. This limit is also not an issue as Xero reports a recommended delay time that the connector will use to avoid hitting the limit again.

Uncertified Connectors Limit

Uncertified OAuth applications are limited to 25 connections. There is no limit on connections for certified apps.

Per-Day Limit

At most 5000 API calls per day may be made from the application to the same organization. Hitting this limit is rare but can happen with certain tables (such as history tables and some reports) or certain settings (such as AutoExpandDetails) are used, as they require the connector to make a single API call for each invoice/contact/etc. that is read.

If this limit is hit frequently then the first step should be avoiding the tables or settings that trigger it. It is also possible to convert the queries into versions which read fewer rows at one time. This is usually done by replacing simple selects with subqueries that pick out small groups of rows, and then using multiple versions of these queries at different times:

/* Retreives history for about 1/16 of the invoices */
SELECT * FROM HistoryInvoices WHERE InvoiceId IN (
  SELECT InvoiceId FROM Invoices WHERE InvoiceId LIKE '0%'
)

The RetryDailyLimit option is also available but its use is strongly discouraged, as in the worst cases the delays that Xero suggests can span several hours during which the connector will be unavailable. It is typically better to restructure how you request data to fit within the limits than to ignore the limits and execute queries that take multiple hours to run.

Important Notes

Configuration Files and Their Paths

  • All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Xero connector.

User Defined Views

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

SSL Configuration

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

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

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

See Query Processing for more information.

User Defined Views

The Xero connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

  • Create a JSON-formatted configuration file defining the views you want.
  • DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector 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 connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Contacts 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.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector 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.

Data Model

The Xero connector models five Xero APIs as relational databases: the Accounting, Australian Payroll, Files, Fixed Assets and Projects APIs. Set the Schema property and use the information in each section to select the proper API based on your project needs.

Using the Accounting API

The Accounting API contains the company's financial information, such as contacts, invoices and reporting. It also contains history tables which records changes to most kinds of entities.

INSERTs to specific tables which model line items in the Accounting API have batch support enabled. The recommended setting of BatchSize for maximum performance is 50.

See Accounting Data Model for the available entities in the Accounting API.

Using the Australian Payroll API

The Australian Payroll API contains information on the company's employees, such as timesheets, leave and tax declarations.

INSERTs to specific tables which model line items in the Australian Payroll API have batch support enabled. The recommended setting of BatchSize for maximum performance is 50.

See Australian Payroll Data Model for the available entities in the Australian Payroll API.

Using the Files API

The Files API contains information on the files and attachments managed by Xero. It can be used to upload and download files, and attach them to accounting entities.

See Files Data Model for the available entities in the Files API.

Using the Fixed Assets API

The Fixed Assets API contains information on the company's fixed assets, such as registration, depreciation and resale.

See Fixed Assets Data Model for the available entities in the Fixed Assets API.

Using the Projects API

The Projects API contains information on the company's projects, such as billing and time tracking.

See Projects Data Model for the available entities in the Projects API.

Accounting Data Model

The Xero connector models the Xero Accounting API as relational tables, views, and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information

in the data source.

Tables

The connector models the data in Xero as a list of tables in a relational database that can be queried using standard SQL statements.

Xero Connector Tables
Name Description
Accounts Query, insert, update and delete accounts for a Xero organisation.
BankTransactions Query, insert and update bank transactions for a Xero organisation.
BankTransfers Usage information for the operation BankTransfers.rsd.
BatchPayments Query and insert BatchPayments for a Xero organisation.
ContactGroups Query, insert, update and delete contact groups for a Xero organisation.
Contacts Query, insert and update contacts for a Xero organisation.
CreditNotes Query, insert and update credit notes for a Xero organisation.
Employees Query, insert and update employees for a Xero organisation.
ExpenseClaims Query, insert and update expense claims for a Xero organisation.
HistoryBankTransactions Query and insert BankTransaction history and notes for a Xero organization.
HistoryBatchPayments Query and insert BatchPayment history and notes for a Xero organization.
HistoryContacts Query and insert Contact history and notes for a Xero organization.
HistoryCreditNotes Query and insert CreditNote history and notes for a Xero organization.
HistoryInvoices Query and insert Invoice history and notes for a Xero organization.
HistoryPurchaseOrders Query and insert PurchaseOrder history and notes for a Xero organization.
Invoices Query, insert and update Invoices for a Xero organisation.
Items Query, insert, update and delete items for a Xero organisation.
LinkedTransactions Query, insert, update and delete LinkedTransactions for a Xero organisation.
ManualJournals Query, insert and update manual journals for a Xero organisation.
Payments Query, insert and delete payments for a Xero organisation.
PurchaseOrders Query, insert and update purchase orders for a Xero organisation.
Quotes Query quotes for a Xero organization
Receipts Query, insert and update receipts for a Xero organisation.
TaxRates Usage information for the operation TaxRates.rsd.
Accounts

Query, insert, update and delete accounts for a Xero organisation.

Table Specific Information

The Accounts table allows you to SELECT and INSERT accounts for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. The following fields are required for inserts: Code, Name, and Type.

INSERT INTO Accounts (Name, Code, Type) VALUES ('John Doe', '2000', 'BANK')
Columns
Name Type ReadOnly Description
AccountId [KEY] String True The unique, Xero-assigned identifier of the account.
Code String False Customer-defined alphanumeric account code. For example, 200 or SALES.
Name String False Name of the account.
Type String False The account type. Valid values are BANK, CURRENT, CURRLIAB, DEPRECIATN, DIRECTCOSTS, EQUITY, EXPENSE, FIXED, LIABILITY, NONCURRENT, OTHERINCOME, OVERHEADS, PREPAYMENT, REVENUE, SALES, TERMLIAB, PAYGLIABILITY, SUPERANNUATIONEXPENSE, SUPERANNUATIONLIABILITY, and WAGESEXPENSE.
Description String False Description of the account. All accounts except bank accounts return this element.
TaxType String False The tax type. For example, INPUT or NONE.
EnablePaymentsToAccount Boolean False Describes whether the account can have payments applied to it.
ShowInExpenseClaims Boolean False Describes whether the account code is available for use with expense claims.
Class String True The account class type. For example, ASSET or EQUITY.
Status String False The account status code.
SystemAccount String True Returned only for system accounts, such as DEBTORS, CREDITORS, etc.
BankAccountNumber String False The bank account number associated with the account. BANK account types only.
CurrencyCode String False The currency code associated with the account. BANK account types only.
ReportingCode String True The reporting code. Only available for NZ organisations.
ReportingCodeName String True The reporting code name. Only available for NZ organisations.
UpdatedDateUTC Datetime True Timestamp of the last change to the user record.
HasAttachments Boolean True This field indicates whether the invoice has an attachment.
BankAccountType String False Valid types are BANK, CREDITCARD, and PAYPAL
TenantId String False The ID of the tenant to query instead of the connection tenant
BankTransactions

Query, insert and update bank transactions for a Xero organisation.

Table Specific Information

The BankTransactions table allows you to SELECT, INSERT, UPDATE, and DELETE bank transactions for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To create a new bank transaction record, the following fields are required in addition to at least one line item: Type, Contact_ContactName or Contact_ContactId, and BankAccount_AccountId or BankAccount_Code. To create a new line item, the following fields are required: LineItem_Description, LineItem_Quantity, LineItem_UnitAmount, and LineItem_AccountCode.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

  1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the BankTransactions API for more details.

    INSERT INTO BankTransactions (Type, Contact_ContactName, BankAccount_Code, LineItemAggregate) VALUES (
      'RECEIVE',
      'John Doe',
      '200',
      '<LineItem>
            <Description>Monthly account fee</Description>
            <UnitAmount>49.90</UnitAmount>
            <TaxType>NONE</TaxType>
            <TaxAmount>0.00</TaxAmount>
            <LineAmount>49.90</LineAmount>
            <AccountCode>404</AccountCode>
            <Quantity>1.0000</Quantity>
       </LineItem>'
    )
    
  2. You can also insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing bank transaction:

    INSERT INTO BankTransactions (BankTransactionId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
    

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. Delete

The Xero API has limited support for deleting any object. In order to delete a record from the BankTransactions table, you must update the Status field to an appropriate value.

Columns
Name Type ReadOnly Description
Id [KEY] String True The line item index combined with the ID of the bank transaction.
BankTransactionId String False The ID of the bank transaction.
Type String False The transaction type. Valid values are RECEIVE, SPEND, RECEIVE-OVERPAYMENT, RECEIVE-PREPAYMENT, RECEIVE-TRANSFER, SPEND-OVERPAYMENT, SPEND-PREPAYMENT, and SPEND-TRANSFER.
LineAmountTypes String False How line item values are provided relative to tax. Can be either Exclusive, Inclusive or NoTax
Contact_ContactName String False The name of the contact associated with the transaction.
Contact_ContactId String False The ID of the contact associated with the transaction.
BankAccount_AccountId String False The ID for the associated bank account.
BankAccount_AccountName String False The Name for the associated bank account.
BankAccount_Code String False The code for the associated bank account.
LineItem_LineItemId String False The Xero-generated ID of the bank transaction line item
LineItem_Description String False A description for the line item in the bank transaction.
LineItem_Quantity Double False The quantity of the subject of the line item. This value must be greater than or equal to 0.
LineItem_UnitAmount Decimal False The unit amount of the subject of the line item. This amount must be positive.
LineItem_AccountCode String False The code for the referenced account. Including this field is recommended when posting.
LineItem_Item_ItemId String False The ID of the item the lineitem refers to.
LineItem_Item_Name String False The name of the item the lineitem refers to.
LineItem_Item_Code String False The code of the item the lineitem refers to.
LineItem_TaxType String False The tax code for the transaction. This field is used as an override for the default tax code for the selected account.
LineItem_TaxAmount Decimal False The automatically calculated percentage of tax for the line amount, based on the tax rate.
LineItem_LineAmount Decimal False The amount of the line item. If either Quantity or UnitAmount are omitted, Xero will calculate the missing value from LineAmount.
LineItem_TrackingCategory1_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItemAggregate String False Used to define LineItem rows using XML values. Should be provided on INSERT only.
IsReconciled Boolean False This field shows whether this transaction is reconciled.
Date Date False The date of the transaction.
Reference String False An internal Xero reference for the transaction.
CurrencyRate Decimal False Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.
URL String False A link to a source document.
Status String False The bank transaction status code. The valid values are AUTHORISED and DELETED.
SubTotal Decimal False The total of the transaction, excluding taxes.
TotalTax Decimal False The tax on the bank transaction.
Total Decimal False The total of the transaction, including tax.
PrepaymentId String False Xero generated unique identifier for a Prepayment. This will be returned on BankTransactions with a Type of SPEND-PREPAYMENT or RECEIVE-PREPAYMENT.
OverpaymentId String False Xero generated unique identifier for an Overpayment. This will be returned on BankTransactions with a Type of SPEND-OVERPAYMENT or RECEIVE-OVERPAYMENT.
UpdatedDateUTC Datetime True The date when the transaction was last updated.
FullyPaidOnDate Date False The date when the transaction was fully paid.
TenantId String False The ID of the tenant to query instead of the connection tenant
BankTransfers

Usage information for the operation BankTransfers.rsd.

Table Specific Information

The BankTransfers table allows you to SELECT and INSERT bank transfers for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. The following fields are required for inserts: FromBankAccount_Code, ToBankAccount_Code, and Amount

INSERT INTO BankTransfers (FromBankAccount_Code, ToBankAccount_Code, Amount) VALUES ('3d08fd48-434d-4c18-a57b-831663ab70d2', 'ceef65a5-a545-423b-9312-78a53caadbc4', '1000.00')
Columns
Name Type ReadOnly Description
BankTransferId [KEY] String True The unique, Xero-assigned identifier for the bankTransfer.
FromBankAccount_Code String False The bank account code that you will be sending from.
FromBankAccount_AccountId String False The bank account account ID that you will be sending from.
FromBankAccount_Name String False The bank account name that you will be sending from.
ToBankAccount_Code String False The bank account code that you will be sending to.
ToBankAccount_AccountId String False The bank account account ID that you will be sending to.
ToBankAccount_Name String False The bank account name that you will be sending to.
Amount Decimal False The amount of the money to transfer.
Date Date False The date of the transfer YYYY-MM-DD.
CurrencyRate Decimal True The currency rate.
FromBankTransactionID String True The Bank Transaction ID for the source account.
ToBankTransactionID String True The Bank Transaction ID for the destination account.
HasAttachments Boolean True Boolean to indicate if a Bank Transfer has an attachment.
TenantId String False The ID of the tenant to query instead of the connection tenant
BatchPayments

Query and insert BatchPayments for a Xero organisation.

Table Specific Information

The BatchPayments table allows you to SELECT and INSERT batch payments for a Xero organization. The ID column is generated by the connector this value combines the index of the individual payment within the batch with the unique, Xero-generated BatchPaymentId

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To create a new batch payment, the AccountId, Date and Reference are required in addition to at least one payment item; the Payment_InvoiceId and Payment_Amount fields are required to insert a payment item.

You can also provide multiple payments using an XML aggregate which contains the data for each payment in the batch. Please refer to the Xero API documentation for more details on what elements are supported within each payment entry.

INSERT INTO BatchPayments (AccountId, Reference, Date, PaymentAggregate) VALUES (
  'AAAAA-AAAAA-AAAAA-AAAAA',
  '2019-03-01',
  '<Payment>
        <Invoice>
            <InvoiceID>5aa9451d-95d1-4f95-a966-bbab2573f71c</InvoiceID>
            <HasErrors>false</HasErrors>
            <IsDiscounted>false</IsDiscounted>
        </Invoice>
        <PaymentID>a22a64cb-364e-43fa-9a1f-bb2cd1f4adde</PaymentID>
        <Reference>ref/cheque</Reference>
        <Amount>913.55</Amount>
    </Payment>
    <Payment>
        ...
    </Payment>'
)
Columns
Name Type ReadOnly Description
Id [KEY] String True The batch payment index combined with the ID of the batch payment.
BatchPaymentId String True The ID of the invoice. This field is assigned by Xero.
AccountId String False The ID of the account used to make the payment. It must either be a BANK account or have payments enabled.
Particulars String False (NZ only) The particulars that will appear on the statement.
Code String False (NZ only) The transaction code that will appear on the statement.
Reference String False (NZ only) The transaction reference that will appear on the statement.
Details String False (Non-NZ only) The details sent to the organization's bank as a reference for the bank transaction.
Narrative String False (UK only) A description that is only visible in the Xero statement line
Date Date False The date the payment is being made.
Payment_PaymentId String True The ID of the Payment. This field is assigned by Xero.
Payment_InvoiceId String False The ID of the Invoice the payment was made against
Payment_BankAccountNumber String False The account number of the bank the payment is being made to.
Payment_Particulars String False (NZ only) The particulars that will appear on the statement.
Payment_Code String False (NZ only) The transaction code that will appear on the supplier's bank account.
Payment_Reference String False (NZ only) The transaction reference that will appear on the supplier's bank account.
Payment_Details String False The details sent to the supplier's bank as a reference for the bank transaction.
Payment_Amount Decimal False The amount being paid.
PaymentAggregate String False Used to define Payment rows using XML values. Should be provided on INSERT only.
Type String True Either PAYBATCH for bill payments, or RECBATCH for sales payments.
Status String True Either AUTHORISED or DELETED
TotalAmount Decimal True The total of all the payments included in the batch
IsReconciled Boolean True Whether the batch payment has been reconciled
UpdatedDateUTC Datetime True The date when the batch payment was last updated.
TenantId String False The ID of the tenant to query instead of the connection tenant
ContactGroups

Query, insert, update and delete contact groups for a Xero organisation.

Table Specific Information

The ContactGroups table allows you to manage both contact groups as well as the members of those contact groups.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

When inserting with a contact group, you can either insert a new contact group or add contacts into an existing group.

To insert a new contact group, only the Name field should be given:

INSERT INTO ContactGroups (Name) VALUES ('Contractors')

To add a contact into an existing group, both the ContactGroupId and the Contact_ContactId must be provided. The value for the Contact_ContactId field can be retrieved by querying the Contacts table.

INSERT INTO ContactGroups (ContactGroupId, Contact_ContactId) VALUES ('11111-11111-11111-11111', '22222-22222-22222-22222')

Multiple contacts may be provided using the ContactAggregate:

INSERT INTO ContactGroups (ContactGroupId, ContactAggregate) VALUES (
  '11111-11111-11111-11111',
  '<Contact>
    <ContactID>22222-22222-22222-22222</ContactID>
   </Contact>
   <Contact>
    <ContactID>33333-33333-33333-33333</ContactID>
</Contact>')

UPDATE

When updating a contact group, the only fields that can be changed are the Name and Status field. Changing the Status field to DELETED will delete the contact group.

-- Renaming the group
UPDATE ContactGroup SET Name = 'External Contractors' WHERE ContactGroupId = '11111-11111-11111-1111'


-- Deleting the group
UPDATE ContactGroup SET Status = 'DELETED' WHERE ContactGroupId = '11111-11111-11111-1111'

DELETE

Deleting entries from a contact group will remove individual contacts from the group.

DELETE FROM ContactGroups WHERE ID = '3|11111-11111-11111-11111'
Columns
Name Type ReadOnly Description
Id [KEY] String True The line item index combined with the ID of the Contact Group
ContactGroupId String False The unique Xero identifier for the contact group.
Name String False The contact group name.
Status String False The status of the contact group.
ContactAggregate String False Used to define Contact rows using XML values. Only allowed when performing an INSERT into an existing ContactGroup.
Contact_ContactId String False The unique identifier of the contact group member
Contact_Name String True THe name of the contact group member
TenantId String False The ID of the tenant to query instead of the connection tenant
Contacts

Query, insert and update contacts for a Xero organisation.

Table Specific Information

The Contacts table allows you to SELECT, INSERT, and UPDATE contacts for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. The Name field is required to add a new contact.

INSERT INTO Contacts (Name) VALUES ('John Doe')

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns
Name Type ReadOnly Description
ContactId [KEY] String True The unique Xero identifier for the contact.
Name String False The contact name.
AccountNumber String False The account number associated with the contact.
ContactNumber String False An identifier from an external system. Although this field is read-only on the Xero contact screen, it can be updated via the API.
CompanyNumber String False The registration number of the company. The maximum length of this field is 50 characters.
ContactStatus String False The status of the contact. Available status types are ACTIVE or ARCHIVED.
FirstName String False The first name of the contact. The maximum length of this field is 255 characters.
LastName String False The last name of the contact. The maximum length of this field is 255 characters.
EmailAddress String False The email address of the contact. The maximum length of this field is 500 characters.
SkypeUserName String False The Skype username of the contact.
ContactPerson1_FirstName String False First name of contact person.
ContactPerson1_LastName String False Last name of contact person.
ContactPerson1_EmailAddress String False Email address name of contact person.
ContactPerson1_IncludeInEmails Boolean False Whether to include contact person in emails.
ContactPerson2_FirstName String False First name of contact person.
ContactPerson2_LastName String False Last name of contact person.
ContactPerson2_EmailAddress String False Email address name of contact person.
ContactPerson2_IncludeInEmails Boolean False Whether to include contact person in emails.
ContactPerson3_FirstName String False First name of contact person.
ContactPerson3_LastName String False Last name of contact person.
ContactPerson3_EmailAddress String False Email address name of contact person.
ContactPerson3_IncludeInEmails Boolean False Whether to include contact person in emails.
ContactPerson4_FirstName String False First name of contact person.
ContactPerson4_LastName String False Last name of contact person.
ContactPerson4_EmailAddress String False Email address name of contact person.
ContactPerson4_IncludeInEmails Boolean False Whether to include contact person in emails.
BankAccountDetails String False The bank account number of the contact.
TaxNumber String False The region-specific tax number of the contact: the ABN (Australia), GST (New Zealand), VAT (UK), or Tax ID Number (US and global).
AccountsReceivableTaxType String False The default tax type used for the contact on accounts-receivable invoices.
AccountsPayableTaxType String False The default tax type used for the contact on accounts-payable invoices.
Street_AddressLine1 String False Line 1 of the address.
Street_AddressLine2 String False Line 2 of the address.
Street_AddressLine3 String False Line 3 of the address.
Street_AddressLine4 String False Line 4 of the address.
Street_City String False The city.
Street_Region String False The region.
Street_PostalCode String False The postal code.
Street_Country String False The country.
Street_AttentionTo String False The attention-to line of the address.
POBox_AddressLine1 String False Line 1 of the address.
POBox_AddressLine2 String False Line 2 of the address.
POBox_AddressLine3 String False Line 3 of the address.
POBox_AddressLine4 String False Line 4 of the address.
POBox_City String False The city.
POBox_Region String False The region.
POBox_PostalCode String False The postal code.
POBox_Country String False The country.
POBox_AttentionTo String False The attention-to line of the address.
DDI_PhoneNumber String False The phone number without area or country code.
DDI_PhoneAreaCode String False The area code.
DDI_PhoneCountryCode String False The country code.
Default_PhoneNumber String False The phone number without area or country code.
Default_PhoneAreaCode String False The area code.
Default_PhoneCountryCode String False The country code.
Fax_PhoneNumber String False The phone number without area or country code.
Fax_PhoneAreaCode String False The area code.
Fax_PhoneCountryCode String False The country code.
Mobile_PhoneNumber String False The phone number without area or country code.
Mobile_PhoneAreaCode String False The area code.
Mobile_PhoneCountryCode String False The country code.
UpdatedDateUTC Datetime True The timestamp of when the contact was last updated.
ContactGroupId String True Displays which contact groups a contact is included in.
MergedToContactID String True This field displays the destination ID of a contact that has been merged.
IsSupplier Boolean True This field shows whether a contact has any accounts-payable (AP) invoices. This field is set automatically when an AP invoice is generated for a contact.
IsCustomer Boolean True This field shows whether a contact has any accounts-receivable (AR) invoices. This field is set automatically when an AR invoice is generated for a contact.
DefaultCurrency String False The default currency for the contact.
Website String True The website of the contact.
BrandingThemeId String True The ID of the branding theme.
BrandingThemeName String True The Name of the branding theme.
PurchasesDefaultAccountCode String False The default purchases account code for contacts.
PurchasesDefaultLineAmountType String True The default purchases line amount type for contacts. Possible values are: INCLUSIVE, EXCLUSIVE, or NONE.
SalesDefaultAccountCode String False The default sales account code for contacts.
SalesDefaultLineAmountType String True The default sales line amount type for contacts. Possible values are: INCLUSIVE, EXCLUSIVE, or NONE.
BatchPayments_BankAccountNumber String True Batch payment bank account number for the contact.
BatchPayments_BankAccountName String True Batch payment bank account name for the contact.
BatchPayments_Details String True Batch payment details for the contact.
Discount Decimal True The default discount rate for the contact.
Balances_AccountsReceivable_Outstanding Decimal True The outstanding and overdue amounts for sales invoices (accounts receivable).
Balances_AccountsReceivable_Overdue Decimal True The outstanding and overdue amounts for bills (accounts payable).
Balances_AccountsPayable_Outstanding Decimal True The outstanding and overdue amounts for sales invoices (accounts receivable).
Balances_AccountsPayable_Overdue Decimal True The outstanding and overdue amounts for bills (accounts payable).
PaymentTerms_Bills_Day Int True The default payment terms for the contact.
PaymentTerms_Bills_Type String True The default payment terms for the contact.
PaymentTerms_Sales_Day Int True The default payment terms for the contact.
PaymentTerms_Sales_Type String True The default payment terms for the contact.
SalesTrackingCategory1_Name String False The default sales tracking category name for contacts.
SalesTrackingOption1_Name String False The default sales tracking option name for contacts.
SalesTrackingCategory2_Name String False The default sales tracking category name for contacts.
SalesTrackingOption2_Name String False The default sales tracking option name for contacts.
PurchaseTrackingCategory1_Name String False The default purchase tracking category name for contacts.
PurchaseTrackingOption1_Name String False The default purchase tracking option name for contacts.
PurchaseTrackingCategory2_Name String False The default purchase tracking category name for contacts.
PurchaseTrackingOption2_Name String False The default purchase tracking option name for contacts.
HasAttachments Boolean True This field indicates whether the invoice has an attachment.
TenantId String False The ID of the tenant to query instead of the connection tenant
CreditNotes

Query, insert and update credit notes for a Xero organisation.

Table Specific Information

The CreditNotes table allows you to SELECT, INSERT, UPDATE, and DELETE credit note line items for a Xero organization. The ID column is generated by the connector; the value of this field combines the index of the line item with the unique, Xero-generated CreditNoteId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new credit note, the Contact_Name field is required. For a credit note to be approved, the following fields must be defined in addition to at least one line item: Contact_Name, Date, and LineAmountTypes.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

  1. You can also insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the CreditNotes API for more details.

    INSERT INTO CreditNotes (Type, Contact_Name, LineItemAggregate) VALUES (
      'ACCPAYCREDIT',
      'John Doe',
      '<LineItem>
        <Description>LineItemDesc1</Description>
        <Quantity>1</Quantity>
      </LineItem>
      <LineItem>
        <Description>LineItemDesc2</Description>
        <Quantity>2</Quantity>
      </LineItem>'
    )
    
  2. You can insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing credit note:

    INSERT INTO CreditNotes (CreditNoteId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
    

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. DELETE

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

Columns
Name Type ReadOnly Description
Id [KEY] String True The line item index combined with the ID of the credit note.
CreditNoteId String False The ID of the credit note.
CreditNoteNumber String False A unique identifier for the credit note that identifies it to the user.
Reference String False An optional field to store a reference.
Type String False The credit note type. The valid values are ACCPAYCREDIT and ACCRECCREDIT.
Contact_ContactId String False The ID of the contact.
Contact_Name String False The name of the contact.
Date Date False The date the credit note was created.
Status String False The status of the credit note.
LineAmountTypes String False The line amount type, which determines whether line amounts include tax (default). The valid values are Exclusive, Inclusive, and NoTax. Credit notes cannot be approved without this field being defined.
LineItem_LineItemId String False The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated.
LineItem_Description String False The description for the line item of the credit note. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_Quantity Double False The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_UnitAmount Decimal False The unit amount of the subject of the line item. Invoices cannot be approved without these fields.
LineItem_ItemCode String False The user-defined item code.
LineItem_AccountCode String False The account code. The account code must be active for the organisation. Invoices cannot be approved without these fields.
LineItem_Item_ItemId String False The ID of the item the lineitem refers to.
LineItem_Item_Name String False The name of the item the lineitem refers to.
LineItem_Item_Code String False The code of the item the lineitem refers to.
LineItem_TaxType String False The description for the line item of the credit note. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_TaxAmount Decimal False The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_LineAmount Decimal False The total of the Quantity field multiplied by the UnitAmount field with discounts applied.
LineItem_TrackingCategory1_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate Double False The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type credit notes.
LineItemAggregate String False Used to define LineItem rows using XML values. Should be provided on INSERT only.
SubTotal Decimal False The subtotal of the credit note, excluding taxes.
TotalTax Decimal False The total tax on the credit note.
Total Decimal False The total of the credit note (the subtotal plus the total tax).
UpdatedDateUTC Datetime True The date when the credit note was last updated.
CurrencyCode String False The currency used for the credit note.
FullyPaidOnDate Date False The date when the credit note was fully paid.
SentToContact Boolean False This field indicates whether the credit note has been sent to the contact via the Xero app.
BrandingThemeId String False The ID of the branding theme.
CurrencyRate Decimal False Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.
RemainingCredit Decimal False The remaining credit for this credit note.
Allocation1_AppliedAmount Decimal True The amount to be applied from this credit note to a given invoice.
Allocation1_Date Date True The date the allocation was made.
Allocation1_InvoiceId String True The ID of the invoice this allocation has been applied to.
Allocation1_InvoiceNumber String True The Invoice this allocation has been applied to.
Allocation2_AppliedAmount Decimal True The amount to be applied from this credit note to a given invoice.
Allocation2_Date Date True The date the allocation was made.
Allocation2_InvoiceId String True The ID of the invoice this allocation has been applied to.
Allocation2_InvoiceNumber String True The Invoice this allocation has been applied to.
PaymentIds String True A comma-delimited list of payment Ids associated with the credit notes.
HasAttachments Boolean True This field indicates whether the invoice has an attachment.
TenantId String False The ID of the tenant to query instead of the connection tenant
Employees

Query, insert and update employees for a Xero organisation.

Table Specific Information

The Employees table allows you to SELECT, INSERT, and UPDATE employees for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new employee record, the following fields are required: FirstName and LastName.

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns
Name Type ReadOnly Description
EmployeeId [KEY] String True The unique identifier of the employee. This field is assigned by Xero.
Status String False The status of the employee. Valid values are ACTIVE and DELETED.
FirstName String False The first name of the employee.
LastName String False The last name of the employee.
ExternalLink_URL String False A URL to an external resource.
ExternalLink_Description String False A description for the external link.
UpdatedDateUTC Datetime True Timestamp of the last change to the employee record.
TenantId String False The ID of the tenant to query instead of the connection tenant
ExpenseClaims

Query, insert and update expense claims for a Xero organisation.

Table Specific Information

The ExpenseClaims table allows you to SELECT, INSERT, and UPDATE expense claims for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To create a new expense claim record, the UserId field must be defined; in addition, at least one ReceiptId must be specified. One can be provided either via the ReceiptId column:

INSERT INTO ExpenseClaims (UserId, ReceiptId) VALUES (
  'XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY'
)

Multiple may also be specified by including them in the ReceiptAggregate:

INSERT INTO ExpenseClaims (UserId, ReceiptAggregate) VALUES (
  'XXXXX-XXXXX-XXXXX-XXXXX',
  '<Receipt>
    <ReceiptID>YYYYY-YYYYY-YYYYY-YYYYY</ReceiptID>
  </Receipt>
  <Receipt>
    <ReceiptID>ZZZZZ-ZZZZZ-ZZZZZ-ZZZZZ</ReceiptID>
  </Receipt>'
)

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns
Name Type ReadOnly Description
ExpenseClaimId [KEY] String True The unique identifier of the expense claim.
UserId String False The ID of the associated user.
ReceiptAggregate String False Used to define Receipt rows using XML values. Should be provided on INSERT only.
ReceiptId String False The ID of the receipt.
Status String True The status code for the expense claim. The valid values are SUBMITTED, AUTHORISED, and PAID.
UpdatedDateUTC Datetime True The date when the expense claim was last updated.
Total Decimal True The total amount of an expense claim being paid.
AmountDue Decimal True The amount due to be paid for an expense claim.
AmountPaid Decimal True The amount still to pay for an expense claim.
PaymentDueDate Date True The date when the expense claim is due to be paid.
ReportingDate Date True The date the expense claim will be reported in Xero.
TenantId String False The ID of the tenant to query instead of the connection tenant
HistoryBankTransactions

Query and insert BankTransaction history and notes for a Xero organization.

Table Specific Information

The HistoryBankTransactions table allows you to read the notes created on a transaction, and the history of changes to that transaction. If a BankTransactionId is not provided, the history of all BankTransactions will be retrieved.

A BankTransactionId and Details is required for inserting a note:

INSERT INTO HistoryBankTransactions (BankTransactionId, Details) VALUES ('...', 'Refund for overpayment'),
Columns
Name Type ReadOnly Description
Id [KEY] String True A combination of the position of the history item and when it occurred.
BankTransactionId String True The ID of the bank transaction the history item belongs to.
Changes String True What type of change happened on the bank transactions
Date Datetime True When the change occurred
User String True The name of the user that made the change
Details String False The change that was made
TenantId String False The ID of the tenant to query instead of the connection tenant
HistoryBatchPayments

Query and insert BatchPayment history and notes for a Xero organization.

Table Specific Information

The HistoryBatchPayments table allows you to read the notes created on a batch payment, and the history of changes to that batch payment. If a BatchPaymentId is not provided, the history of all batch payments will be retrieved.

An BatchPaymentId and Details is required for inserting a note:

INSERT INTO HistoryBatchPayments (BatchPaymentId, Details) VALUES ('...', 'Batch submitted on 3/1/2019')
Columns
Name Type ReadOnly Description
Id [KEY] String True A combination of the position of the history item and when it occurred.
BatchPaymentId String True The ID of the item the history item belongs to.
Changes String True What type of change happened on the item
Date Datetime True When the change occurred
User String True The name of the user that made the change
Details String False The change that was made
TenantId String False The ID of the tenant to query instead of the connection tenant
HistoryContacts

Query and insert Contact history and notes for a Xero organization.

Table Specific Information

The HistoryContacts table allows you to read the notes created on a contact, and the history of changes to that contact. If a ContactId is not provided, the history of all Contacts will be retrieved.

A ContactId and Details is required for inserting a note:

INSERT INTO HistoryContacts (ContactId, Details) VALUES ('...', 'Reliable customer'),
Columns
Name Type ReadOnly Description
Id [KEY] String True A combination of the position of the history item and when it occurred.
ContactId String True The ID of the contact the history item belongs to.
Changes String True What type of change happened on the contact
Date Datetime True When the change occurred
User String True The name of the user that made the change
Details String False The change that was made
TenantId String False The ID of the tenant to query instead of the connection tenant
HistoryCreditNotes

Query and insert CreditNote history and notes for a Xero organization.

Table Specific Information

The HistoryCreditNotes table allows you to read the notes created on a credit note, and the history of changes to that credit note. If a CreditNoteId is not provided, the history of all CreditNotes will be retrieved.

SELECT * FROM HistoryCreditNotes WHERE CreditNoteId = '...'

A CreditNoteId and Details is required for inserting a note:

INSERT INTO HistoryCreditNotes (CreditNoteId, Details) VALUES ('...', 'Offered 12% discount'),
Columns
Name Type ReadOnly Description
Id [KEY] String True A combination of the position of the history item and when it occurred.
CreditNoteId String True The ID of the credit note the history item belongs to.
Changes String True What type of change happened on the credit note
Date Datetime True When the change occurred
User String True The name of the user that made the change
Details String False The change that was made
TenantId String False The ID of the tenant to query instead of the connection tenant
HistoryInvoices

Query and insert Invoice history and notes for a Xero organization.

Table Specific Information

The HistoryInvoices table allows you to read the notes created on a invoice, and the history of changes to that invoice. If a InvoiceId is not provided, the history of all Invoices will be retrieved.

An InvoiceId and Details is required for inserting a note:

INSERT INTO HistoryInvoices (InvoiceId, Details) VALUES ('...', 'Payment expected on December 1st'),
Columns
Name Type ReadOnly Description
Id [KEY] String True A combination of the position of the history item and when it occurred.
InvoiceId String True The ID of the invoice the history item belongs to.
Changes String True What type of change happened on the invoice
Date Datetime True When the change occurred
User String True The name of the user that made the change
Details String False The change that was made
TenantId String False The ID of the tenant to query instead of the connection tenant
HistoryPurchaseOrders

Query and insert PurchaseOrder history and notes for a Xero organization.

Table Specific Information

The HistoryPurchaseOrders table allows you to read the notes created on a purchase order, and the history of changes to that purchase order. If a PurchaseOrderId is not provided, the history of all PurchaseOrders will be retrieved.

A PurchaseOrderId and Details is required for inserting a note:

INSERT INTO HistoryPurchaseOrders (PurchaseOrderId, Details) VALUES ('...', 'Rescheduled delivery for October 9th'),
Columns
Name Type ReadOnly Description
Id [KEY] String True A combination of the position of the history item and when it occurred.
PurchaseOrderId String True The ID of the purchase order the history item belongs to.
Changes String True What type of change happened on the purchase order
Date Datetime True When the change occurred
User String True The name of the user that made the change
Details String False The change that was made
TenantId String False The ID of the tenant to query instead of the connection tenant
Invoices

Query, insert and update Invoices for a Xero organisation.

Table Specific Information

The Invoices table allows you to SELECT, INSERT, UPDATE, and DELETE invoice line items for a Xero organization. The ID column is generated by the connector this value combines the index of the line item with the unique, Xero-generated InvoiceId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To create a new invoice, the Type and ContactName fields must be set in addition to at least one line item. Note that also at least the LineItem_LineAmount or LineItem_UnitAmount are also required fields for the LineItem aggregate.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

  1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the Invoices API for more details.

    INSERT INTO Invoices (Type, ContactName, LineItemAggregate) VALUES (
      'ACCPAY',
      'Boom FM',
      '<LineItem>
        <ItemCode>12</ItemCode>
        <Description>Onsite project management </Description>
        <Quantity>1.0000</Quantity>
        <UnitAmount>1800.00</UnitAmount>
        <TaxType>OUTPUT</TaxType>
        <TaxAmount>225.00</TaxAmount>
        <LineAmount>1800.00</LineAmount>
        <AccountCode>200</AccountCode>
        <AccountId>4f2a3169-8454-4012-a642-05a88ef32982</AccountId>
        <Tracking>
            <TrackingCategory>
                <Name>Activity/Workstream</Name>
                <Option>Onsite consultancy</Option>
            </TrackingCategory>
        </Tracking>
        <LineItemID>52208ff9-528a-4985-a9ad-b2b1d4210e38</LineItemID>
       </LineItem>
       <LineItem>
        ...
       </LineItem>'
    )
    
  2. You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, adding a line item to an existing invoice:

    INSERT INTO Invoices (InvoiceId, LineItem_Description, LineItem_Quantity, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1, 10)
    

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. DELETE

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

Discount Amounts, Discount Rates and Line Amounts

Invoices in Xero may be discounted either via a fixed amount using LineItem_DiscountAmount or with a percentage using LineItem_DiscountRate. These two methods are not compatible because discount amounts are more precise than discount rates. For example, a line item with the amount 2000.00 and a discount amount of 99.99 cannot be represented using the discount rate 4.999 because Xero allows only two digits of precision on discount rates. In addition, setting any form of discount means that LineItem_LineAmount becomes computed by Xero. This means that, out the three fields LIneItem_DiscountAmount, LineItem_DiscountAmount and LineItem_DiscountRate, only one can be used as part of an INSERT or UDPATE query. For example, neither of the below queries are valid:

INSERT INTO Invoices (Invoice, LineItem_LineAmount, LineItem_DiscountRate) VALUES ('2bf5f07b-edf0-4b05-9200-cc2cd6b89b1e', 4999.99, 6.75)
UPDATE Invoices SET LineItem_DiscountRate = 12.50, LineItem_DiscountAmount = 99.99 WHERE ID = '1|2bf5f07b-edf0-4b05-9200-cc2cd6b89b1e'

It also means that the connector will remove the two other values when assigning any one of these fields. For example, if you were to assign a LineItem_LineAmount to an Invoice, the connector would remove any discount values attached to that Invoice.

Columns
Name Type ReadOnly Description
Id [KEY] String True The line item index combined with the ID of the invoice.
InvoiceId String False The ID of the invoice. This field is assigned by Xero.
Type String False The type of the invoice. The valid values are ACCPAY and ACCREC.
ContactId String False The ID of the contact.
ContactName String False The name of the contact. This value is required when inserting.
LineItem_LineItemId String False The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated.
LineItem_Description String False The description for the line item. This field must contain at least 1 character and is required to create an approved invoice.
LineItem_Quantity Double False The quantity of the associated item in the line item. This field is required for invoice approval and must be zero or positive.
LineItem_UnitAmount Decimal False The unit amount of the associated item in the line item. This field is required for invoice approval.
LineItem_ItemCode String False The code that identifies the associated item.
LineItem_AccountCode String False The account code. This must be active for the organisation. This field is required for invoice approval.
LineItem_Item_ItemId String False The ID of the item the lineitem refers to.
LineItem_Item_Name String False The name of the item the lineitem refers to.
LineItem_Item_Code String False The code of the item the lineitem refers to.
LineItem_TaxType String False The tax type. This field is used to override AccountCode, the default tax code for the selected account.
LineItem_TaxAmount Decimal False The automatically calculated percentage of tax, based on the tax rate, for the line amount.
LineItem_LineAmount Decimal False The total of the quantity multiplied by the unit amount with any discounts applied.
LineItem_TrackingCategory1_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate Double False The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices.
LineItem_DiscountAmount Decimal False The discount amount being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices.
LineItemAggregate String False Used to define LineItem rows using XML values. Should be provided on INSERT only.
Date Date False The date the invoice was created.
DueDate Date False The date the invoice is due.
LineAmountTypes String False This field specifies whether line amounts include tax (default). When this field is not specified, line amount totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax.
InvoiceNumber String False The unique, alphanumeric code identifying the invoice. For ACCREC-type invoices. When this field is missing it is automatically generated from the Organisation Invoice Settings. For ACCPAY-type invoices, this field stores a nonunique alphanumeric code identifying the invoice.
Reference String False An additional reference number for ACCREC-type invoices.
BrandingThemeId String False The ID of the branding theme.
URL String False A URL to a source document.
CurrencyCode String False The ISO 4217 currency code.
CurrencyRate Decimal False The currency rate for a multicurrency invoice. If no rate is specified, the XE.com day rate is used.
Status String False The status code for the invoice. The valid values are DRAFT, SUBMITTED, and AUTHORISED.
SentToContact Boolean False This field indicates whether the invoice should be marked as sent. This field can be set on only invoices that have been approved.
ExpectedPaymentDate Date True The expected payment date. This field is shown on sales invoices (accounts receivable).
PlannedPaymentDate Date True The planned payment date. This field is shown on bills (accounts payable).
SubTotal Decimal True The subtotal for the invoice, which excludes taxes.
TotalTax Decimal True The total tax for the invoice.
Total Decimal True The total for the invoice, including tax -- the sum of SubTotal and TotalTax.
TotalDiscount Decimal True Total of discounts applied on the invoice line items.
HasAttachments Boolean True This field indicates whether the invoice has an attachment.
PaymentIds String True A comma-delimited list of payment Ids associated with the invoice.
PrePaymentIds String True A comma-delimited list of prepayment Ids associated with the invoice.
OverPaymentIds String True A comma-delimited list of overpayment Ids associated with the invoice.
CreditNoteIds String True A comma-delimited list of credit note Ids applied to the invoice.
AmountDue Decimal True The amount remaining to be paid on the invoice.
AmountPaid Decimal True The sum of payments received for the invoice.
CISDeduction Decimal True The deduction withheld by a contractor to be paid to HMRC. Only available for UK organizations.
FullyPaidOnDate Date True The date the invoice was fully paid. Only returned on fully paid invoices.
AmountCredited Decimal True The sum of all credit notes, overpayments, and prepayments applied to the invoice.
UpdatedDateUTC Datetime True The date when the invoice was last updated.
TenantId String False The ID of the tenant to query instead of the connection tenant
Items

Query, insert, update and delete items for a Xero organisation.

Table Specific Information

The Items table allows you to SELECT, INSERT, and UPDATE items for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new item record, the Code field must be defined.

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns
Name Type ReadOnly Description
ItemId [KEY] String True The unique, Xero-assigned identifier for the item.
Code String False A user-defined code that identifies the item.
InventoryAssetAccountCode String False The inventory asset account for the item. The account must be of type INVENTORY. The COGSAccountCode in PurchaseDetails is also required to create a tracked item.
Name String False The name of the item.
PurchaseDescription String False The purchase description of the item.
Description String False A description of the item.
PurchaseDetails_UnitPrice String False The unit Price of the item.
PurchaseDetails_COGSAccountCode String False Cost of goods sold account. Only applicable to the purchase details of tracked items.
PurchaseDetails_AccountCode String False The code of the account to be used for the purchased item.
PurchaseDetails_TaxType String False The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, TAX003, etc.
SalesDetails_UnitPrice String False The unit price of the sold item.
SalesDetails_COGSAccountCode String False Cost of goods sold account. Only applicable to the purchase details of tracked items.
SalesDetails_AccountCode String False The code of the account to be used for the sold item.
SalesDetails_TaxType String False The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, TAX003, etc.
IsTrackedAsInventory Boolean False True for items that are tracked as inventory. An item will be tracked as inventory if the InventoryAssetAccountCode and COGSAccountCode are set.
TotalCostPool Decimal True The value of the item on hand. Calculated using average cost accounting.
QuantityOnHand Double True The quantity of the item on hand.
IsSold Boolean False Boolean value, defaults to true. When IsSold is true the item will be available on sales transactions in the Xero UI. If IsSold is updated to false then Description and SalesDetails values will be nulled.
IsPurchased Boolean False Boolean value, defaults to true. When IsPurchased is true the item is available for purchase transactions in the Xero UI. If IsPurchased is updated to false then PurchaseDescription and PurchaseDetails values will be nulled.
UpdatedDateUTC Datetime True Timestamp of the last change to the item record.
TenantId String False The ID of the tenant to query instead of the connection tenant
LinkedTransactions

Query, insert, update and delete LinkedTransactions for a Xero organisation.

Columns
Name Type ReadOnly Description
LinkedTransactionID [KEY] String True The Xero identifier for an Linked Transaction
SourceTransactionID String False The identifier of the source transaction (the purchase component of a billable expense). Currently, only invoices with a type of ACCPAY can be a source transaction.
SourceLineItemID String False The line item identifier from the source transaction.
ContactID String False The identifier for the contact on the target transaction i.e. the customer that the expense is being billed on to.
TargetTransactionID String False The identifier of the target transaction (the sale component of a billable expense). Currently, only invoices with a type of ACCREC can be a target transaction.
TargetLineItemID String False The line item identifier from the target transaction. When allocating a billable expense to a target transaction the TargetLineItemID is optional.
Status String False The status of the linked transaction. This is derived from the statuses of the source and target transactions and cannot be explicitly set/updated.
Type String False This will always be BILLABLEEXPENSE. More types may be added in future.
UpdatedDateUTC Datetime True The last modified date in UTC format
TenantId String False The ID of the tenant to query instead of the connection tenant
ManualJournals

Query, insert and update manual journals for a Xero organisation.

Table Specific Information

The ManualJournals table allows you to SELECT, INSERT, and UPDATE manual journals journal lines for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ManualJournalId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new manual journal record, the Narration field is required in addition to at least two line items. A journal record must contain at least one credit and one debit, so that the record as a whole is balanced.

  1. You can insert a new object with multiple line items using an XML aggregate. The elements supported here are the same as the JournalLine columns without the "JournalLine_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the ManualJournals API for more details.

    INSERT INTO ManualJournals (Narration, JournalLineAggregate) VALUES (
      'Accrued expenses',
      '<JournalLine>
            <Description>Coded incorrectly Office Equipment should be Computer Equipment</Description>
            <TaxType>NONE</TaxType>
            <LineAmount>-2569</LineAmount>
            <AccountCode>720</AccountCode>
        </JournalLine>
        <JournalLine>
            ...
        </JournalLine>'
    )
    
  2. You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, to add a line item to an existing invoice:

    INSERT INTO ManualJournals (ManualJournalId, JournalLine_LineAmount, JournalLine_AccountCode) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 55.00, '200')
    

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns
Name Type ReadOnly Description
Id [KEY] String True The journal line index combined with the ID of the manual journal.
ManualJournalId String False The ID of the manual journal.
Narration String False A description of the journal being posted.
JournalLine_LineAmount Decimal False The total for the line item. This value is positive for debits and negative for credits.
JournalLine_AccountCode String False The account code that identifies the account.
JournalLine_Description String False The description for the manual journal.
JournalLine_TaxType String False This field can be used to override the default tax code for the selected account.
JournalLine_TrackingCategory1_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory1_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory1_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory1_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory1_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
JournalLine_TrackingCategory2_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
JournalLineAggregate String False Used to define JournalLine rows using XML values. Should be provided on INSERT only.
Date Date False The date the journal was posted.
Status String False The status code for the manual journal. The valid values are VALID, POSTED, DELETED, and VOIDED.
URL String False A link to a source document.
ShowOnCashBasisReports Boolean False This field indicates whether this journal should be included on cash-basis reports. The default value is true.
LineAmountTypes String False The line amount type, which specifies whether line amounts include tax (default). When this field is not specified, line amount totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax.
UpdatedDateUTC Datetime True The date when the manual journal was last updated.
HasAttachments Boolean True This field indicates whether the invoice has an attachment.
TenantId String False The ID of the tenant to query instead of the connection tenant
Payments

Query, insert and delete payments for a Xero organisation.

Table Specific Information

The Payments table allows you to SELECT and INSERT payments for a Xero organization.

Note that this tables does not include payments on expense claims - to retrieve those, you will need to read from the ExpenseClaimPayments view.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new payment record, the InvoiceNumber and AccountCode fields are required.

Columns
Name Type ReadOnly Description
PaymentId [KEY] String True The ID of the payment.
Date Date False The date the payment is being made.
Amount Decimal False The amount of the payment. This value must be less than or equal to the outstanding amount owing on the invoice.
CurrencyRate Decimal False The exchange rate when the payment is received. This field is only used for non-base-currency invoices and credit notes.
Reference String False A optional description for the payment.
PaymentType String False The payment type.
Status String False The status of the payment.
UpdatedDateUTC Datetime False The date when the payment was last updated.
IsReconciled Boolean False This field indicates whether the payment has been reconciled. Only available when querying for one payment at a time.
BatchPaymentId String True If the payment was submitted through BatchPayments, this is the ID of the batch it was provided with.
InvoiceId String False The ID of the invoice. When applying a payment, specify either the InvoiceId or the InvoiceNumber.
InvoiceNumber String False The invoice number of the invoice or credit note. When applying a payment, specify either the InvoiceId or the InvoiceNumber.
CreditNoteId String False The ID of the credit note. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber.
CreditNoteNumber String False The credit note number. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber.
AccountId String False The ID of the account used to make the payment. The account type must be BANK or payments to the account must be enabled.
AccountCode String False The account code of the account used to make the payment.
TenantId String False The ID of the tenant to query instead of the connection tenant
PurchaseOrders

Query, insert and update purchase orders for a Xero organisation.

Columns
Name Type ReadOnly Description
Id [KEY] String True The line item index combined with the ID of the purchase order.
PurchaseOrderId String False The ID of the purchase order.
PurchaseOrderNumber String False A unique identifier for the purchase order that identifies it to the user.
Reference String False An optional field to store a reference.
Type String False The purchase order type. The valid values are ACCPAYCREDIT and ACCRECCREDIT.
Contact_ContactId String False The ID of the contact.
Contact_Name String False The name of the contact.
DeliveryDate Date False The date the purchase order is to be delivered.
ExpectedArrivalDate Date False The date the purchase is expected to arrive.
DeliveryAddress String False The address the goods are to be delivered to.
AttentionTo String False The person the delivery is going to.
Telephone String False The phone number of the person accepting the delivery.
DeliveryInstructions String False Free-form field for information needed to complete the delivery.
Date Date False The date the purchase order was created.
Status String False The status of the purchase order.
LineAmountTypes String False The line amount type, which determines whether line amounts include tax (default). The valid values are Exclusive, Inclusive, and NoTax. Purchase orders cannot be approved without this field being defined.
LineItem_LineItemId String False The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated.
LineItem_Description String False The description for the line item of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_Quantity Double False The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_UnitAmount Decimal False The unit amount of the subject of the line item. Invoices cannot be approved without these fields.
LineItem_ItemCode String False The user-defined item code.
LineItem_AccountCode String False The account code. The account code must be active for the organisation. Invoices cannot be approved without these fields.
LineItem_TaxType String False The description for the line item of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_TaxAmount Decimal False The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_LineAmount Decimal False The total of the Quantity field multiplied by the UnitAmount field with discounts applied.
LineItem_DiscountRate Double False The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type purchase orders.
LineItemAggregate String False Used to define LineItem rows using XML values. Should be provided on INSERT only.
SubTotal Decimal False The subtotal of the purchase order, excluding taxes.
TotalTax Decimal False The total tax on the purchase order.
Total Decimal False The total of the purchase order (the subtotal plus the total tax).
LineItem_TrackingCategory1_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
UpdatedDateUTC Datetime True The date when the purchase order was last updated.
HasAttachments Boolean True Indicates if the PurchaseOrder has a file attached.
CurrencyCode String False The currency used for the purchase order.
BrandingThemeId String False The ID of the branding theme.
CurrencyRate Decimal False Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.
TenantId String False The ID of the tenant to query instead of the connection tenant
Quotes

Query quotes for a Xero organization

Table Specific Information

The Quotes table allows you to SELECT, INSERT, and UPDATE Quote lines for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated QuoteId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new Quote, the ContactId and Date fields are required in addition to at least one line item. A Quote line item must at minimum contain a description (LineItem_Description).

  1. You can insert a new Quote with multiple line items using an XML aggregate. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix. Please refer to the Xero documentation of the Quotes API for more details.

    INSERT INTO Quotes (ContactId, Date, LineItemAggregate) VALUES (
      '883628bc-7f22-4234-8a4c-81571816a8a1',
      '2023-11-21',
      '<LineItem>
            <Description>Example Description</Description>
        </LineItem>'
    )
    
  2. You can also create multiple Quotes in a single query by using a bulk insert. To do this, you should not include a QuoteId in your query, then specify the values for the first line item of each Quote to add.

    INSERT INTO Quotes (ContactId, Date, LineItem_Description)
      VALUES
    ('883628bc-7f22-4234-8a4c-81571816a8a1', '2023-11-21', 'Example Description'),
    ('883628bc-7f22-4234-8a4c-81571816a8a1', '2023-11-21', 'Example Description')
    
  3. You can also insert new line items on an existing object by specifying the Xero-generated value of the existing record. For example, to add 2 new line items to an existing Quote:

    INSERT INTO Quotes (QuoteId, LineItem_Description)
      VALUES
    ('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'newItem1'),
    ('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'newItem2')
    

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns
Name Type ReadOnly Description
Id [KEY] String False The line item index combined with the ID of the Quote.
QuoteId String False The ID of the Quote. This field is assigned by Xero.
ContactId String False The ID of the contact.
ContactName String False The name of the contact.
Date Date False The date the quote was issued.
ExpiryDate Date False The date the quote expires.
Status String False The status of the quote.
LineItem_LineItemId String False The Xero generated identifier for a line item.
LineItem_Description String False The description of the line item.
LineItem_Quantity Double False The quantity of the associated item in the line item.
LineItem_UnitAmount Decimal False The unit amount of the associated item in the line item.
LineItem_ItemCode String False The code that identifies the associated item.
LineItem_AccountCode String False The account code. This must be active for the organisation.
LineItem_TaxType String False The tax type. This field is used to override AccountCode, the default tax code for the selected account.
LineItem_TaxAmount Decimal False The automatically calculated percentage of tax, based on the tax rate, for the line amount.
LineItem_LineAmount Decimal False The total of the quantity multiplied by the unit amount with any discounts applied.
LineItem_DiscountRate Double False The discount percentage being applied to the line item.
LineItem_TrackingCategory1_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
QuoteNumber String False The unique, alphanumeric code identifying the quote.
LineAmountTypes String False This field specifies whether line amounts include tax. The valid values are Exclusive, Inclusive, and NoTax.
Reference String False An additional reference number for ACCREC-type invoices.
CurrencyCode String False The ISO 4217 currency code.
CurrencyRate Decimal False The currency rate for a multicurrency invoice. If no rate is specified, the XE.com day rate is used.
SubTotal Decimal False The subtotal for the invoice, which excludes taxes.
TotalTax Decimal False The total tax for the invoice.
Total Decimal False The total for the invoice, including tax -- the sum of SubTotal and TotalTax.
TotalDiscount Decimal False Total of discounts applied on the invoice line items.
BrandingThemeId String False The ID of the branding theme.
Terms String False The terms of the quote.
UpdatedDateUTC Datetime False The date when the invoice was last updated.
TenantId String False The ID of the tenant to query instead of the connection tenant
LineItemAggregate String False Used to define Quote LineItem rows using XML values. Should be provided on INSERT only.
Receipts

Query, insert and update receipts for a Xero organisation.

Table Specific Information

The Receipts table allows you to SELECT, INSERT, UPDATE, and DELETE receipt line items for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ReceiptId.

Note that this table only includes draft receipts - to retrieve receipts that have already been claimed, you will need to read from the ExpenseClaimReceipts view.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a receipt record, the following fields are required in addition to at least one line item: Date, Contact_Name, and User_UserId. To add a new line item, the following fields are required: LineItem_Description, LineItem_UnitAmount, and LineItem_AccountCode.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

  1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the Receipts API for more details.

    INSERT INTO Receipts (Date, Contact_Name, User_UserId, LineItemAggregate) VALUES (
      '4/19/2006 9:16:48 AM',
      'John Doe',
      'c81045b2-5740-4aea-bf8a-3956941af387',
      '<LineItem>
        <Description>Coffee with client</Description>
        <UnitAmount>13.00</UnitAmount>
        <AccountCode>200</AccountCode>
       </LineItem>
       <LineItem>
        <Description>Bagel</Description>
        <UnitAmount>2.00</UnitAmount>
        <AccountCode>200</AccountCode>
       </LineItem>'
    )
    
  2. You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, to add a line item to an existing invoice:

    INSERT INTO Receipts (ReceiptId, LineItem_Description, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'Coffee with client', 13.80)
    

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. DELETE

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

Columns
Name Type ReadOnly Description
Id [KEY] String True The line item index combined with the unique, Xero-generated identifier for the receipt.
ReceiptId String False The unique, Xero-generated identifier for the script.
Date Date False The date of the receipt.
Contact_ContactId String False The ID of the contact. A contact must be specified when creating a receipt.
Contact_Name String False The name of the contact.
LineItem_Description String False The description for the item.
LineItem_UnitAmount Decimal False The unit amount of the item.
LineItem_AccountCode String False The code for the associated account.
LineItem_Quantity Double False The quantity of the item.
LineItem_TaxType String False The tax type. This field can be used to override the default tax code for the selected account.
LineItem_LineAmount Decimal False The total of the unit amount multiplied by the quantity.
LineItem_TrackingCategory1_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String False The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String False The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String False The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String False The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate Double False The percentage discount being applied to the line item.
LineItemAggregate String False Used to define LineItem rows using XML values. Should be provided on INSERT only.
User_UserId String False The user in the organisation that the expense claim receipt is for.
Reference String False Additional reference number.
LineAmountTypes String False This field specifies whether line amounts include tax (default). When this field is not specified, line amounts totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax.
SubTotal Decimal False The total, excluding taxes, of the receipt.
TotalTax Decimal False The total tax on the receipt.
Total Decimal False The total of the receipt -- the sum of SubTotal plus TotalTax.
Status String True The current status of the receipt. The valid status types are DRAFT, SUBMITTED, AUTHORISED, and DECLINED.
ReceiptNumber String True The Xero-generated number of the receipt in sequence in the current claim for the given user.
UpdatedDateUTC Datetime True The date when the receipt was last updated.
HasAttachments Boolean True This field indicates whether the receipt has attachments.
URL String True A link to a source document.
TenantId String False The ID of the tenant to query instead of the connection tenant
TaxRates

Usage information for the operation TaxRates.rsd.

Table Specific Information

The TaxRates table allows you to SELECT, INSERT, and UPDATE tax components for tax rates in a Xero organization. The ID column is generated by the connector this value combines the index of the line item with the unique, Xero-generated TaxType.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To create a new tax rate, the Name field must be set in addition to at least one tax component; the TaxComponent_Name, TaxComponent_Rate, and TaxComponent_IsCompound fields are required to insert a new line item.

In addition to inserting a single row, this table offers two additional ways to insert into a table.

  1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the TaxComponent columns without the "TaxComponent_" prefix.

    INSERT INTO TaxRates (Name, TaxComponentAggregate) VALUES (
      'My Tax Rate',
      '<TaxComponent>
        <Name>customTax1</Name>
        <Rate>1.0000</Rate>
        <IsCompound>false</IsCompound>
       </TaxComponent>
       <TaxComponent>
        <Name>customTax2</Name>
        <Rate>2.0000</Rate>
        <IsCompound>false</IsCompound>
       </TaxComponent>'
    )
    
  2. You can also insert a new tax component on an existing object by specifiying the Xero-generated value for the existing record. For example, adding a tax component to an existing tax rate:

    INSERT INTO TaxRates (TaxType, TaxComponent_Name, TaxComponent_Rate, TaxComponent_IsCompound) VALUES ('TAX001', 'customTax2', '2.0000', 'False')
    

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Note: You cannot update predefined tax rates through the Xero API. DELETE

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

Columns
Name Type ReadOnly Description
Id [KEY] String True The tax component index combined with the TaxType.
TaxType String True The tax type for the tax rate. There are default tax rates, additionally new tax rates can be setup for a Xero organisation. All new tax rates added have a TaxType of the format TAX001, TAX002 etc.
Name String False The name for the tax rate.
TaxComponent_Name String False The name of Tax Component
TaxComponent_Rate Double False The tax Rate (up to 4dp).
TaxComponent_IsCompound Boolean False Boolean to describe if Tax rate is compounded.
TaxComponentAggregate String False Used to define TaxComponent rows using XML values. Should be provided on INSERT only.
Status String False The Status of the Tax Rate e.g. ACTIVE, DELETED
ReportTaxType String False The report tax type. Required for AU, NZ, and UK organisations (US and Global do not support this element).
CanApplyToAssets Boolean True Boolean to describe if tax rate can be used for asset accounts.
CanApplyToEquity Boolean True Boolean to describe if tax rate can be used for equity accounts.
CanApplyToExpenses Boolean True Boolean to describe if tax rate can be used for expense accounts.
CanApplyToLiabilities Boolean True Boolean to describe if tax rate can be used for liability accounts.
CanApplyToRevenue Boolean True Boolean to describe if tax rate can be used for revenue accounts.
DisplayTaxRate Double True Tax Rate (decimal to 4dp) e.g 12.5000.
EffectiveRate Double True Effective Tax Rate (decimal to 4dp) e.g 12.5000.
TenantId String False The ID of the tenant to query instead of the connection tenant

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Xero Connector Views
Name Description
AgedPayablesByContact Query Aged Payables for a Contact.
AgedReceivablesByContact Query Aged Receivables for a Contact.
BalanceSheet Query a monthly Balance Sheet.
BankStatement Query the transactions listed on the account.
BankSummary Query the balances and cash movements for each bank account.
BrandingThemes Query the Branding Themes defined for the Xero organisation.
BudgetLines Query Budget line items for a Xero organisation
Budgets Query Budgets for a Xero organisation
BudgetSummary Query a monthly Budget Summary.
ContactCISSettings Query CIS settings for contacts in a Xero organisation.
CreditNoteAllocations Query credit note allocations for a Xero organisation.
Currencies Query the Currencies for a Xero organisation.
ExecutiveSummary Query a summary of changes between a month and the previous month
ExpenseClaimPayments Query payments for a Xero organisation.
ExpenseClaimReceipts Query claimed receipts for a Xero organisation.
HistoryBankTransfers Query BankTransfer history and notes for a Xero organization.
HistoryExpenseClaims Query ExpenseClaim history and notes for a Xero organization.
HistoryItems Query Item history and notes for a Xero organization.
HistoryOverpayments Query Overpayment history and notes for a Xero organization.
HistoryPayments Query Payment history and notes for a Xero organization.
HistoryPrepayments Query Prepayment history and notes for a Xero organization.
HistoryReceipts Query Receipt history and notes for a Xero organization.
HistoryRepeatingInvoices Query RepeatingInvoice history and notes for a Xero organization.
Journals Query the line items in journals for a Xero organisation.
Organisation Query organisation data for a Xero organisation.
OrganisationActions Query organisation data for a Xero organisation.
OverpaymentAllocations Query overpayment allocations for a Xero organisation.
Overpayments Query Overpayments for a Xero organisation.
PrepaymentAllocations Query prepayment allocations for a Xero organisation.
Prepayments Query Prepayments for a Xero organisation.
ProfitAndLoss Query a Profit and Loss report over a date range.
RepeatingInvoices Query Repeating Invoice templates.
Tenants Query the list of organizations connected to your Xero account.
TrackingCategories Query Tracking Categories for a Xero organisation.
TrialBalance Query a monthly Trial Balance.
Users Query users for a Xero organisation.
AgedPayablesByContact

Query Aged Payables for a Contact.

Table-Specific Information

SELECT

If the ContactId is not provided, the Aged Payables report for all contacts will be returned, one after the other.

Columns
Name Type Description
ID [KEY] String The row number, combined with the contact ID.
ContactId String The ID of the contact this is reporting on.
Date Datetime The Date the bill is created.
Reference String Purchase order reference for this bill.
DueDate Datetime Date the amount is due.
Overdue Decimal Amount due passed due date.
Currency String The currency used for all fields but DueLocal, or empty if the default currency is used
Total Decimal Total amount due.
Paid Decimal Amount paid.
Credited Decimal Amount credited.
Due Decimal Amount due.
DueLocal Decimal Amount due in the default organisation currency.
InvoiceId String The invoice for this bill.
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
fromDate Datetime Show all payable invoices from this date for contact.
toDate Datetime Show all payable invoices to this date for the contact.
AgedReceivablesByContact

Query Aged Receivables for a Contact.

Table-Specific Information

SELECT

If the ContactId is not provided, the Aged Payables report for all contacts will be returned, one after the other.

Columns
Name Type Description
ID [KEY] String The row number, combined with the contact ID.
ContactId String The ID of the contact this is reporting on.
Date Datetime The Date the invoice is created.
Reference String Sale order reference for this bill.
DueDate Datetime Date the amount is due.
Overdue Decimal Amount due passed due date.
Currency String The currency used for all fields but DueLocal. Is empty if multi-currency is not in use.
Total Decimal Total amount due.
Paid Decimal Amount paid.
Credited Decimal Amount credited.
Due Decimal Amount due.
DueLocal Decimal Amount due in the default organisation currency.
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
fromDate Datetime Show all payable invoices from this date for contact.
toDate Datetime Show all payable invoices to this date for the contact.
BalanceSheet

Query a monthly Balance Sheet.

Columns
Name Type Description
ID [KEY] Integer The row number.
Label1 String The Label column of the BalanceSheet report.
Label2 String The Value column of the BalanceSheet report.
Label3 String The Label column of the BalanceSheet report.
Label4 String The Value column of the BalanceSheet report.
Label5 String The Label column of the BalanceSheet report.
Label6 String The Value column of the BalanceSheet report.
Label7 String The Label column of the BalanceSheet report.
Label8 String The Value column of the BalanceSheet report.
Label9 String The Label column of the BalanceSheet report.
Label10 String The Value column of the BalanceSheet report.
Label11 String The Label column of the BalanceSheet report.
Label12 String The Value column of the BalanceSheet report.
Label13 String The Value column of the BalanceSheet report.
AccountId String The ID of the row's account from the BalanceSheet report.
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime As at date.
Periods Int The number of periods to go back from ToDate.
Timeframe String How big each period should be (MONTH, QUARTER or YEAR)
TrackingOptionID1 String Show all payable invoices to this date for the contact.
TrackingOptionID2 String Show all payable invoices to this date for the contact.
PaymentsOnly Boolean Set this to true to get cash transactions only.
StandardLayout Boolean If you set this parameter to true then no custom report layouts will be applied to response.
BankStatement

Query the transactions listed on the account.

Table Specific Information

SELECT

A bankAccountId may be provided when querying this view. If one is not given, then this report will be run for each bank account stored in Xero.

Columns
Name Type Description
BankAccountId String The ID of the bank account this is reporting on
Date Date The date when the transaction occurred
Description String The transaction's description
Reference String The transaction's reference value
Reconciled String Whether the transaction has been reconciled ('Yes' or 'No')
Source String Where the transaction came from
Amount Decimal The transaction's value
Balance Decimal The statement's running total
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
fromDate Date The start date of the statement
toDate Date The end date of the statement. Must be within 365 days of the start.
BankSummary

Query the balances and cash movements for each bank account.

Columns
Name Type Description
ID [KEY] Integer The row number.
BankAccount String The Bank account number.
OpeningBalance Decimal Amount of funds in the account at the beginning of the financial period.
CashReceived Decimal Amount added to the account.
CashSpent Decimal Amount subtracted from the account.
FXGain Decimal Changes in the account caused by currency conversions
ClosingBalance Decimal Amount of funds in the account at the end of the financial period.
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
toDate Datetime Starting date.
fromDate Datetime Closing date.
PaymentsOnly Boolean Set this to true to get cash transactions only.
BrandingThemes

Query the Branding Themes defined for the Xero organisation.

Table Specific Information

The BrandingThemes view allows you to SELECT branding themes for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns
Name Type Description
BrandingThemeId [KEY] String The unique Xero identifier for the branding theme.
Name String The name of the branding theme.
SortOrder Int The ranked order of the branding theme. The value for the default branding theme is 0.
CreatedDateUTC Datetime The timestamp when the branding theme was created.
TenantId String The ID of the tenant to query instead of the connection tenant
BudgetLines

Query Budget line items for a Xero organisation

Columns
Name Type Description
Id [KEY] String The line item index combined with the ID of the account.
BudgetId String The ID of the invoice. This field is assigned by Xero.
AccountId String The account that applies to this budget line item.
AccountCode String The code of this budget line item account.
Period String The year and month that the balance applies to.
Amount Decimal The amount budgeted to the account for the period.
Notes String Any notes that apply to the balance.
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
DateFrom Datetime Includes budget periods starting from this date.
DateTo Datetime Includes budget periods until this date.
Budgets

Query Budgets for a Xero organisation

Columns
Name Type Description
BudgetId [KEY] String The ID of the budget. This field is assigned by Xero.
Type String The type of the budget, either OVERALL or TRACKING.
Description String The budget description.
TrackingCategory1_CategoryId String The ID of a tracking category.
TrackingCategory1_OptionId String The ID a a tracking category option.
TrackingCategory1_Name String The name of the tracking category.
TrackingCategory1_Option String The value of the tracking category option.
TrackingCategory2_CategoryId String The ID of a tracking category.
TrackingCategory2_OptionId String The ID a a tracking category option.
TrackingCategory2_Name String The name of the tracking category.
TrackingCategory2_Option String The value of the tracking category option.
UpdatedDateUTC Datetime The date when the budget was last updated.
TenantId String The ID of the tenant to query instead of the connection tenant
BudgetSummary

Query a monthly Budget Summary.

Columns
Name Type Description
ID [KEY] Integer The row number.
BankAccount String The Bank account number.
Period1 String Period 1 of the report
Period2 String Period 2 of the report
Period3 String Period 3 of the report
Period4 String Period 4 of the report
Period5 String Period 5 of the report
Period6 String Period 6 of the report
Period7 String Period 7 of the report
Period8 String Period 8 of the report
Period9 String Period 9 of the report
Period10 String Period 10 of the report
Period11 String Period 11 of the report
Period12 String Period 12 of the report
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime As at date.
Periods Integer The number of periods to compare (integer between 1 and 12).
Timeframe Integer The period size to compare to: Specify 1 for month, 3 for quarter, or 12 for year).
ContactCISSettings

Query CIS settings for contacts in a Xero organisation.

Columns
Name Type Description
ContactId [KEY] String The unique Xero identifier for the contact.
CISEnabled Boolean Whether the contact is currently a CIS subcontractor
Rate Int The CIS deduction rate for the contact, in percent
TenantId String The ID of the tenant to query instead of the connection tenant
CreditNoteAllocations

Query credit note allocations for a Xero organisation.

Columns
Name Type Description
Id [KEY] String The allocation index combined with the ID of the credit note.
CreditNoteId String The ID of the credit note.
Allocation_AppliedAmount Decimal The amount to be applied from this credit note to a given invoice.
Allocation_Date Date The date the allocation was made.
Allocation_InvoiceId String The ID of the invoice this allocation has been applied to.
Allocation_InvoiceNumber String The Invoice this allocation has been applied to.
TenantId String The ID of the tenant to query instead of the connection tenant
Currencies

Query the Currencies for a Xero organisation.

Table Specific Information

The Currencies table allows you to SELECT currencies for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns
Name Type Description
Code [KEY] String The ISO 4217 currency code.
Description String A name that identifies the currency.
TenantId String The ID of the tenant to query instead of the connection tenant
ExecutiveSummary

Query a summary of changes between a month and the previous month

Table Specific Information

SELECT

A date may be provided when querying this view. If it is not provided, then this report will compare the current month with the previous month.

Columns
Name Type Description
Metric String The value that is being compared between months
ThisMonth Decimal The value of the metric at this month
PreviousMonth Decimal The value of the metric at the previous month
Variance Decimal The percent difference between the previous month and this month.
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
date Date The month to compare with the previous month.
ExpenseClaimPayments

Query payments for a Xero organisation.

View Specific Information

The ExpenseClaimPayments view allows you to SELECT payments to different expense claims for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ExpenseClaimID.

Note that this table does not include other types of Payments - to retrieve those, you will need to read from the Payments table.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns
Name Type Description
Id [KEY] String The line item index combined with the unique, Xero-generated identifier for the expense claim.
ExpenseClaimId String The ID of the expense claim that this payment belongs to
Date Date The date the payment is being made.
Amount Decimal The amount of the payment. This value must be less than or equal to the outstanding amount owing on the expense claim.
Reference String A optional description for the payment.
AccountId String The ID of the account used to make the payment. The account type must be BANK or payments to the account must be enabled.
AccountCode String The account code of the account used to make the payment.
AccountName String The account code of the account used to make the payment.
TenantId String The ID of the tenant to query instead of the connection tenant
ExpenseClaimReceipts

Query claimed receipts for a Xero organisation.

View Specific Information

The ExpenseClaimReceipts view allows you to SELECT claimed receipt line items for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ExpenseClaimID.

Note that this table does not include draft receipts - to retrieve those, you will need to read from the Receipts table.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns
Name Type Description
Id [KEY] String The line item index combined with the unique, Xero-generated identifier for the expense claim.
ExpenseClaimId String The ID of the expense claim that this receipt belongs to
ReceiptId String The unique, Xero-generated identifier for the receipt.
Date Date The date of the receipt.
Contact_ContactId String The ID of the contact. A contact must be specified when creating a receipt.
Contact_Name String The name of the contact.
LineItem_Description String The description for the item.
LineItem_UnitAmount Decimal The unit amount of the item.
LineItem_AccountCode String The code for the associated account.
LineItem_Quantity Double The quantity of the item.
LineItem_TaxType String The tax type. This field can be used to override the default tax code for the selected account.
LineItem_LineAmount Decimal The total of the unit amount multiplied by the quantity.
LineItem_TrackingCategory1_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate Double The percentage discount being applied to the line item.
User_UserId String The user in the organisation that the expense claim receipt is for.
Reference String Additional reference number.
LineAmountTypes String This field specifies whether line amounts include tax (default). When this field is not specified, line amounts totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax.
SubTotal Decimal The total, excluding taxes, of the receipt.
TotalTax Decimal The total tax on the receipt.
Total Decimal The total of the receipt -- the sum of SubTotal plus TotalTax.
Status String The current status of the receipt. The valid status types are DRAFT, SUBMITTED, AUTHORISED, and DECLINED.
ReceiptNumber String The Xero-generated number of the receipt in sequence in the current claim for the given user.
UpdatedDateUTC Datetime The date when the receipt was last updated.
HasAttachments Boolean This field indicates whether the receipt has attachments.
URL String A link to a source document.
TenantId String The ID of the tenant to query instead of the connection tenant
HistoryBankTransfers

Query BankTransfer history and notes for a Xero organization.

View Specific Information

The HistoryBankTransfers view allows you to read the notes created on a transfer, and the history of changes to that transfer. If a BankTransferId is not provided, the history of all BankTransfers will be retrieved.

Columns
Name Type Description
Id [KEY] String A combination of the position of the history item and when it occurred.
BankTransferId String The ID of the bank transfer the history item belongs to.
Changes String What type of change happened on the bank transfers
Date Datetime When the change occurred
User String The name of the user that made the change
Details String The change that was made
TenantId String The ID of the tenant to query instead of the connection tenant
HistoryExpenseClaims

Query ExpenseClaim history and notes for a Xero organization.

View Specific Information

The HistoryExpenseClaims view allows you to read the notes created on a expense claim, and the history of changes to that expense claim. If a ExpenseClaimId is not provided, the history of all ExpenseClaims will be retrieved.

Columns
Name Type Description
Id [KEY] String A combination of the position of the history item and when it occurred.
ExpenseClaimId String The ID of the expense claim the history item belongs to.
Changes String What type of change happened on the expense claim
Date Datetime When the change occurred
User String The name of the user that made the change
Details String The change that was made
TenantId String The ID of the tenant to query instead of the connection tenant
HistoryItems

Query Item history and notes for a Xero organization.

View Specific Information

The HistoryItems view allows you to read the notes created on a item, and the history of changes to that item. If a ItemId is not provided, the history of all Items will be retrieved.

Columns
Name Type Description
Id [KEY] String A combination of the position of the history item and when it occurred.
ItemId String The ID of the item the history item belongs to.
Changes String What type of change happened on the item
Date Datetime When the change occurred
User String The name of the user that made the change
Details String The change that was made
TenantId String The ID of the tenant to query instead of the connection tenant
HistoryOverpayments

Query Overpayment history and notes for a Xero organization.

View Specific Information

The HistoryOverpayments view allows you to read the notes created on a overpayment, and the history of changes to that overpayment. If a OverpaymentId is not provided, the history of all Overpayments will be retrieved.

Columns
Name Type Description
Id [KEY] String A combination of the position of the history item and when it occurred.
OverpaymentId String The ID of the overpayment the history item belongs to.
Changes String What type of change happened on the overpayment
Date Datetime When the change occurred
User String The name of the user that made the change
Details String The change that was made
TenantId String The ID of the tenant to query instead of the connection tenant
HistoryPayments

Query Payment history and notes for a Xero organization.

Columns
Name Type Description
Id [KEY] String A combination of the position of the history item and when it occurred.
PaymentId String The ID of the payment the history item belongs to.
Changes String What type of change happened on the payment
Date Datetime When the change occurred
User String The name of the user that made the change
Details String The change that was made
TenantId String The ID of the tenant to query instead of the connection tenant
HistoryPrepayments

Query Prepayment history and notes for a Xero organization.

View Specific Information

The HistoryPrepayments view allows you to read the notes created on a prepayment, and the history of changes to that prepayment. If a PrepaymentId is not provided, the history of all Prepayments will be retrieved.

Columns
Name Type Description
Id [KEY] String A combination of the position of the history item and when it occurred.
PrepaymentId String The ID of the prepayment the history item belongs to.
Changes String What type of change happened on the prepayment
Date Datetime When the change occurred
User String The name of the user that made the change
Details String The change that was made
TenantId String The ID of the tenant to query instead of the connection tenant
HistoryReceipts

Query Receipt history and notes for a Xero organization.

View Specific Information

The HistoryReceipts view allows you to read the notes created on a receipt, and the history of changes to that receipt. If a ReceiptId is not provided, the history of all Receipts will be retrieved.

Columns
Name Type Description
Id [KEY] String A combination of the position of the history item and when it occurred.
ReceiptId String The ID of the receipt the history item belongs to.
Changes String What type of change happened on the receipt
Date Datetime When the change occurred
User String The name of the user that made the change
Details String The change that was made
TenantId String The ID of the tenant to query instead of the connection tenant
HistoryRepeatingInvoices

Query RepeatingInvoice history and notes for a Xero organization.

View Specific Information

The HistoryRepeatingInvoices view allows you to read the notes created on a repeating invoice, and the history of changes to that repeating invoice. If a RepeatingInvoiceId is not provided, the history of all RepeatingInvoices will be retrieved.

Columns
Name Type Description
Id [KEY] String A combination of the position of the history item and when it occurred.
RepeatingInvoiceId String The ID of the repeating invoice the history item belongs to.
Changes String What type of change happened on the repeating invoice
Date Datetime When the change occurred
User String The name of the user that made the change
Details String The change that was made
TenantId String The ID of the tenant to query instead of the connection tenant
Journals

Query the line items in journals for a Xero organisation.

Table Specific Information

The Journals view allows you to SELECT the journal lines in journals for a Xero organization. The ID column is generated by the connector; this value combines the index of the line item with the unique, Xero-generated JournalId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns
Name Type Description
Id [KEY] String The journal line index combined with the unique Xero identifier of the journal.
JournalId String The unique Xero identifier of the journal.
JournalDate Date The date the journal was posted.
JournalNumber Integer The journal number, assigned by Xero.
CreatedDateUTC Datetime The date the journal was entered in the system.
Reference String A reference value.
SourceId String The identifier for the source transaction (e.g. InvoiceId).
SourceType String The journal source type. The type of transaction that created the journal. (e.g ACCREC, ACCPAY, ACCRECCREDIT)
JournalLine_JournalLineId String The unique Xero identifier of the journal line item.
JournalLine_Description String The description of the journal line item.
JournalLine_AccountId String The account associated with the line item in the journal.
JournalLine_AccountCode String Customer-defined alphanumeric account code; e.g, 200 or SALES.
JournalLine_AccountType String The type of the account; e.g., BANK, CURRENT, or CRRLIAB.
JournalLine_AccountName String The name of the account.
JournalLine_NetAmount Decimal The net amount of the line item. This value is positive for debits and negative for credits.
JournalLine_GrossAmount Decimal The net amount plus the tax amount.
JournalLine_TaxAmount Decimal The total tax on a journal line.
JournalLine_TaxType String The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, etc.
JournalLine_TaxName String A name that identifies the tax type.
LineItem_TrackingCategory1_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
ModifiedAfter String If set, only journals created or modified since this timestamp will be returned e.g. 2009-11-12T00:00:00.
PaymentsOnly Boolean Set this to true to get cash transactions only.
Offset Int If provided, only journals with a higher JournalNumber will be returned.
Organisation

Query organisation data for a Xero organisation.

Table Specific Information

The organization view allows you to SELECT organization data for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns
Name Type Description
ShortCode [KEY] String A unique identifier for the organisation.
APIKey String Unique APIKey for Xero-to-Xero transactions.
Name String Display name of the organisation shown in Xero.
LegalName String Organisation name shown on reports.
PaysTax Boolean Whether the organisation is registered with a local tax authority.
Version String The version of Xero used by the organisation.
BaseCurrency String Default currency for the organisation.
CountryCode String Country code for the organisation.
IsDemoCompany String Whether the organisation is a demo company.
OrganisationStatus String Will be set to ACTIVE if you can connect to the organisation via the Xero API.
RegistrationNumber String The registration number for NZ, AU, and UK organisations.
TaxNumber String Tax number of the organisation.
FinancialYearEndDay String Financial year end day of the organisation. For example, 1-31.
FinancialYearEndMonth String Financial year end month of the organisation. For example, 1-12.
PeriodLockDate Date Period lock date of the organisation.
EndOfYearLockDate Date End of year lock date of the organisation.
CreatedDateUTC Datetime Timestamp of when the organisation was created in Xero.
UpdatedDateUTC Datetime Timestamp of the last change to the user record.
OrganisationEntityType String The entity type of the organisation. For example, COMPANY.
Timezone String The time zone for the organisation.
Edition String BUSINESS or PARTNER. Parnter edition organizations have restricted functionality.
Class String One of: DEMO, TRIAL, STARTER, STANDARD, PREMIUM, PREMIUM_20, PREMIUM_50, PREMIUM_100, LEDGER, GST_CASHBOOK, NON_GST_CASHBOOK.
LineOfBusiness String Description of business type of the organisation.
Street_AddressLine1 String The street address line 1.
Street_AddressLine2 String The street address line 2.
Street_AddressLine3 String The street address line 3.
Street_AddressLine4 String The street address line 4.
Street_City String The street address city.
Street_PostalCode String The street address postal code.
Street_Country String The street address country.
Street_Region String The street address region.
Street_AttentionTo String The street address attention-to line.
POBox_AddressLine1 String The PO box address line 1.
POBox_AddressLine2 String The PO box address line 2.
POBox_AddressLine3 String The PO box address line 3.
POBox_AddressLine4 String The PO box address line 4.
POBox_City String The PO box address city.
POBox_PostalCode String The PO box address postal code.
POBox_Country String The PO box address country.
POBox_Region String The PO box address region.
POBox_AttentionTo String The PO box address attention-to line.
DDI_PhoneNumber String The DDI phone number.
DDI_PhoneAreaCode String The DDI area code.
DDI_PhoneCountryCode String The DDI country code.
Default_PhoneNumber String The default phone number.
Default_PhoneAreaCode String The default area code.
Default_PhoneCountryCode String The default country code.
Fax_PhoneNumber String The fax phone number.
Fax_PhoneAreaCode String The fax area code.
Fax_PhoneCountryCode String The fax country code.
Mobile_PhoneNumber String The mobile phone number.
Mobile_PhoneAreaCode String The mobile area code.
Mobile_PhoneCountryCode String The mobile country code.
Facebook_URL String The URL to the Facebook page of the organisation.
Twitter_URL String The URL to the Twitter page of the organisation.
GooglePlus_URL String The URL to the Google Plus page of the organisation.
LinkedIn_URL String The URL to the LinkedIn page of the organisation.
Website_URL String A URL to the website of the organisation.
PaymentTerms_Bills_Day String Default payment terms for bills (days).
PaymentTerms_Bills_Type String Default payment terms for bills (type).
PaymentTerms_Sales_Day String Default payment terms for sales (days).
PaymentTerms_Sales_Type String Default payment terms for sales (type).
TenantId String The ID of the tenant to query instead of the connection tenant
OrganisationActions

Query organisation data for a Xero organisation.

Columns
Name Type Description
Name [KEY] String The name of the permission.
Status Bool Whether the action is permitted to this app.
TenantId String The ID of the tenant to query instead of the connection tenant
OverpaymentAllocations

Query overpayment allocations for a Xero organisation.

Columns
Name Type Description
Id [KEY] String The allocation index combined with the ID of the overpayment.
OverpaymentId String The ID of the overpayment.
Allocation_AppliedAmount Decimal The amount to be applied from this overpayment to a given invoice.
Allocation_Date Date The date the allocation was made.
Allocation_InvoiceId String The ID of the invoice this allocation has been applied to.
Allocation_InvoiceNumber String The Invoice this allocation has been applied to.
TenantId String The ID of the tenant to query instead of the connection tenant
Overpayments

Query Overpayments for a Xero organisation.

Columns
Name Type Description
OverpaymentId [KEY] String The ID of the overpayment.
Date Date The date the overpayment was made.
Type String The overpayment type. The valid values are RECEIVE-OVERPAYMENT and SPEND-OVERPAYMENT.
RemainingCredit Decimal The remaining credit balance on the overpayment.
Contact_ContactId String The ID of the contact.
Contact_Name String The name of the contact.
Status String The status of the overpayment. Valid values are AUTHORISED, PAID, and VOIDED.
SubTotal Decimal The subtotal for the overpayment, which excludes taxes.
TotalTax Decimal The total tax for the overpayment.
Total Decimal The total for the overpayment, including tax -- the sum of SubTotal and TotalTax.
LineItem_LineItemId String The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated.
LineItem_Description String The description for the line item. This field must contain at least 1 character and is required to create an approved invoice.
LineItem_Quantity Double The quantity of the associated item in the line item. This field is required for invoice approval and must be zero or positive.
LineItem_UnitAmount Decimal The unit amount of the associated item in the line item. This field is required for invoice approval.
LineItem_ItemCode String The code that identifies the associated item.
LineItem_AccountCode String The account code. This must be active for the organisation. This field is required for invoice approval.
LineItem_TaxType String The tax type. This field is used to override AccountCode, the default tax code for the selected account.
LineItem_TaxAmount Decimal The automatically calculated percentage of tax, based on the tax rate, for the line amount.
LineItem_LineAmount Decimal The total of the quantity multiplied by the unit amount with any discounts applied.
LineItem_TrackingCategory1_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate Double The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices.
LineAmountTypes String The LineAmount Type of the overpayment. Valid values are Exclusive, Inclusive, and NoTax.
CurrencyCode String Currency used for the overpayment.
CurrencyRate String The currency rate for a multicurrency overpayment. If no rate is specified, the XE.com day rate is used.
UpdatedDateUTC Datetime The date when the overpayment was last updated.
Allocation1_AppliedAmount Decimal The amount to be applied from this credit note to a given invoice.
Allocation1_Date Date The date the allocation was made.
Allocation1_InvoiceId String The ID of the invoice this allocation has been applied to.
Allocation1_InvoiceNumber String The Invoice this allocation has been applied to.
Allocation2_AppliedAmount Decimal The amount to be applied from this credit note to a given invoice.
Allocation2_Date Date The date the allocation was made.
Allocation2_InvoiceId String The ID of the invoice this allocation has been applied to.
Allocation2_InvoiceNumber String The Invoice this allocation has been applied to.
PaymentIds String A comma-delimited list of payment Ids associated with the overpayment.
HasAttachments Boolean Boolean to indicate if a overpayment has an attachment.
TenantId String The ID of the tenant to query instead of the connection tenant
PrepaymentAllocations

Query prepayment allocations for a Xero organisation.

Columns
Name Type Description
Id [KEY] String The allocation index combined with the ID of the prepayment.
PrepaymentId String The ID of the prepayment.
Allocation_AppliedAmount Decimal The amount to be applied from this prepayment to a given invoice.
Allocation_Date Date The date the allocation was made.
Allocation_InvoiceId String The ID of the invoice this allocation has been applied to.
Allocation_InvoiceNumber String The Invoice this allocation has been applied to.
TenantId String The ID of the tenant to query instead of the connection tenant
Prepayments

Query Prepayments for a Xero organisation.

Columns
Name Type Description
PrepaymentId [KEY] String The ID of the prepayment.
Date Date The date the prepayment was made.
Reference String A optional description for the payment.
Type String The prepayment type. The valid values are RECEIVE-PREPAYMENT and SPEND-PREPAYMENT.
RemainingCredit Decimal The remaining credit balance on the prepayment.
Contact_ContactId String The ID of the contact.
Contact_Name String The name of the contact.
Status String The status of the prepayment. Valid values are AUTHORISED, PAID, and VOIDED.
SubTotal Decimal The subtotal for the prepayment, which excludes taxes.
TotalTax Decimal The total tax for the prepayment.
Total Decimal The total for the prepayment, including tax -- the sum of SubTotal and TotalTax.
LineItem_LineItemId String The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated.
LineItem_Description String The description for the line item. This field must contain at least 1 character and is required to create an approved invoice.
LineItem_Quantity Double The quantity of the associated item in the line item. This field is required for invoice approval and must be zero or positive.
LineItem_UnitAmount Decimal The unit amount of the associated item in the line item. This field is required for invoice approval.
LineItem_ItemCode String The code that identifies the associated item.
LineItem_AccountCode String The account code. This must be active for the organisation. This field is required for invoice approval.
LineItem_TaxType String The tax type. This field is used to override AccountCode, the default tax code for the selected account.
LineItem_TaxAmount Decimal The automatically calculated percentage of tax, based on the tax rate, for the line amount.
LineItem_LineAmount Decimal The total of the quantity multiplied by the unit amount with any discounts applied.
LineItem_TrackingCategory1_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate Double The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices.
LineAmountTypes String The LineAmount Type of the prepayment. Valid values are Exclusive, Inclusive, and NoTax.
CurrencyCode String Currency used for the prepayment.
CurrencyRate String The currency rate for a multicurrency prepayment. If no rate is specified, the XE.com day rate is used.
UpdatedDateUTC Datetime The date when the prepayment was last updated.
Allocation1_AppliedAmount Decimal The amount to be applied from this credit note to a given invoice.
Allocation1_Date Date The date the allocation was made.
Allocation1_InvoiceId String The ID of the invoice this allocation has been applied to.
Allocation1_InvoiceNumber String The Invoice this allocation has been applied to.
Allocation2_AppliedAmount Decimal The amount to be applied from this credit note to a given invoice.
Allocation2_Date Date The date the allocation was made.
Allocation2_InvoiceId String The ID of the invoice this allocation has been applied to.
Allocation2_InvoiceNumber String The Invoice this allocation has been applied to.
PaymentIds String A comma-delimited list of payment Ids associated with the prepayment.
HasAttachments Boolean Boolean to indicate if a prepayment has an attachment.
TenantId String The ID of the tenant to query instead of the connection tenant
ProfitAndLoss

Query a Profit and Loss report over a date range.

Table-Specific Information

SELECT

There are a few different ways the range of dates can be specified when using this report:

1. When no parameters are specified, the report spans the current month:

SELECT * FROM ProfitAndLoss

2. When just FromDate is specified, the report spans from FromDate to the current date:

SELECT * FROM ProfitAndLoss WHERE FromDate = '2018-01-01'

Note

FromDate must be within a year of the current date.

3. When FromDate and ToDate are specified, the report is run from FromDate to ToDate:

SELECT * FROM ProfitAndLoss WHERE FromDate = '2018-01-01' AND ToDate = '2018-04-01'

Note

FromDate must be within a year of ToDate.

4. When Periods and Timeframe are specified, the report spans from the current date to the start of the specified Timeframe:

SELECT * FROM ProfitAndLoss WHERE Periods = 2 AND Timeframe = 'MONTH'

Note

When specifying the 'Month' timeframe, the Xero API may generate a report for the last 30 days from the start of the specified month, even for months with 31 days. As a workaround, specify a 31-day month in the ToDate column as an additional query criteria.

SELECT * FROM ProfitAndLoss WHERE Periods = 11 AND Timeframe = 'MONTH' AND FromDate= '2023-10-01' AND ToDate= '2023-10-31'

5. When Periods, Timeframe and ToDate are specified, the report spans from ToDate to the start of the specified Timeframe:

SELECT * FROM ProfitAndLoss WHERE Periods = 2 AND Timeframe = 'MONTH' AND ToDate = '2018-04-01'

Note

The report start date is set as the first of the current month. ToDate must be set as a later date, within 365 days of the first of the current month.

Columns
Name Type Description
ID [KEY] Integer The row number.
Label1 String The Label column of the ProfitAndLossStandard report.
Label2 String The Value column of the ProfitAndLossStandard report.
Label3 String The Label column of the ProfitAndLossStandard report.
Label4 String The Value column of the ProfitAndLossStandard report.
Label5 String The Label column of the ProfitAndLossStandard report.
Label6 String The Value column of the ProfitAndLossStandard report.
Label7 String The Label column of the ProfitAndLossStandard report.
Label8 String The Value column of the ProfitAndLossStandard report.
Label9 String The Label column of the ProfitAndLossStandard report.
Label10 String The Value column of the ProfitAndLossStandard report.
Label11 String The Label column of the ProfitAndLossStandard report.
Label12 String The Value column of the ProfitAndLossStandard report.
Label13 String The Value column of the ProfitAndLossStandard report.
AccountId String The ID of the row's account from the ProfitAndLossStandard report.
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
FromDate Datetime Show all payable invoices from this date for contact.
ToDate Datetime Show all payable invoices to this date for the contact.
Periods Int The number of periods to go back from ToDate.
Timeframe String How big each period should be (MONTH, QUARTER or YEAR)
TrackingCategoryID String If you specify the trackingCategoryID parameter then the Profit and Loss Report will show figures for each of the options in the category as separate columns.
TrackingOptionID String If you specify this parameter in addition to the trackingCategoryID then just one option will be returned (i.e. 1 column only)
TrackingCategoryID2 String If you specify a second trackingCategoryID parameter then the Profit and Loss Report will show figures for each combination of options from the two categories as separate columns.
TrackingOptionID2 String If you specify this parameter in addition to a second trackingCategoryID then just one option will be returned combined with the option/s from the first tracking category.
PaymentsOnly Boolean Set this to true to get cash transactions only.
StandardLayout Boolean If you set this parameter to true then no custom report layouts will be applied to response.
RepeatingInvoices

Query Repeating Invoice templates.

Table Specific Information

The RepeatingInvoice view allows you to SELECT the line items in repeating invoices for a Xero organization. The ID column is generated by the connector; this value combines the index of the line item with the unique, Xero-generated RepeatingInvoiceId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns
Name Type Description
Id [KEY] String The line item index combined with the ID of the invoice.
RepeatingInvoiceId String The ID of the invoice. This field is assigned by Xero.
Type String The type of the invoice. The valid values are ACCPAY and ACCREC.
ContactId String The ID of the contact. This value is required when inserting.
ContactName String The name of the contact. This value is required when inserting.
Schedule_Period Integer Integer used with the unit for the scheduled period. e.g. 1 (every 1 week).
Schedule_Unit String the schedule unit. The valid values are WEEKLY or MONTHLY.
Schedule_DueDate Integer Integer used with the due date type for the scheduled period. e.g. 20 (of following month).
Schedule_DueDateType String The due date type of the schedule. The valid values are DAYSAFTERBILLDATE, DAYSAFTERBILLMONTH, OFCURRENTMONTH, and OFFOLLOWINGMONTH
Schedule_StartDate Date Invoice date the first invoice in the repeating schedule.
Schedule_NextScheduledDate Date The calendar date of the next invoice in the schedule to be generated.
Schedule_EndDate Date Invoice end date. Only returned if the template has an end date set.
LineItem_LineItemId String The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated.
LineItem_Description String The description for the line item. This field must contain at least 1 character and is required to create an approved invoice.
LineItem_Quantity Double The quantity of the associated item in the line item. This field is required for invoice approval and must be zero or positive.
LineItem_UnitAmount Decimal The unit amount of the associated item in the line item. This field is required for invoice approval.
LineItem_ItemCode String The code that identifies the associated item.
LineItem_AccountCode String The account code. This must be active for the organisation. This field is required for invoice approval.
LineItem_TaxType String The tax type. This field is used to override AccountCode, the default tax code for the selected account.
LineItem_TaxAmount Decimal The automatically calculated percentage of tax, based on the tax rate, for the line amount.
LineItem_LineAmount Decimal The total of the quantity multiplied by the unit amount with any discounts applied.
LineItem_TrackingCategory1_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName String The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId String The ID of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName String The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat String The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_DiscountRate Double The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices.
LineAmountTypes String This field specifies whether line amounts include tax (default). When this field is not specified, line amount totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax.
Reference String An additional reference number for ACCREC-type invoices.
BrandingThemeId String The ID of the branding theme.
CurrencyCode String The ISO 4217 currency code.
Status String The status code for the invoice. The valid values are DRAFT, SUBMITTED, and AUTHORISED.
SubTotal Decimal The subtotal for the invoice, which excludes taxes.
TotalTax Decimal The total tax for the invoice.
Total Decimal The total for the invoice, including tax -- the sum of SubTotal and TotalTax.
HasAttachments Boolean This field indicates whether the invoice has an attachment.
TenantId String The ID of the tenant to query instead of the connection tenant
Tenants

Query the list of organizations connected to your Xero account.

Columns
Name Type Description
TenantId [KEY] String A unique identifier for the tenant.
Name String The name of the organization or practice
Type String Whether the tenant is an organization or a practice
IsActive Boolean Whether the connection is currently using this tenant
TrackingCategories

Query Tracking Categories for a Xero organisation.

Table Specific Information

The TrackingCategories view allows you to SELECT tracking categories for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated TrackingCategoryId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns
Name Type Description
Id [KEY] String The tracking option index combined with the unique Xero identifier of the tracking category.
TrackingCategoryId String The unique Xero identifier of the tracking category.
Name String The name of the tracking category. For example, Department or Region.
Status String The status of a tracking category. This will always be ACTIVE.
TrackingOptionId String The unique Xero identifier of the tracking option.
OptionName String The name of the tracking category option. For example, North or South.
TenantId String The ID of the tenant to query instead of the connection tenant
TrialBalance

Query a monthly Trial Balance.

Columns
Name Type Description
ID [KEY] Integer The row number.
Account String Account name.
Debit String Debit trial balance for current month up to the date specified.
Credit String Credit trial balance for current month up to the date specified.
YTDDebit String Year to Date Debit.
YTDCredit String Year to Date Credit.
AccountId String Account ID
TenantId String The ID of the tenant to query instead of the connection tenant
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime As at date.
PaymentsOnly Boolean Set this to true to get cash transactions only.
Users

Query users for a Xero organisation.

Table Specific Information

The Users table allows you to SELECT users for a Xero organization.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns
Name Type Description
UserId [KEY] String The unique Xero identifier of the user.
FirstName String First name of the user.
LastName String Last name of the user.
EmailAddress String Email address of the user.
UpdatedDateUTC Datetime Timestamp of the last change to the user record.
IsSubscriber Boolean Indicates if the user is the subscriber.
OrganisationRole String Organisation role of the user, values include READONLY, INVOICEONLY, STANDARD, FINANCIALADVISER, MANAGEDCLIENT, and CASHBOOKCLIENT.
TenantId String The ID of the tenant to query instead of the connection tenant

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Xero.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Xero, along with an indication of whether the procedure succeeded or failed.

Xero Connector Stored Procedures
Name Description
AllocatetoInvoice Allocates a document against an Invoice.
CreateReportSchema Saves a dynamic ProfitAndLoss report as a custom schema.
DownloadAttachment Obtains an attachment from a specified document in Xero.
DownloadPDF Downloads a specified document in PDF format from Xero.
GetLimitInfo Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL Obtains the OAuth authorization URL for authentication with various Xero services.
ListAttachments Retrieves a list of attachments that have been uploaded against a document in Xero.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SendInvoiceEmail Sends an email to the contacts of a sales invoice
SwitchTenant Changes the tenant this connection fetches data from.
UploadAttachment Obtains a list of attachments an item has in Xero.
AllocatetoInvoice

Allocates a document against an Invoice.

Input
Name Type Required Description
Type String True The type of document to allocate. The allowed values are CreditNotes, Overpayments, Prepayments.
DocumentId String True The ID of the document to be allocated.
InvoiceId String True The invoice this document is being allocated against.
AppliedAmount String True The amount being applied to the invoice.
TenantId String False The ID of the tenant to query instead of the connection tenant
Date String False The date the overpayment is applied.
Result Set Columns
Name Type Description
Success String A boolean indicating if the operation was successful.
CreateReportSchema

Saves a dynamic ProfitAndLoss report as a custom schema.

CreateReportSchema

CreateReportSchema creates a schema file based on the specified report.

This schema adds a table to your existing list that corresponds with the results of your report, which can then be queried like other tables.

(Reports from the Xero are not modeled by connector as queryable tables by default.)

The generated schema file outlines the metadata for the report, such as columns and column data types. You can edit the file to adjust data types, rename columns, and include or exclude columns.

Updating a Report Schema

In the following example, the SP CreateReportSchema creates a new report using TestReportTest1 as a base template. It appends new columns to TestReportTest1 and creates a new report, named TestReport2. The new report is saved as ...\TestReportTest2.rsd.

EXECUTE [CreateReportSchema]
    [ReportName] = "TestReportTest2",
    [CustomFieldIdsPrimitive] = "1459925,1459928",
    [CustomFieldIdsDropdown] = "1469785",
    [CustomDimensionKeyIds] = "13539564",
    [BaseReportName] = "TestReportTest1",
[FileName] = "...\TestReportTest2.rsd"

Input

Name Type Required Description
ReportName String True Name that should be given to the generated report. If FileStream and the 'Location' connection property are not set, the associated file will be placed in the file path set in the 'Location'
Periods Int False The number of periods to go back from ToDate.
Timeframe String False How big each period should be (MONTH, QUARTER or YEAR)
TrackingCategoryID String False If you specify the trackingCategoryID parameter then the Profit and Loss Report will show figures for each of the options in the category as separate columns.
TrackingCategoryID2 String False If you specify a second trackingCategoryID parameter then the Profit and Loss Report will show figures for each combination of options from the two categories as separate columns.
TenantId String False The ID of the tenant to query instead of the connection tenant

Result Set Columns

Name Type Description
Success String A boolean indicating if the operation was successful.
FileData String If the DownloadPath and FileStream are not provided, this contains the base64 encoded content of the file.
DownloadAttachment

Obtains an attachment from a specified document in Xero.

Input
Name Type Required Description
Table String True The table containing the document with the attachment. The allowed values are Invoices, Receipts, CreditNotes, BankTransactions, BankTransfers, Contacts, Accounts, ManualJournals.
ObjectId String True The unique ID of the item to obtain an attachment from.
Filename String True The name of the attachment to retrieve.
DownloadPath String False The path of the file to be saved. Must be a path to a file, not just a directory.
TenantId String False The ID of the tenant to query instead of the connection tenant
Result Set Columns
Name Type Description
Success String A boolean indicating if the operation was successful.
FileData String If the DownloadPath and FileStream are not provided, this contains the base64 encoded content of the file.
DownloadPDF

Downloads a specified document in PDF format from Xero.

Input
Name Type Required Description
Table String True The table containing the document to be downloaded. The allowed values are Invoices, CreditNotes, Quotes.
ObjectId String True The unique ID of the item to download as PDF.
DownloadPath String False The path of the file to be saved. Must be a path to a file, not just a directory.
TenantId String False The ID of the tenant to query instead of the connection tenant
Result Set Columns
Name Type Description
Success String A boolean indicating if the operation was successful.
FileData String If the DownloadPath and FileStream are not provided, this contains the base64 encoded content of the file.
GetLimitInfo

Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.

If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:

SELECT * FROM Organisation LIMIT 1

Result Set Columns

Name Type Description
TenantId String ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining Integer The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining Integer The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining Integer The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter Integer The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified Datetime When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.
GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input
Name Type Required Description
AuthMode String True The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken String False The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey String False The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL String False This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier String False The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier String False The PKCEVerifier returned by GetOAuthAuthorizationURL.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret String The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input
Name Type Required Description
CallbackURL String False This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope String False The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State String False A state token to include in the authorization URL. Not included by default.
Result Set Columns
Name Type Description
URL String The URL to complete user authentication.
AuthToken String A token used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
AuthKey String A key used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
PKCEVerifier String A random value used as input for GetOAuthAccessToken in the PKCE flow.
ListAttachments

Retrieves a list of attachments that have been uploaded against a document in Xero.

Input
Name Type Required Description
Table String True The table containing the document to retrieve a list of attachments from. The allowed values are Invoices, Receipts, CreditNotes, BankTransactions, BankTransfers, Contacts, Accounts, ManualJournals.
ObjectId String False The unique ID of the document to retrieve a list of attachments from.
TenantId String False The ID of the tenant to query instead of the connection tenant
Result Set Columns
Name Type Description
AttachmentId String The ID of the attachment.
FileName String The file name of the attachment.
URL String The URL of the attachment.
MimeType String The Internet media type of the attachment.
ContentLength String The size of the attachment in bytes.
RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input
Name Type Required Description
OAuthRefreshToken String True The refresh token returned from the original authorization code exchange.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input
Name Type Required Description
TenantId String False The ID of the tenant to revoke, as given in the Tenants view
Result Set Columns
Name Type Description
Success String Whether the connection was successfully revoked
SendInvoiceEmail

Sends an email to the contacts of a sales invoice

Input
Name Type Required Description
InvoiceId String True The InvoiceId of the invoice to mail
TenantId String False The ID of the tenant to query instead of the connection tenant
Result Set Columns
Name Type Description
Success String A boolean indicating if the operation was successful.
SwitchTenant

Changes the tenant this connection fetches data from.

Input
Name Type Required Description
TenantId String False The ID of the tenant to attach to. If empty, the current tenant is not changed.
Result Set Columns
Name Type Description
ActiveTenant String The ID of the tenant that is currently active.
UploadAttachment

Obtains a list of attachments an item has in Xero.

Input
Name Type Required Description
Table String True The table containing the document with the attachment. The allowed values are Invoices, Receipts, CreditNotes, BankTransactions, BankTransfers, Contacts, Accounts, ManualJournals.
ObjectId String True The unique ID of the item to obtain an attachment from.
FilePath String False The location of the file to attach.
FileData String False Base-64 encoded data for the file. Required if FilePath is not defined.
Filename String False The name of the file to attach. Required if FilePath is not defined.
ContentType String False The type of file to attach.
IncludeOnline String False Sets an attachment to be included with the invoice when viewed online. Set false by default.
TenantId String False The ID of the tenant to query instead of the connection tenant
Result Set Columns
Name Type Description
Success String A boolean indicating if the operation was successful.
AttachmentId String The ID of the attachment.
FileName String The file name of the attachment.
URL String The URL of the attachment.
MimeType String The Internet media type of the attachment.
ContentLength String The size of the attachment in bytes.
IncludeOnline String A boolean indicating if IncludeOnline was set to true.

Australian Payroll Data Model

The Xero connector models the Xero Australian Payroll API as relational tables, Views, and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

However, note that deletes are not supported by the Australian Payroll API.

Tables

Tables describes the available tables.

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information

in the data source.

Tables

The connector models the data in Xero as a list of tables in a relational database that can be queried using standard SQL statements.

Xero Connector Tables
Name Description
Employees Query, insert and update employees for a Xero organisation.
LeaveApplications Query, insert and update Leave Applications for a Xero organisation.
PayItemsDeductions Query, insert and update PayItems for a Xero organisation.
PayItemsEarnings Query, insert and update PayItems for a Xero organisation.
PayItemsLeave Query, insert and update PayItems for a Xero organisation.
PayItemsReimbursements Query, insert and update PayItems for a Xero organisation.
PayrollCalendars Query, insert and update Payroll Calendars for a Xero organisation.
PayRuns Query, insert and update payruns for a Xero organisation.
PaySlipDeductions Query payslip deduction line-items for a Xero organisation.
PaySlipEarnings Query payslip earning line-items for a Xero organisation.
PaySlipLeaveAccrual Query payslip leave accrual line-items for a Xero organisation.
PaySlipReimbursements Query payslip reimbursements line-items for a Xero organisation.
PaySlipSuperannuations Query payslip superannuation line-items for a Xero organisation.
PaySlipTaxes Query payslip tax line-items for a Xero organisation.
PaySlipTimesheetEarnings Query payslip timesheet-earnings line-items for a Xero organisation.
SuperFunds Query, insert and update add and update Payroll Super Funds in a Xero organisation.
Timesheets Query, insert and update Timesheets for a Xero organisation.
Employees

Query, insert and update employees for a Xero organisation.

Columns
Name Type ReadOnly Filterable Description
EmployeeID [KEY] String True The unique identifier of the employee. This field is assigned by Xero.
Status String False The status of the employee. Valid values are ACTIVE and TERMINATED.
Title String False The title of the employee.
FirstName String False The first name of the employee.
MiddleNames String False The middle names of the employee.
LastName String False The last name of the employee.
DateOfBirth Date False The employee's birthday.
JobTitle String False The job title of the employee.
Gender String False The gender of the employee.
Phone String False The employee's home phone number.
Mobile String False The employee's mobile phone number.
Email String False The employee's email address.
StartDate Date False The employee's hire date.
TerminationDate Date False The employee's termination date.
TerminationReason String False The reasion for the eompleeoy's termination, as a one-letter code.
OrdinaryEarningsRateID String False A reference to the employee's pay schedule
PayrollCalendarID String False A reference to the employee's pay schedule
IsAuthorisedToApproveLeave Boolean False Whether the employee is authorised to approve timeoff.
IsAuthorisedToApproveTimesheets Boolean False Whether the employee is authorised to approve timesheets.
EmployeeGroupName String False The name of the tracking category the employee belongs to, if one is assigned.
UpdatedDateUTC Datetime True Timestamp of the last change to the employee record.
HomeAddress_AddressLine1 String False Address Line 1 for employee home address.
HomeAddress_AddressLine2 String False Address Line 2 for employee home address.
HomeAddress_City String False Suburb for employee home address.
HomeAddress_Region String False State abbreviation for employee home address.
HomeAddress_PostalCode String False PostalCode for employee home address.
HomeAddress_Country String False Country of HomeAddress.
TaxDeclaration_EmploymentBasis String False One of FULLTIME, PARTTIME, CASUAL, LABOURHIRE or SUPERINCOMESTREAM
TaxDeclaration_TFNExemptionType String False If the employee has no TFN, this may be one of NOTQUOTED, PENDING, PENSIONER or UNDER18
TaxDeclaration_TaxFileNumber String False The last three digits of the employee's TFN
TaxDeclaration_AustralianResidentForTaxPurposes Boolean False Whether the employee is an Australian resident for tax purposes
TaxDeclaration_ResidencyStatus String False One of AUSTRALIANRESIDENT, FOREIGNRESIDENT or WORKINGHOLIDAYMAKER
TaxDeclaration_TaxFreeThresholdClaimed Boolean False Whether the employee claims to make below the tax free threshold
TaxDeclaration_TaxOffsetEstimatedAmount Decimal False Any other tax offsets the employee has claimed
TaxDeclaration_HasHELPDebt Boolean False Whether the employee has HECS or HELP debt
TaxDeclaration_HasSFSSDebt Boolean False Whether the employee has financial supplement debt
TaxDeclaration_HasTradeSupportLoanDebt Boolean False Whether the employee has a trade support loan
TaxDeclaration_UpwardVariationTaxWithholdingAmount Decimal False The extra withholding the employee has requested, in percent
TaxDeclaration_EligibleToReceiveLeaveLoading Boolean False If the employee is eligible to receive extra earnings when they take leave
TaxDeclaration_ApprovedWithholdingVariationPercentage Decimal False The extra withholding the employee has been granted, in percent
BankAccounts String True The Bank accounts for the employee in XML format.
PayTemplate String True The pay template for the employee in XML format.
OpeningBalances String True The opening balances for the employee in XML format.
LeaveBalances String True The leave balances for the empoloyee in XML format.
SuperMemberships String True The Super memberships for the employee in XML format.
TenantId String False The ID of the tenant to query instead of the connection tenant
LeaveApplications

Query, insert and update Leave Applications for a Xero organisation.

Table Specific Information

The Timesheets table allows you to SELECT and INSERT leave applications for a Xero organization. The ID column is generated by the connector; the value of this field combines the index of the line item with the unique, Xero-generated LeaveApplicationID.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new leave application, the EmployeeID, LeaveTypeID, Title, StartDate and EndDate are required. In addition leave periods may also be provided.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

  1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the Line columns with the LeavePeriods_LeavePeriod_ prefix removed.

    INSERT INTO LeaveApplications (EmployeeID, LeaveTypeID, Title, StartDate, EndDate, LeavePeriodAggregate) VALUES (
      'XXXXX-XXXXX-XXXXX-XXXXX',
      'YYYYY-YYYYY-YYYYY-YYYYY',
      'Visit relatives',
      '2021-01-01',
      '2021-01-07',
      '<LeavePeriod>
        <NumberOfUnits>8</NumberOfUnits>
       </LeavePeriod>
       <LeavePeriod>
        <NumberOfUnits>32</NumberOfUnits>
       </LeavePeriod>'
    )
    
  2. You can insert a new period on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a period to an existing leave application:

    INSERT INTO LeaveApplications (LeaveApplicationID, LeavePeriods_LeavePeriod_NumberOfUnits) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 32)
    

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. DELETE

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

Columns
Name Type ReadOnly Filterable Description
Id [KEY] String True The leave period line-item combined with the unique Xero identifier of the leave application.
LeaveApplicationID String True The unique identifier of the Leave Application. This field is assigned by Xero.
EmployeeID String False The Xero identifier for Payroll Employee.
LeaveTypeID String False The Xero identifier for Leave Type.
Title String False The title of the leave (max length = 50).
StartDate String False Start date of the leave (YYYY-MM-DD).
EndDate String False End date of the leave (YYYY-MM-DD).
Description String False The Description of the Leave (max length = 200).
LeavePeriodAggregate String False Used to define LeavePeriod rows using XML values. Should be provided on INSERT only.
LeavePeriods_LeavePeriod_PayPeriodStartDate String False The start of the payperiod the leave is being drawn from
LeavePeriods_LeavePeriod_PayPeriodEndDate String False The end of the payperiod the leave is being drawn from
LeavePeriods_LeavePeriod_LeavePeriodStatus String False Either SCHEDULED or PROCESSED
LeavePeriods_LeavePeriod_NumberOfUnits String False Amount of leave consumed from this payperiod
TenantId String False The ID of the tenant to query instead of the connection tenant
PayItemsDeductions

Query, insert and update PayItems for a Xero organisation.

Columns
Name Type ReadOnly Filterable Description
DeductionTypeID [KEY] String True The unique identifier of the PayItem. This field is assigned by Xero.
Name String False Name of the deduction type.
DeductionCategory String False Category of the deduction.
AccountCode String False Customer defined alpha numeric account code.
ReducesTax String False Indicates that this is a pre-tax deduction.
ReducesSuper String False Indicates that this reduces your superannuation guarantee contribution liability.
UpdatedDateUTC Datetime True Timestamp of the last change to the PayItem record.
TenantId String False The ID of the tenant to query instead of the connection tenant
PayItemsEarnings

Query, insert and update PayItems for a Xero organisation.

Columns
Name Type ReadOnly Filterable Description
EarningsRateID [KEY] String True The unique identifier of the PayItem. This field is assigned by Xero.
Name String False Name of the earnings rate.
DisplayName String False Display name of the earnings rate, as it will appear on payslips.
AccountCode String False Customer defined alpha numeric account code.
TypeOfUnits String False Type of units used to record earnings.
IsExemptFromTax String False You should only set this value if you are sure that a payment is exempt from PAYG withholding.
IsExemptFromSuper String False See the ATO website for details of which payments are exempt from SGC.
IsReportableAsW1 Bool False Whether the earnings rate is subject to W1 witholding.
EarningsType String False The type of Pay Items earning. Valid values are FIXED, ORDINARYTIMEEARNINGS, OVERATIMEEARNINGS, ALLOWANCE and LUMPSUMD.
AllowanceType String False When EarningsType is ALLOWANCE, this reports what the allowance is paid for.
AllowanceCategory String False Determines the kind of allowance when AllowanceType is OTHER.
RateType String False The rate type of the earning. Valid values are FIXEDAMOUNT, MULTIPLE, RATEPERUNIT
RatePerUnit String False Only applicable if RateType is RATEPERUNIT.
Multiplier Double False This is the multiplier used to calculate the rate per unit, based on the employee's ordinary earnings type. Only applicable if RateType is MULTIPLE.
AccrueLeave String False Indicates that this earnings rate should accrue leave. Only applicable if RateType is MULTIPLE.
Amount String False Option Amount for FIXEDAMOUNT RateType EarningsRate.
CurrentRecord Bool False Wehther the earnings rate may still be used.
UpdatedDateUTC Datetime True Timestamp of the last change to the PayItem record.
TenantId String False The ID of the tenant to query instead of the connection tenant
PayItemsLeave

Query, insert and update PayItems for a Xero organisation.

Columns
Name Type ReadOnly Filterable Description
LeaveTypeID [KEY] String True The unique identifier of the PayItem. This field is assigned by Xero.
Name String False Name of the leave type.
TypeOfUnits String False The type of units by which leave entitlements are normally tracked.
IsPaidLeave String False Set this to indicate that an employee will be paid when taking this type of leave.
ShowOnPaySlip String False Set this if you want a balance for this leave type to be shown on your employee's payslips.
NormalEntitlement String False The number of units the employee is entitled to each year.
LeaveLoadingRate String False Enter an amount here if your organisation pays an additional percentage on top of ordinary earnings when your employees take leave (typically 17.5%).
UpdatedDateUTC String False The Date time this row was last updated.
TenantId String False The ID of the tenant to query instead of the connection tenant
PayItemsReimbursements

Query, insert and update PayItems for a Xero organisation.

Columns
Name Type ReadOnly Filterable Description
ReimbursementTypeID [KEY] String True The unique identifier of the PayItem. This field is assigned by Xero.
Name String False Name of the reimbursement type.
AccountCode String False Customer defined alpha numeric account code.
UpdatedDateUTC Datetime True Timestamp of the last change to the PayItem record.
TenantId String False The ID of the tenant to query instead of the connection tenant
PayrollCalendars

Query, insert and update Payroll Calendars for a Xero organisation.

Columns
Name Type ReadOnly Filterable Description
PayrollCalendarID [KEY] String True The unique identifier of the PaySchedule. This field is assigned by Xero.
Name String False The name of the Payroll Calendar.
CalendarType String False The type of payroll calendar.
StartDate String False The Start date of the upcoming pay period.
PaymentDate String False The date on which employees will be paid for the upcomming pay period.
TenantId String False The ID of the tenant to query instead of the connection tenant
PayRuns

Query, insert and update payruns for a Xero organisation.

Columns
Name Type ReadOnly Filterable Description
PayRunID [KEY] String True The unique identifier of the payrun. This field is assigned by Xero.
PayrollCalendarID String False The status of the payrun. Valid values are ACTIVE and DELETED.
PayRunPeriodStartDate String False Period Start Date for the PayRun.
PayRunPeriodEndDate String False Period End Date for the PayRun.
PayRunStatus String False The status of the payrun.
PaymentDate String False Payment Date for the PayRun.
PayslipMessage String False Payslip message for the PayRun.
Wages Decimal False Total Wages for the PayRun.
Deductions Decimal False Total Deduction for the PayRun.
Tax Decimal False Total Tax for the PayRun.
Super Decimal False Total Super for the PayRun.
Reimbursement Decimal False Total Reimbursement for the PayRun.
NetPay Decimal False Total NetPay for the PayRun.
UpdatedDateUTC Datetime True Timestamp of the last change to the payrun record.
Payslip String False The payslip for this pay run in XML format.
TenantId String False The ID of the tenant to query instead of the connection tenant
PaySlipDeductions

Query payslip deduction line-items for a Xero organisation.

Table Specific Information

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The connector adds a new deduction line to the payslip.

INSERT INTO PaySlipDeductions (PayslipID, DeductionLine_DeductionTypeId, DeductionLine_CalculationType, DeductionLine_Amount) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', 'b78b5ddd-6a20-4992-8e77-1243a122265d', 'FIXEDAMOUNT', 50)

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

UPDATE PaySlipDeductions SET DeductionLine_Amount=5 WHERE Id='2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the ID to delete payslip deduction lines.

DELETE FROM PaySlipDeductions WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'
Columns
Name Type ReadOnly Filterable Description
Id [KEY] String True The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID String False The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID String True The unique identifier of the payslip's employee.
DeductionLine_DeductionTypeId String False The unique identifier of the line-item's deduction type
DeductionLine_CalculationType String False How the deduction is applied to earnings, either PRETAX, POSTTAX or FIXEDAMOUNT
DeductionLine_Amount Decimal False The total amount of the deduction
DeductionLine_Percentage Double False The percentage of income this deduction applies to
TenantId String False The ID of the tenant to query instead of the connection tenant
PaySlipEarnings

Query payslip earning line-items for a Xero organisation.

Table Specific Information

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The connector adds a new earnings line to the payslip.

INSERT INTO PaySlipEarnings (PaySlipID, EarningsLine_EarningsRateId, EarningsLine_RatePerUnit, EarningsLine_NumberOfUnits) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', 'fc394b8d-ff2e-4f10-992b-2a41390ad2c4', 4, 20)

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

UPDATE PaySlipEarnings SET EarningsLine_RatePerUnit = 5 WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the ID to delete payslip earnings lines.

DELETE FROM PaySlipEarnings WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'
Columns
Name Type ReadOnly Filterable Description
Id [KEY] String True The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID String False The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID String True The unique identifier of the payslip's employee.
EarningsLine_EarningsRateId String False The unique identifier of the line-item's earnings rate
EarningsLine_RatePerUnit Decimal False The line-item's wage per time unit (e.g. hours)
EarningsLine_NumberOfUnits Double False The number of time units in this line-item (e.g. hours)
EarningsLine_FixedAmount Decimal False The total earnings in this line-item. Only appears if the rate-type is FIXED.
EarningsLine_LumpSumETaxYear String False The tax year that applies to these earnings. Only appears if the earnings type is LUMPSUME.
TenantId String False The ID of the tenant to query instead of the connection tenant
PaySlipLeaveAccrual

Query payslip leave accrual line-items for a Xero organisation.

Table Specific Information

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The connector adds a new leave accrual line to the payslip.

INSERT INTO PaySlipLeaveAccrual (PaySlipID, LeaveAccrualLine_LeaveTypeId, LeaveAccrualLine_NumberOfUnits, LeaveAccrualLine_AutoCalculate) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', '73f37030-b1ed-45fe-b7a2-f704a3a28ad4', 8, false)

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

UPDATE PaySlipLeaveAccrual SET LeaveAccrualLine_NumberOfUnits = 5 WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the ID to delete leave accrual lines.

DELETE FROM PaySlipLeaveAccrual WHERE Id = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'
PaySlipReimbursements
Columns
Name Type ReadOnly Filterable Description
Id [KEY] String True The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID String False The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID String True The unique identifier of the payslip's employee.
LeaveAccrualLine_LeaveTypeId String False The unique identifier of the line-item's leave type
LeaveAccrualLine_NumberOfUnits Double False How much leave time was added
LeaveAccrualLine_AutoCalculate String False Whether or not to automatically calculate leave
TenantId String False The ID of the tenant to query instead of the connection tenant
PaySlipReimbursements

Query payslip reimbursements line-items for a Xero organisation.

Table Specific Information

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The connector adds a new reimbursement line to the payslip.

INSERT INTO PaySlipReimbursements (PaySlipID, ReimbursementLine_ReimbursementTypeId, ReimbursementLine_Amount) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', '3432120f-4d83-4bc2-8b5d-5a116838229e', 5)

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

UPDATE PaySlipReimbursements SET ReimbursementLine_Amount = 6 WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the ID to delete payslip reimbursement lines.

DELETE FROM PaySlipReimbursements WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'
Columns
Name Type ReadOnly Filterable Description
Id [KEY] String True The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID String False The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID String True The unique identifier of the payslip's employee.
ReimbursementLine_ReimbursementTypeId String False The unique identifier of the line-item's reimbursement type
ReimbursementLine_Description String False A description of the reimbursement
ReimbursementLine_ExpenseAccount String False The account the reimbursement is paid from
ReimbursementLine_Amount Decimal False The amount of the reimbursement
TenantId String False The ID of the tenant to query instead of the connection tenant
PaySlipSuperannuations

Query payslip superannuation line-items for a Xero organisation.

Table Specific Information

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The connector adds a new superannuation line to the payslip.

INSERT INTO PaySlipSuperannuations (PayslipID, SuperannuationLine_SuperMembershipId, SuperannuationLine_ContributionType, SuperannuationLine_CalculationType, SuperannuationLine_Amount, SuperannuationLine_ExpenseAccountCode, SuperannuationLine_LiabilityAccountCode, SuperannuationLine_Percentage, SuperannuationLine_PaymentDateForThisPeriod) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', '045ef1ba-1399-4b49-b324-8c8b70d05607', 'SGC', 'PERCENTAGEOFEARNINGS', 9, '478', '826', 9.0, '2023-06-28')

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

UPDATE PaySlipSuperannuations SET SuperannuationLine_Percentage = 10 WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the ID to delete payslip superannuation lines.

DELETE FROM PaySlipSuperannuations WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'
Columns
Name Type ReadOnly Filterable Description
Id [KEY] String True The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID String False The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID String True The unique identifier of the payslip's employee.
SuperannuationLine_SuperMembershipId String False The unique identifier of the line-item's super fund.
SuperannuationLine_ContributionType String False The super contribution type
SuperannuationLine_CalculationType String False The super calculation type
SuperannuationLine_MinimumMonthlyEarnings Decimal False The super minimum monthly earnings
SuperannuationLine_ExpenseAccountCode String False The super expense account code
SuperannuationLine_LiabilityAccountCode String False The super liability account code
SuperannuationLine_PaymentDateForThisPeriod Date False The super payment date for this period
SuperannuationLine_Percentage Double False The super percentage
SuperannuationLine_Amount Decimal False The super amount
TenantId String False The ID of the tenant to query instead of the connection tenant
PaySlipTaxes

Query payslip tax line-items for a Xero organisation.

Table Specific Information

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The connector adds a new tax line to the payslip.

INSERT INTO PaySlipTaxes (PayslipID, TaxLine_Amount) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', 5)

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

UPDATE PaySlipTaxes SET TaxLine_Amount = 10 WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the ID to delete payslip tax lines.

DELETE FROM PaySlipTaxes WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'
Columns
Name Type ReadOnly Filterable Description
Id [KEY] String True The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID String False The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID String True The unique identifier of the payslip's employee.
TaxLine_TaxTypeName String False The name of the line-item's tax type
TaxLine_Description String False The description of the tax
TaxLine_Amount Decimal False The tax's amount
TaxLine_LiabilityAccount String False The liability account used to pay the tax
TenantId String False The ID of the tenant to query instead of the connection tenant
PaySlipTimesheetEarnings

Query payslip timesheet-earnings line-items for a Xero organisation.

Table Specific Information

This table supports the following operators server-side: =,IN

INSERT

To insert a single row, set the required fields. The connector adds a new timesheet earnings line to the payslip.

INSERT INTO PaySlipTimesheetEarnings (PayslipID, TimesheetEarningsLine_EarningsRateId, TimesheetEarningsLine_RatePerUnit, TimesheetEarningsLine_NumberOfUnits) VALUES ('ea5aaaa7-c330-41d8-bfae-98ddc1f37680', 'c8602c7c-5f6f-433e-b7a7-bcbbd8386818', 32, 10)

UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

UPDATE PaySlipTimesheetEarnings SET TimesheetEarningsLine_NumberOfUnits = 5 WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'

DELETE

Supply the ID to delete timesheet earnings lines.

DELETE FROM PaySlipTimesheetEarnings WHERE ID = '2|ea5aaaa7-c330-41d8-bfae-98ddc1f37680'
Columns
Name Type ReadOnly Filterable Description
Id [KEY] String True The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID String False The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID String True The unique identifier of the payslip's employee.
TimesheetEarningsLine_EarningsRateId String False The unique identifier of the line-item's earnings rate
TimesheetEarningsLine_RatePerUnit Decimal False The line-item's rate per time unit (e.g. hours)
TimesheetEarningsLine_NumberOfUnits Decimal False The number of time units (e.g. hours)
TenantId String False The ID of the tenant to query instead of the connection tenant
SuperFunds

Query, insert and update add and update Payroll Super Funds in a Xero organisation.

Columns
Name Type ReadOnly Filterable Description
SuperFundID [KEY] String True Xero Identifier.
Type String True REGULATED, or SMSF.
Name String False Name of the Super fund.
ABN String False ABN of the Super Fund.
USI String False USI of the Regulated Super Fund.
BSB String False BSB of the Self Managed Super Fund.
AccountNumber String False The account number for the self managed super fund.
AccountName String False The account name for the self managed super fund.
ElectronicServiceAddress String False The electronic service address for the self managed super fund.
EmployerNumber String False Some funds assign a unique number to each employer.
SPIN String False The SPIN of the Regulated SuperFund (deprecated).
UpdatedDateUTC String False The date of the last update to this row
TenantId String False The ID of the tenant to query instead of the connection tenant
Timesheets

Query, insert and update Timesheets for a Xero organisation.

Table Specific Information

The Timesheets table allows you to SELECT, INSERT and UPDATE timesheet lines for a Xero organization. The ID column is generated by the connector; the value of this field combines the index of the line item with the unique, Xero-generated TimesheetID.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines. INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new timesheet, the EmployeeID, StartDate and EndDate fields are required.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

  1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the Line columns with the Line_ prefix removed, except when providing numbers of units. See the Xero documentation for the Timesheets API endpoint for more details.

    INSERT INTO Timesheets (EmployeeID, StartDate, EndDate, TimesheetLineAggregate) VALUES (
      'XXXXX-XXXXX-XXXXX-XXXXX',
      '2021-01-01',
      '2021-01-07',
      '<TimesheetLine>
        <EarningsRateID>YYYYY-YYYYY-YYYYY-YYYYY</EarningsRateID>
        <NumberOfUnits>
         <NumberOfUnit>8</NumberOfUnit>
         <NumberOfUnit>7</NumberOfUnit>
         <NumberOfUnit>8</NumberOfUnit>
         <NumberOfUnit>6</NumberOfUnit>
         <NumberOfUnit>9</NumberOfUnit>
       </TimesheetLine>
       <TimesheetLine>
        <EarningsRateID>ZZZZZ-ZZZZZ-ZZZZZ-ZZZZZ</EarningsRateID>
        ...
       </TimesheetLine>'
    )
    
  2. You can insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing timesheet:

    INSERT INTO Timesheets (TimesheetID, Line_EarningsRateID, Line_NumberOfUnits_NumberOfUnit1, Line_NumberOfUnits_NumberOfUnit2, ...) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 8, 7, ...)
    

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. UPDATE

You can update any field that is not read-only.

Note: UPDATE operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data. DELETE

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

Columns
Name Type ReadOnly Filterable Description
Id [KEY] String True The timesheet line-item combined with the unique Xero identifier of the timesheet.
TimesheetID String True The unique identifier of the Timesheet. This field is assigned by Xero.
EmployeeID String False The Xero identifier for an employee.
StartDate Date False Period start date.
EndDate Date False Period end date.
Status String False The status code for the timesheet. The valid values are DRAFT, PROCESSED, and APPROVED.
Hours Double False Timesheet total hours.
Line_EarningsRateId String False The Xero identifier for an Earnings Type.
Line_TrackingItemId String False The Xero identifier for a Tracking Category TrackingOptionID. The TrackingOptionID must belong to the TrackingCategory selected as TimesheetCategories under Payroll Settings.
Line_WorkLocationId String False The Xero identifier for a Work Location.
Line_NumberOfUnits_NumberOfUnit1 String False Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit2 String False Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit3 String False Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit4 String False Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit5 String False Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit6 String False Number of units of a Timesheet line.
Line_NumberOfUnits_NumberOfUnit7 String False Number of units of a Timesheet line.
Line_UpdatedDateUTC Datetime True Timestamp of the last change to the Timesheet Line record.
TimesheetLineAggregate String False Used to define TimesheetLine rows using XML values. Should be provided on INSERT only.
TenantId String False The ID of the tenant to query instead of the connection tenant

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Xero Connector Views
Name Description
EmployeeBankAccounts Query the bank accounts for an Employee in a Xero organisation.
EmployeePayTemplateDeductions Query the pay template deduction lines for an Employee in a Xero organisation.
EmployeePayTemplateEarnings Query the pay template earnings lines for an Employee in a Xero organisation.
EmployeePayTemplateLeave Query the pay template leave lines for an Employee in a Xero organisation.
EmployeePayTemplateReimbursements Query the pay template reimbursement lines for an Employee in a Xero organisation.
EmployeePayTemplateSupers Query the pay template super lines for an Employee in a Xero organisation.
LeaveBalances Query the Leave Balance for an Employee in a Xero organisation.
PaySlipLeaveEarnings Query payslip leave earnings line-items for a Xero organisation.
PaySlips Query payslips for a Xero organisation.
Tenants Query the list of organisations connected to your Xero account.
EmployeeBankAccounts

Query the bank accounts for an Employee in a Xero organisation.

Columns
Name Type Filterable Description
EmployeeID String The ID of the Employee. This field is assigned by Xero.
StatementText String The text that will appear on the employee's bank statement
AccountName String The name of the account
BSB String The BSB number of the account
AccountNumber String The account number
Remainder Boolean If the remaining part of the employee's salary should be transferred to this account
Amount Decimal A fixed amount of the employee's salary which is transferred to this account
TenantId String The ID of the tenant to query instead of the connection tenant
EmployeePayTemplateDeductions

Query the pay template deduction lines for an Employee in a Xero organisation.

Columns
Name Type Filterable Description
EmployeeID String The ID of the Employee. This field is assigned by Xero.
DeductionTypeID String The ID of the Deductions Rate used for this line item
CalculationType String One of FIXEDAMOUNT, PRETAX or POSTTAX
Percentage Decimal The deduction rate, for PRETAX or POSTTAX deductions
Amount Decimal The deduction amount, for FIXEDAMOUNT deductions
TenantId String The ID of the tenant to query instead of the connection tenant
EmployeePayTemplateEarnings

Query the pay template earnings lines for an Employee in a Xero organisation.

Columns
Name Type Filterable Description
EmployeeID String The ID of the Employee. This field is assigned by Xero.
EarningsRateID String The ID of the Earnings Rate used for this line item
CalculationType String One of USEEARNINGSRATE, ENTEREARNINGSRATE or ANNUALSALARY
NumberOfUnitsPerWeek Int How many hours the employee works per week. Only used for ANNUALSALARY.
AnnualSalary Decimal The annual salary of the employee
RatePerUnit Decimal The rate per unit of the earnings line
NormalNumberOfUnits Decimal The units of time in the earnings line
TenantId String The ID of the tenant to query instead of the connection tenant
EmployeePayTemplateLeave

Query the pay template leave lines for an Employee in a Xero organisation.

Columns
Name Type Filterable Description
EmployeeID String The ID of the Employee. This field is assigned by Xero.
LeaveTypeID String The ID of the leave type applied to this line
CalculationType String One of FIXEDAMOUNTEACHPERIOD, ENTIRERATEINPAYTEMPLATE or BASEDONORDINARYEARNINGS
AnnualNumberOfUnits Decimal The hours of leave accrued each year
FullTimeNumberOfUnitsPerPeriod Decimal The hours of leave accrued per pay period
NumberOfUnits Decimal A fixed amount of leave
EntitlementFinalPayPayoutType String One of NOTPAIDOUT or PAIDOUT
EmploymentTerminationPaymentType String One of O or R, only valid if EntitlementFinalPayPaoutType is PAIDOUT
IncludeSuperannuationGuaranteeContribution Boolean Whether ETP leave earnings are subject to SGC
TenantId String The ID of the tenant to query instead of the connection tenant
EmployeePayTemplateReimbursements

Query the pay template reimbursement lines for an Employee in a Xero organisation.

Columns
Name Type Filterable Description
EmployeeID String The ID of the Employee. This field is assigned by Xero.
ReimbursementTypeID String The ID of the reimbursement type applied to this line
Description String The description of the reimbursement line
Amount Decimal The amount of the reimbursement line
TenantId String The ID of the tenant to query instead of the connection tenant
EmployeePayTemplateSupers

Query the pay template super lines for an Employee in a Xero organisation.

Columns
Name Type Filterable Description
EmployeeID String The ID of the Employee. This field is assigned by Xero.
SuperMembershipID String The ID of the superannuation fund this line contributes to
ContributionType String One of SGC, SALARYSACRIFICE, EMPLOYERADDITIONAL or EMPLOYEE
CalculationType String One of FIXEDAMOUNT, PERCENTAGEOFEARNINGS or STATUTORY
ExpenseAccountCode String The code for the expense account used by this line
LiabilityAccountCode String The code for the expense account used by this line
MinimumMonthlyEarnings Decimal The minimum the employee must make in a month for this line to take effect
Percentage Decimal The percentage of the super line
TenantId String The ID of the tenant to query instead of the connection tenant
LeaveBalances

Query the Leave Balance for an Employee in a Xero organisation.

Columns
Name Type Filterable Description
EmployeeID [KEY] String ID of the employee
LeaveTypeID [KEY] String ID of the leave type
LeaveName String The name of the leave type
NumberOfUnits Double The balance of the leave available
TypeOfUnits String The type of units
TenantId String The ID of the tenant to query instead of the connection tenant
PaySlipLeaveEarnings

Query payslip leave earnings line-items for a Xero organisation.

Columns
Name Type Filterable Description
Id [KEY] String The earnings line-item combined with the unique Xero identifier of the payslip
PayslipID String The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID String The unique identifier of the payslip's employee.
LeaveEarningsLine_EarningsRateId String The unique identifier of the line-item's earnings rate
LeaveEarningsLine_RatePerUnit Decimal The line-item's hourly wage
LeaveEarningsLine_NumberOfUnits Double The number of hours in this line-item
TenantId String The ID of the tenant to query instead of the connection tenant
PaySlips

Query payslips for a Xero organisation.

Columns
Name Type Filterable Description
PayRunID String The ID of the payrun that contains the payslips
PayslipID [KEY] String The unique identifier of the payslip. This field is assigned by Xero.
EmployeeID String The unique identifier of the payslip's employee.
FirstName String The employee's first name.
LastName String The employee's first name.
EmployeeGroup String The name of the group the employee belongs to.
LastEdited Datetime When this payslip was last changed.
Wages Decimal The total wages included in the payslip.
Deductions Decimal The total deductions included in the payslip.
NetPay Decimal The total net pay included in the paysilp.
Tax Decimal The total tax paid included in the payslip.
Super Decimal The total superannuation included in the paysilp.
Reimbursements Decimal The total reimbursement included in the payslip.
TenantId String The ID of the tenant to query instead of the connection tenant
Tenants

Query the list of organisations connected to your Xero account.

Columns
Name Type Filterable Description
TenantId [KEY] String A unique identifier for the tenant.
Name String The name of the organisation or practice
Type String Whether the tenant is an organisation or a practice
IsActive Boolean Whether the connection is currently using this tenant

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Xero.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Xero, along with an indication of whether the procedure succeeded or failed.

Xero Connector Stored Procedures
Name Description
GetLimitInfo Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL Obtains the OAuth authorization URL for authentication with various Xero services.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SwitchTenant Changes the tenant this connection fetches data from.
GetLimitInfo

Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.

If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:

SELECT * FROM PayRuns LIMIT 1

Result Set Columns

Name Type Description
TenantId String ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining Integer The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining Integer The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining Integer The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter Integer The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified Datetime When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.
GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input
Name Type Required Description
AuthMode String True The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken String False The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey String False The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL String False This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier String False The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier String False The PKCEVerifier returned by GetOAuthAuthorizationURL.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret String The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input
Name Type Required Description
CallbackURL String False This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope String False The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State String False A state token to include in the authorization URL. Not included by default.
Result Set Columns
Name Type Description
URL String The URL to complete user authentication.
AuthToken String A token used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
AuthKey String A key used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
PKCEVerifier String A random value used as input for GetOAuthAccessToken in the PKCE flow.
RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input
Name Type Required Description
OAuthRefreshToken String True The refresh token returned from the original authorization code exchange.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input
Name Type Required Description
TenantId String False The ID of the tenant to revoke, as given in the Tenants view
Result Set Columns
Name Type Description
Success String Whether the connection was successfully revoked
SwitchTenant

Changes the tenant this connection fetches data from.

Input
Name Type Required Description
TenantId String False The ID of the tenant to attach to. If empty, the current tenant is not changed.
Result Set Columns
Name Type Description
ActiveTenant String The ID of the tenant that is currently active.

Files Data Model

The Xero connector models the Xero Files API as relational tables and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information

in the data source.

Tables

The connector models the data in Xero as a list of tables in a relational database that can be queried using standard SQL statements.

Xero Connector Tables
Name Description
Associations Query, insert and delete Associations for a Xero organization.
Files Query, update and delete Files for a Xero organization.
Folders Query, insert, update and delete Folders for a Xero organization.
Associations

Query, insert and delete Associations for a Xero organization.

Table-Specific Information

To create an Association, everything but the ID and ObjectType must be provided:

INSERT INTO Associations (FileId, ObjectId, ObjectGroup)
VALUES ('2bf7b85c-7ed8-47b4-8559-d83f8f5f9b9c', '4819be2c-b3b7-43a2-80fe-ef479ad25351', 'Invoice')

Associations cannot be changed once they have been inserted. If you need to update an Association you must delete it and recreate it.

Columns
Name Type ReadOnly Description
Id [KEY] String False A unique combination of the file and object being associated
FileId String False The FileId of the file that is being associated
ObjectId String False The identifier of the object that is being associated to
ObjectGroup String False The Accounting entity to which the object should be attached
ObjectType String True A more specific category that the object belongs to
TenantId String False The ID of the tenant to query instead of the connection tenant
Files

Query, update and delete Files for a Xero organization.

Table-Specific Information

If you need to interact with file contents (either uploading or downloading them), please see the Files stored procedures.

Columns
Name Type ReadOnly Description
FileId [KEY] String True The unique Xero-generated ID of the file
Name String False The name of the file
FolderId String False The FolderId of the folder containing the file
MimeType String True What type of data the file contains
Size Int True The size of the file in bytes
User_Id String True The unique ID of the user who uploaded the file
User_Name String True The username of the user who uploaded the file
User_FirstName String True The first name of the user who uploaded the file
User_LastName String True The last name of the user who uploaded the file
User_FullName String True The full name of the user who uploaded the file
Created Datetime True When the file was first uploaded
Updated Datetime True When the file metadata was last changed
TenantId String False The ID of the tenant to query instead of the connection tenant
Folders

Query, insert, update and delete Folders for a Xero organization.

Table-Specific Information

A Name has to be provided when creating a Folder:

INSERT INTO Folders (Name) VALUE ('Legal Documents')
Columns
Name Type ReadOnly Description
FolderId [KEY] String True The unique Xero-generated ID of the folder
Name String False The name of the folder
FileCount Integer True How many files exist in the folder
IsInbox Boolean True Whether this folder is the inbox. The inbox is automatically created by Xero and cannot be changed or deleted.
Email String True The email address that accepts files and uploads them to this folder. Only available for the Inbox.
TenantId String False The ID of the tenant to query instead of the connection tenant

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Xero Connector Views
Name Description
Tenants Query the list of organizations connected to your Xero account.
Tenants

Query the list of organizations connected to your Xero account.

Columns
Name Type Description
TenantId [KEY] String A unique identifier for the tenant.
Name String The name of the organization or practice
Type String Whether the tenant is an organization or a practice
IsActive Boolean Whether the connection is currently using this tenant

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Xero.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Xero, along with an indication of whether the procedure succeeded or failed.

Xero Connector Stored Procedures
Name Description
DownloadFile Downloads a file from the Xero Files service
GetLimitInfo Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL Obtains the OAuth authorization URL for authentication with various Xero services.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SwitchTenant Changes the tenant this connection fetches data from.
UploadFile Uploads a file to the Xero Files service
DownloadFile

Downloads a file from the Xero Files service

Procedure-Specific Information

There are two different ways to download a file. If you provide a DownloadPath, then the file is downloaded and its contents are stored at that location:

EXECUTE DownloadFile @FileID = '...', @DownloadPath = 'c:/my/xero/files/form.docx'

If you do not provide a DownloadPath, then the contents of the file are base64 encoded and returned in the FileData output:

EXECUTE DownloadFile @FileID = '...'

Input

Name Type Required Description
FileId String True The ID of the file to download
DownloadPath String False Where to write the downloaded file.
TenantId String False The ID of the tenant to query instead of the connection tenant

Result Set Columns

Name Type Description
FileData String If the DownloadPath and FileStream are not provided, this contains the base64 encoded content of the file.
Success String This parameter sets whether the operation was successful or not.
GetLimitInfo

Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.

If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:

SELECT * FROM Files LIMIT 1

Result Set Columns

Name Type Description
TenantId String ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining Integer The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining Integer The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining Integer The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter Integer The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified Datetime When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.
GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input
Name Type Required Description
AuthMode String True The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken String False The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey String False The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL String False This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier String False The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier String False The PKCEVerifier returned by GetOAuthAuthorizationURL.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret String The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input
Name Type Required Description
CallbackURL String False This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope String False The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State String False A state token to include in the authorization URL. Not included by default.
Result Set Columns
Name Type Description
URL String The URL to complete user authentication.
AuthToken String A token used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
AuthKey String A key used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
PKCEVerifier String A random value used as input for GetOAuthAccessToken in the PKCE flow.
RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input
Name Type Required Description
OAuthRefreshToken String True The refresh token returned from the original authorization code exchange.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input
Name Type Required Description
TenantId String False The ID of the tenant to revoke, as given in the Tenants view
Result Set Columns
Name Type Description
Success String Whether the connection was successfully revoked
SwitchTenant

Changes the tenant this connection fetches data from.

Input
Name Type Required Description
TenantId String False The ID of the tenant to attach to. If empty, the current tenant is not changed.
Result Set Columns
Name Type Description
ActiveTenant String The ID of the tenant that is currently active.
UploadFile

Uploads a file to the Xero Files service

Procedure-Specific Information

There are a few different ways to upload a file:

1. Provide the full path to a local file. This will create a file with the same name and content in Xero:

EXECUTE UploadFile @Filename = 'c:/my/xero/files/form.docx

2. Provide the full path to a local file as well as a Name. This will still upload the content in the file, but lets you change the name of the file in Xero:

EXECUTE UploadFile @Filename = 'c:/my/xero/files/form.docx', @Name = 'legal-form.docx'

3. Provide the file's content in base64 and the name the file should receive in Xero:

EXECUTE UploadFile @Name = hello.txt', @FileData = 'SGVsbG8sIFdvcmxkIQo='

In addition, you can provide a ContentType to override what type Xero should use when classifying the file. Please note however that Xero only supports a limited number of MIME types, which are listed in the Xero Central article on managing the file library.

Input

Name Type Required Description
Name String False The name the file will receive when uploaded. If not given, it will be determined from Filename.
ContentType String False This parameter explicitly sets the MIME type for the document. If left empty, it will be determined automatically.
FileData String False Base-64 encoded data for the file. Required if Filename and FileStream are not defined.
Filename String False The full path (including filename) of the file to be uploaded. Required if FileData and FileStream are not defined.
TenantId String False The ID of the tenant to query instead of the connection tenant

Result Set Columns

Name Type Description
Success String Whether the operation was successful or not.
FileId String The unique Xero-generated ID of the file
Name String The name of the uploaded file
FolderId String The FolderId the file was uploaded to
MimeType String What type of data the uploaded file contains
Size Int The size of the uploaded file in bytes
User_Id String The unique ID of the user who uploaded the file
User_Name String The username of the user who uploaded the file
User_FirstName String The first name of the user who uploaded the file
User_LastName String The last name of the user who uploaded the file
User_FullName String The full name of the user who uploaded the file
Created Datetime When the file was first uploaded
Updated Datetime When the file was last changed

Fixed Assets Data Model

The Xero connector models the Xero Assets API as relational tables, views and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Views

Views describes the available views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information

in the data source.

Tables

The connector models the data in Xero as a list of tables in a relational database that can be queried using standard SQL statements.

Xero Connector Tables
Name Description
Assets Query, insert and update assets for a Xero organization.
AssetTypes Query, insert and update asset types for a Xero organization.
Assets

Query, insert and update assets for a Xero organization.

Table-Specific Information

Creating Assets

An asset can be created with only an AssetName and an AssetNumber. This will create a draft asset with blank settings for purchase date and price, serial number and all other values.

INSERT INTO Assets (AssetName, AssetNumber) VALUES ('Corporate van', 'FA-0042')

Updating Assets

Like asset types, an asset's value can be measured with either a depreciation rate or an estimated lifetime. Once these values have been set, you can only change between the two by setting the other to null:

UPDATE Assets
SET BookDepreciationSetting_DepreciationRate = 0.12,
    BookDepreciationSetting_EffectiveLifeYears = null
WHERE AssetId = '46c7f1f4-7cdb-47d3-8772-2796d0b83e87'

Registering Assets

To move an asset from the draft to the registered state, you must assign these properties on it:

  1. AssetTypeId
  2. PurchaseDate
  3. PurchasePrice
  4. BookDepreciationSetting_DepreciationStartDate
  5. BookDepreciationSetting_AveragingMethod
  6. Either a BookDepreciationSetting_DepreciationRate or a BookDepreciationSetting_EffectiveLifeYears

Then you can update the AssetStatus field to the value "Registered".

Disposing Assets

Disposing of assets is not supported through the API.

Columns
Name Type ReadOnly Description
AssetId [KEY] String True The unique Xero-generated ID of the asset
AssetName String False The name of the asset
AssetNumber String False A unique code for the asset
AssetStatus String False Either DRAFT, REGISTERED or DISPOSED
Description String False Free-form comments on the asset
PurchaseDate Date False When the asset was purchased
PurchasePrice Decimal False How much asset cost when it was purchased
DisposalDate Date False When the asset was purchased
DisposalPrice Decimal False What price the asset was disposed at
WarrantyExpiryDate Date False When the asset's warranty expires
SerialNumber String False The asset's serial number
AssetTypeId String False The ID of the type of this asset
BookDepreciationSetting_DepreciationMethod String False Either NoDepreciation, StraightLine, DiminishingValue100, DiminishingValue150, DiminishingValue200 or FullDepreciation
BookDepreciationSetting_AveragingMethod String False Either ActualDays or FullMonth
BookDepreciationSetting_DepreciationRate Double False The rate of depreciation in percent
BookDepreciationSetting_EffectiveLifeYears Int False The effective life of the asset in years
BookDepreciationSetting_DepreciationCalculationMethod String False Either Rate, Life or None
BookDepreciationDetail_CurrentCapitalGain Decimal False Once the asset is disposed, this will be the sell price minus the purchase price
BookDepreciationDetail_CurrentGainLoss Decimal False Once the asset is disposed, this will be the minimum of the sell and purchase price, minus the current book value
BookDepreciationDetail_DepreciationStartDate Date False When the depreciation takes effect
BookDepreciationDetail_CostLimit Decimal False The value of the asset to be depreciated, if this is less than the cost
BookDepreciationDetail_ResidualValue Decimal False The value of the asset once it's been fully depreciated
BookDepreciationDetail_PriorAccumDepreciationAmount Decimal False All depreciation prior to the current fiscal year
BookDepreciationDetail_CurrentAccumDepreciationAmount Decimal False All depreciation occurring in the current financial year
CanRollBack Boolean True Whether the asset's depreciation can be rolled back
AccountingBookValue Decimal True The accounting value of the asset
TenantId String False The ID of the tenant to query instead of the connection tenant
AssetTypes

Query, insert and update asset types for a Xero organization.

Table-Specific Information

Creating Asset Types

An AssetType can be created with one of two methods of computing depreciation. The first is according to a fixed percentage (in this example, 0.15%):

INSERT INTO AssetTypes (
  AssetTypeName,
  FixedAssetAccountId,
  DepreciationExpenseAccountId,
  AccumulatedDepreciationAccountId,
  BookDepreciationSetting_DepreciationMethod,
  BookDepreciationSetting_AveragingMethod,
  BookDepreciationSetting_DepreciationRate
)
VALUES (
  'Vehicles',
  '8e968d1a-5e25-4dad-b6f9-c8f4d72c0cac',
  '20b36596-3d1d-4789-9f08-50c92d19d522',
  '2a9bb066-3398-40e8-a953-caaabed2b7a7',
  'StraightLine',
  'FullMonth',
  0.15
)

Alternatively, you could create an asset type that depreciates according to an effective lifetime:

INSERT INTO AssetTypes (
  AssetTypeName,
  FixedAssetAccountId,
  DepreciationExpenseAccountId,
  AccumulatedDepreciationAccountId,
  BookDepreciationSetting_DepreciationMethod,
  BookDepreciationSetting_AveragingMethod,
  BookDepreciationSetting_EffectiveLifeYears
)
VALUES (
  'Vehicles',
  '8e968d1a-5e25-4dad-b6f9-c8f4d72c0cac',
  '20b36596-3d1d-4789-9f08-50c92d19d522',
  '2a9bb066-3398-40e8-a953-caaabed2b7a7',
  'StraightLine',
  'FullMonth',
  12
)

Updating Asset Types

Since an AssetType must be either using a depreciation rate or an effective lifetime, you must set one field to NULL if you want to change the AssetType to use the other. For example, to change from an effective lifetime to a depreciation rate:

UPDATE AssetTypes
SET BookDepreciationSetting_DepreciationRate = 0.09,
    BookDepreciationSetting_EffectiveLifeYears = null
WHERE AssetTypeId = 'bf28b6ca-3cc6-4591-ac5a-2c1115f04b4b'
Columns
Name Type ReadOnly Description
AssetTypeId [KEY] String True The unique Xero-generated ID of the asset type
AssetTypeName String False The name of the asset type
FixedAssetAccountId String False The asset account used for assets of this type
DepreciationExpenseAccountId String False The expense account used when assets of this type depreciate
AccumulatedDepreciationAccountId String False The account used for accumulated depreciation of assets of this type
BookDepreciationSetting_DepreciationMethod String False Either NoDepreciation, StraightLine, DiminishingValue100, DiminishingValue150, DiminishingValue200 or FullDepreciation
BookDepreciationSetting_AveragingMethod String False Either ActualDays or FullMonth
BookDepreciationSetting_DepreciationRate Decimal False The rate of depreciation in percent
BookDepreciationSetting_EffectiveLifeYears Int False The effective life of the asset in years
BookDepreciationSetting_DepreciationCalculationMethod String True Either Rate, Life or None
Locks Int True The number of assets using this asset type
TenantId String False The ID of the tenant to query instead of the connection tenant

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Xero Connector Views
Name Description
Settings Query asset settings for a Xero organization.
Tenants Query the list of organizations connected to your Xero account.
Settings

Query asset settings for a Xero organization.

Columns
Name Type Description
AssetNumberPrefix String The prefix used for generating fixed asset numbers
AssetNumberSequence String The next available asset number
AssetStartDate Date The date Xero started calculating depreciation for fixed assets
LastDepreciationDate Date The last depreciation date
DefaultGainOnDisposalAccountId String The default account that gains are posted to
DefaultLossOnDisposalAccountId String The default account that losses are posted to
DefaultCapitalGainOnDisposalAccountId String The default account that capital gains are posted to
OptInForTax Boolean
TenantId String The ID of the tenant to query instead of the connection tenant
Tenants

Query the list of organizations connected to your Xero account.

Columns
Name Type Description
TenantId [KEY] String A unique identifier for the tenant.
Name String The name of the organization or practice
Type String Whether the tenant is an organization or a practice
IsActive Boolean Whether the connection is currently using this tenant

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Xero.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Xero, along with an indication of whether the procedure succeeded or failed.

Xero Connector Stored Procedures
Name Description
GetLimitInfo Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL Obtains the OAuth authorization URL for authentication with various Xero services.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SwitchTenant Changes the tenant this connection fetches data from.
GetLimitInfo

Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.

If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:

SELECT * FROM AssetTypes LIMIT 1

Result Set Columns

Name Type Description
TenantId String ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining Integer The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining Integer The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining Integer The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter Integer The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified Datetime When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.
GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input
Name Type Required Description
AuthMode String True The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken String False The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey String False The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL String False This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier String False The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier String False The PKCEVerifier returned by GetOAuthAuthorizationURL.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret String The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input
Name Type Required Description
CallbackURL String False This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope String False The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State String False A state token to include in the authorization URL. Not included by default.
Result Set Columns
Name Type Description
URL String The URL to complete user authentication.
AuthToken String A token used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
AuthKey String A key used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
PKCEVerifier String A random value used as input for GetOAuthAccessToken in the PKCE flow.
RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input
Name Type Required Description
OAuthRefreshToken String True The refresh token returned from the original authorization code exchange.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input
Name Type Required Description
TenantId String False The ID of the tenant to revoke, as given in the Tenants view
Result Set Columns
Name Type Description
Success String Whether the connection was successfully revoked
SwitchTenant

Changes the tenant this connection fetches data from.

Input
Name Type Required Description
TenantId String False The ID of the tenant to attach to. If empty, the current tenant is not changed.
Result Set Columns
Name Type Description
ActiveTenant String The ID of the tenant that is currently active.

Projects Data Model

The Xero connector models the Xero Projects API as relational tables, views and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Views

Views describes the available views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information

in the data source.

Tables

The connector models the data in Xero as a list of tables in a relational database that can be queried using standard SQL statements.

Xero Connector Tables
Name Description
Projects Query, insert and update projects for a Xero organisation.
Tasks Query, insert, update and delete tasks for a Xero project.
Time Query, insert, update and delete time entries for a Xero project.
Projects

Query, insert and update projects for a Xero organisation.

Table-Specific Information

Creating Projects

A Project must be created with a Name and ContactId. The ContactId comes from the ContactId column in the Contacts table of the Accounting API. An EstimateValue may also be provided to set the initial estimate for the project:

INSERT INTO Projects (Name, ContactId, Deadline, EstimateValue) VALUES ('Construct Parking Lot', '1c5ac798-1e31-4c09-82ac-4781261ff20a', '2025-01-01', 250000)

Updating Projects

Once a project has been created, only its Deadline and EstimateValue may be updated:

UPDATE Projects SET Deadline = '2022-01-01', EstimateValue = 150000 WHERE ProjectId = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29'
Columns
Name Type ReadOnly Description
ProjectId [KEY] String True The unique Xero identifier of the project.
ContactId String False The ID of the Contact the project was created for
Name String False The name of the project.
CurrencyCode String True The currency used within the project.
MinutesLogged Int True The total minutes logged against all tasks in the project
TotalTaskAmountValue Decimal True The total actuals amount of each project task
TotalTaskAmountCurrency String True The currency of the TotalTaskAmountValue
TotalExpenseAmountValue Decimal True The total actuals amount of each project expense
TotalExpenseAmountCurrency String True The currency of the TotalExpenseAmountValue
MinutesToBeInvoiced Int True The total minutes across all tasks which have not been invoiced
TaskAmountToBeInvoicedValue Decimal True The total AmountToBeInvoicedValue for each task in the project
TaskAmountToBeInvoicedCurrency String True The currency of the TaskAmountToBeInvoicedValue
TaskAmountInvoicedValue Decimal True The total AmountInvoicedValue for each task in the project
TaskAmountInvoicedCurrency String True The currency of the TaskAmountInvoicedValue
ExpenseAmountToBeInvoicedValue Decimal True The total of all the expenses in the project which have not been invoiced
ExpenseAmountToBeInvoicedCurrency String True The currency of the ExpenseAmountToBeInvoicedValue
ExpenseAmountInvoicedValue Decimal True The total of all the expenses in the project which have been invoiced
ExpenseAmountInvoicedCurrency String True The currency of the ExpenseAmountInvoicedValue
ProjectAmountInvoicedValue Decimal True The total amount that has been invoiced across the whole project
ProjectAmountInvoicedCurrency String True The currency of the ProjectAmountInvoicedValue
DepositValue Decimal True The deposit for the project
DepositCurrency String True The currency of the DepositValue
DepositAppliedValue Decimal True The amount of the deposit which have been credited to project invoices.
DepositAppliedCurrency String True The currency of the DepositAppliedValue
CreditNoteValue Decimal True The total amount of credit notes in the project
CreditNoteCurrency String True The currency of the CreditNoteValue
Deadline Date False The currency of the CreditNoteValue
TotalInvoicedValue Decimal True The total amount invoiced to the project across all categories
TotalInvoicedCurrency String True The currency of the TotalInvoicedValue
TotalToBeInvoicedValue Decimal True The total amount for the project that is yet to be invoiced, across all categories
TotalToBeInvoicedCurrency String True The currency of the TotalToBeInvoicedValue
EstimateValue Decimal False The estimate for the project
EstimateCurrency String True The currency of the EstimateValue
Status String True Either INPROGRESS or CLOSED
TenantId String False The ID of the tenant to query instead of the connection tenant
Tasks

Query, insert, update and delete tasks for a Xero project.

Table-Specific Information

Creating Tasks

A Task must be created with a ProjectId, Name, RateCurrency, RateValue, and ChargeType. The ProjectId and RateCurrency must come from the project (ProjectId and CurrencyCode respectively). ChargeType determines how the RateValue is applied to the cost of the project:

  • TIME charges the project the amount in RateValue every hour.
  • FIXED charges the project the amount in RateValue once.
  • NON_CHARGEABLE does not charge the project. RateValue must be set to 0.

The EstimateMinutes may also be provided to set the time estimate for the task:

INSERT INTO Tasks (ProjectId, Name, RateCurrency, RateValue, ChargeType, EstimateMinutes) VALUES ('1de78bad-5a81-4cb8-ab53-5a1a3bc73b29', 'Pave Sidewalks', 'USD', 25000, 'FIXED', 1000)

Updating Tasks

Once a Task has been created, its RateCurrency, RateValue, ChargeType or EstimateMinutes may be updated:

UPDATE Tasks SET RateValue = 250, ChargeType = 'TIME', EstimateMinutes = 2000 WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/d15810a1-9324-4765-a357-80d160a0b87c'

Deleting Tasks

Tasks may also be deleted by specifying their Id:

DELETE FROM Tasks WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/d15810a1-9324-4765-a357-80d160a0b87c'
Columns
Name Type ReadOnly Description
Id [KEY] String False A unique combination of the project and task identifiers
TaskId String True The unique Xero identifier of the task.
ProjectId String False The ID of the project
Name String False The name of the task
ChargeType String False One of TIME, FIXED or NON_CHARGEABLE
RateValue Decimal False The per-hour rate billed for the task
RateCurrency String False The currency of the RateValue
EstimateMinutes Int False The estimated number of minutes to complete the task
TotalMinutes Int True The total number of minutes logged against the task
TotalAmountValue Decimal True The total value of the task, TotalMinutes multiplied by RateValue
TotalAmountCurrency String True The currency of the TotalAmountValue
MinutesToBeInvoiced Int True The minutes on this task that have not been invoiced
MinutesInvoiced Int True The minutes on this task that have been invoiced
NonChargeableMinutes Int True The minutes on this task that cannot be charged
AmountToBeInvoicedValue Decimal True The total value of the task that has not been invoiced, MinutesToBeInvoiced multiplied by RateValue
AmountToBeInvoicedCurrency String True The currency of the AmountToBeInvoicedValue
AmountInvoicedValue Decimal True The total value of the task that has been invoiced, MinutesInvoiced multiplied by RateValue
AmountInvoicedCurrency String True The currency of the AmountInvoicedValue
Status String True One of ACTIVE, INVOICED or LOCKED
TenantId String False The ID of the tenant to query instead of the connection tenant
Time

Query, insert, update and delete time entries for a Xero project.

Table-Specific Information

Creating Time

A Time must be created with a ProjectId, TaskId, UserId, Date and Duration. An optional description may also be provided:

INSERT INTO Time (ProjectId, TaskId, UserId, Date, Duration, Description) VALUES ('1de78bad-5a81-4cb8-ab53-5a1a3bc73b29', 'd15810a1-9324-4765-a357-80d160a0b87c', '312b124b-f33f-4cfe-a831-20eb290610d6', '2019-10-01', 250, 'Paved northwest section')

Updating Time

Once a Time has been created, its UserId, Date, Duration or Description may be updated:

UPDATE Time SET Date = '2019-11-01', Duration = 350 WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/cb53829a-31ca-4760-a2a7-5a2a46e67cfb'

Deleting Time

Time may also be deleted by specifying their Id:

DELETE FROM Time WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/cb53829a-31ca-4760-a2a7-5a2a46e67cfb'
Columns
Name Type ReadOnly Description
Id [KEY] String False A unique combination of the project and time entry identifiers
TimeEntryId String True The unique Xero identifier of the time entry.
UserId String False The ID of the user who logged the time entry
ProjectId String False The ID of the project the task belongs to
TaskId String False The ID of the task the time is logged for
Date Date False The day the time was logged for
DateEntered Datetime True When the time entry was created
Duration Int False When the time entry was created
Description String False The description of the time entry
Status String True One of ACTIVE, LOCKED or INVOICED
TenantId String False The ID of the tenant to query instead of the connection tenant

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Xero Connector Views
Name Description
Tenants Query the list of organizations connected to your Xero account.
Users Query project users for a Xero organisation.
Tenants

Query the list of organizations connected to your Xero account.

Columns
Name Type Description
TenantId [KEY] String A unique identifier for the tenant.
Name String The name of the organization or practice
Type String Whether the tenant is an organization or a practice
IsActive Boolean Whether the connection is currently using this tenant
Users

Query project users for a Xero organisation.

Columns
Name Type Description
UserId [KEY] String The unique Xero identifier of the user.
Name String The full name of the user.
Email String The email of the user.
TenantId String The ID of the tenant to query instead of the connection tenant

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Xero.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Xero, along with an indication of whether the procedure succeeded or failed.

Xero Connector Stored Procedures
Name Description
GetLimitInfo Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.
GetOAuthAccessToken Obtains the OAuth access token to be used for authentication with Xero.
GetOAuthAuthorizationURL Obtains the OAuth authorization URL for authentication with various Xero services.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with various Xero services.
RevokeTenant Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
SwitchTenant Changes the tenant this connection fetches data from.
GetLimitInfo

Retrieves limit information. Use this stored procedure to monitor your API limits as you make calls against a tenant.

Procedure-Specific Information

This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.

If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:

SELECT * FROM Projects LIMIT 1

Result Set Columns

Name Type Description
TenantId String ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one.
AppRemaining Integer The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants.
MinuteRemaining Integer The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant.
DayRemaining Integer The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant.
RetryAfter Integer The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant.
LastModified Datetime When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time.
GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with Xero.

Input
Name Type Required Description
AuthMode String True The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
AuthToken String False The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
AuthKey String False The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode.
CallbackURL String False This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
Verifier String False The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
PKCEVerifier String False The PKCEVerifier returned by GetOAuthAuthorizationURL.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret String The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
GetOAuthAuthorizationURL

Obtains the OAuth authorization URL for authentication with various Xero services.

Input
Name Type Required Description
CallbackURL String False This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal.
Scope String False The space-separated list of scopes you are requesting from Xero. By default all scopes are requested.
State String False A state token to include in the authorization URL. Not included by default.
Result Set Columns
Name Type Description
URL String The URL to complete user authentication.
AuthToken String A token used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
AuthKey String A key used as input for the GetOAuthAccessToken stored procedure to verify the request and get the OAuth Access Token.
PKCEVerifier String A random value used as input for GetOAuthAccessToken in the PKCE flow.
RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Xero services.

Input
Name Type Required Description
OAuthRefreshToken String True The refresh token returned from the original authorization code exchange.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
RevokeTenant

Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.

Input
Name Type Required Description
TenantId String False The ID of the tenant to revoke, as given in the Tenants view
Result Set Columns
Name Type Description
Success String Whether the connection was successfully revoked
SwitchTenant

Changes the tenant this connection fetches data from.

Input
Name Type Required Description
TenantId String False The ID of the tenant to attach to. If empty, the current tenant is not changed.
Result Set Columns
Name Type Description
ActiveTenant String The ID of the tenant that is currently active.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Xero:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Contacts table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Contacts'
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the RefreshOAuthAccessToken stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='RefreshOAuthAccessToken' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Contacts table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Contacts'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:xero:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Advanced Configurations Properties

The advanced configurations 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. Click the links for further details.

Authentication

Property Description
AuthScheme The type of authentication to use when connecting to Xero.
Schema The Xero API you want to access as a database schema.
Tenant Sets the Xero organization to connect to. Can be a name or tenant ID.

OAuth

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
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 This determines the scopes that the OAuth application requests from Xero.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

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.

Miscellaneous

Property Description
AutoExpandDetails Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.
IncludeArchived Set this property to include archived records.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
RetryDailyLimit Whether to wait when Xero reports that the provider has hit its daily limit.
RetryWaitTime How long to wait when a request hits the Xero API limit, in milliseconds.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

Authentication

This section provides a complete list of authentication properties you can configure.

Property Description
AuthScheme The type of authentication to use when connecting to Xero.
Schema The Xero API you want to access as a database schema.
Tenant Sets the Xero organization to connect to. Can be a name or tenant ID.

AuthScheme

The type of authentication to use when connecting to Xero.

Possible Values

Auto, OAuth, PKCE, OAuthClient

Data Type

string

Default Value

PKCE

Remarks

Current authentication methods supported for new apps:

  • OAuth: Authenticates you using OAuth with a client ID and secret. Should be used with server-side applications.
  • PKCE: Authenticates you using OAuth with only a client ID. Should be used with client-side applications.
  • OAuthClient: Authenticates you using client credentials with a client ID and secret. Can only be used if you have purchased a Custom Connection license from Xero.
  • Auto: Uses the value of the XeroAppAuthentication property to determine the authentication mode. XeroAppAuthentication is included only for backwards compatibility and all new connections should use a different AuthScheme property.

Schema

The Xero API you want to access as a database schema.

Possible Values

ACCOUNTING, ASSETS, PAYROLLAUS, FILES, PROJECTS

Data Type

string

Default Value

ACCOUNTING

Remarks

The connector supports the Accounting API, the Assets API, the Australian Payroll API, the Files API and the Projects API. Set the Schema connection property to Accounting, Assets, PayrollAUS, Files or Projects to connect to one of the corresponding APIs.

Tenant

Sets the Xero organization to connect to. Can be a name or tenant ID.

Data Type

string

Default Value

""

Remarks

Xero apps may be authorized by for multiple organizatins at once. By default the connector will connect to the first available tenant. To control which organization your are connected to, set this option.

This option can be set to either the name of your organization or to the tenant ID. If you have multiple organizations with the same name, then you must use the tenant ID. You can find the tenant ID by connecting with this option unset and then querying the Tenants view.

OAuth

This section provides a complete list of OAuth properties you can configure.

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
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 This determines the scopes that the OAuth application requests from Xero.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

OFF

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

OAuthClientId

The client ID assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

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.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

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.

Scope

This determines the scopes that the OAuth application requests from Xero.

Data Type

string

Default Value

""

Remarks

By default the connector will request that the user authorize all available scopes. If you want to override this, you can set this property to a space-separated list of OAuth scopes.

OAuthSettingsLocation

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.

Data Type

string

Default Value

%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the driver saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.

Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.

The default location is "%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt" with %APPDATA% set to the user's configuration directory. The default values are

  • Windows: "register://%DSN"
  • Unix: "%AppData%..."

where DSN is the name of the current DSN used in the open connection.

The following table lists the value of %APPDATA% by OS:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Linux ~/.config

CallbackURL

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.

Data Type

string

Default Value

http://localhost:33333

Remarks

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.

OAuthVerifier

The verifier code returned from the OAuth authorization URL.

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

Authentication on Headless Machines

See to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

OAuthRefreshToken

The OAuth refresh token for the corresponding OAuth access token.

Data Type

string

Default Value

""

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSL

This section provides a complete list of SSL properties you can configure.

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

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.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

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.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\Xero Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.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.

Note

Given that this connector supports multiple schemas, the structure for Xero custom schema files is as follows:

  • Each schema is given a folder corresponding to that schema name.
  • These schema folders are contained in a parent folder.
  • The parent folder should be set as the Location, not an individual schema's folder.

If left unspecified, the default location is "%APPDATA%\Xero Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

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.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

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.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property Description
AutoExpandDetails Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.
IncludeArchived Set this property to include archived records.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
RetryDailyLimit Whether to wait when Xero reports that the provider has hit its daily limit.
RetryWaitTime How long to wait when a request hits the Xero API limit, in milliseconds.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

AutoExpandDetails

Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.

Data Type

bool

Default Value

false

Remarks

Possible values include True and False. When set to true, this makes some columns contain data that are otherwise NULL, but it will make reads from those tables slower than they are by default.

When set to false (the default), the only way to get extra details from those tables is to explicitly select single items:

SELECT * FROM ExpenseClaims WHERE ExpenseClaimId = '...'

IncludeArchived

Set this property to include archived records.

Data Type

bool

Default Value

false

Remarks

Possible values include True and False.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

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.

Integration and Formatting
Property Description
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.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

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, "*=*".

RetryDailyLimit

Whether to wait when Xero reports that the provider has hit its daily limit.

Data Type

bool

Default Value

false

Remarks

Use of this option is strongly discouraged. See Xero API Limits for more details.

By default the connector will fail if it hits the daily API limit to avoid excessive wait times. If this is enabled and the connector hits the daily API limit, it will instead delay and retry after whatever length of time Xero recommends. This can potentially lead to the connector delaying for several hours.

RetryWaitTime

How long to wait when a request hits the Xero API limit, in milliseconds.

Data Type

string

Default Value

10000

Remarks

When connector issues more requests then Xero allows, this option determines how long the connector will wait before issuing another request. Increasing this value is recommended when multiple driver connections are active, such as when using Power BI.

This value is in milliseconds, so to use a delay of 10 seconds this property must be set to 10000.

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

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 connector throws an exception.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector 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 connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Contacts 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.json

Note that the specified path is not embedded in quotation marks.