Looking Inside Xero - Part 3

by

In our last couple of articles, we focused on the Xero file structure and Xero API which exposes various file structures to 3rd party query, insert, update and in some cases, delete within Xero.  The API is the primary way in which the hundreds of various Apps work with Xero to provide add-on and supplemental functionality.

Part 1 of this series looked at the basic structure including a list of tables within Xero, as well as an example of a list-type table (the Account table). In Part 2 we began by looking at an example of a transaction-type type (the Invoices table). We then looked at an example of a SQL to insert an object with multiple line items into the Invoice table, and the various fields (and field details) making up the Invoices table.

We also provided a summary of the use of views which are table-like structures within Xero that do not permit direct update. We noted that in some cases stored procedures maybe available to update views.

In this article we want to begin where we left off last time and that is by looking at the concept of stored procedures.  Stored Procedures serve to supplement the data that is directly available within the file structure. 

Sometimes it’s necessary to update data available with a view structure within the file using a stored procedure because the view does not provide direct, table-like, 2-way updates. In these cases, the data retrieval is performed by accessing the appropriate view or table, and the update is performed by calling a stored procedure.

Stored procedures make use of a list of parameters and return a data-set containing a collection of tuples that constitute the intended response. The following stored procedures are available via the Xero API:

As you can see, many of the stored procedures are associated with the OAuth access authentication process which we will cover in regard to Xero within a future article.

As an example of a stored procedure, we can GET for ListAttachments by accessing the appropriate table containing the document to retrieve a list of attachments from. The allowed Table values are Invoices, Receipts, CreditNotes, Bank Transactions, Bank Transfers, Contacts, Accounts, and Manual Journals. We must also provide the ObjectID which represents the unique ID of the document to retrieve a list of attachments from.

To retrieve a list of attachments that have been uploaded against a document, you will need to construct a url that contains the Guid of the document. This url is in the format:

As an example:

The returned message should contain a summary of the attachments against the specified document:

AttachmentID – a string representing the ID of the attachment

FileName – a string representing the fine name of the attachment.

URL – a string representing the URL of the attachment.

MimeType – a string representing the Internet media type of the attachment.

ContentLength – a string representing the attachment in bytes.

An example of such a returned message can be seen here:

Attachments can be uploaded to Xero using the PUT or POST method. To upload an attachment, a PUT or POST http request is made to a specific url created for each attachment. The body of the http request contains the raw attachment content, not xml or json data that is normally used to upload data to Xero. Attachments can be uploaded to Invoices, Receipts, Credit Notes, Repeating Invoices, Bank Transactions, Bank Transfers, Contacts, Accounts, and Manual Journals.

An example of a POST request:

The returned response should contain a summary of the attachment that has been accepted into the API (as shown in the example below):

The PUT method is identical to the POST method. If an attachment already exists on the specified document, then the attachment being uploaded will overwrite it.

Only ten (10) attachments can be uploaded to a document, but you can replace any already attached document by specifying the existing attachment.

We will continue to explore ‘inside of Xero’ by looking at additional data structures and API access within the next segment of this mini-series.

Acknowledgement & Credits:

Some Xero_attachment_## illustrations of API coding and responses contained herein are from examples provided by Xero.