• Products >
• Support >
• Solutions
• Company
• Blog

• < Back
• ODBC drivers >
• JDBC drivers >
• Bridges, gateways >
• Other >
• All products

• < Back
• JDBC-ODBC Bridge
• JDBC-Access Gateway

• < Back
• ODBC-ODBC Bridge
• ODBC-JDBC Gateway
• dbExpress-ODBC Gateway
• XML-ODBC Server

• < Back
• ODBC-ODBC Join Engine
• SDK
• Consultancy
• Roadmap

Inserting, updating, and deleting data with the Easysoft Xero ODBC driver

We're currently adding INSERT, UPDATE, and DELETE support to the Xero ODBC driver. For a pre-release version of the read-write Xero ODBC driver, contact the Easysoft Support team ().

Contents

• Which tables support INSERT, UPDATE, and DELETE statements?
• Special tables
  • LastId table
  • History table
  • API table
  • JSONRequestBody columns
  • XML format columns
• Attachments
  • Example: downloading an attachment
  • Example: uploading an attachment
  • Online invoice and credit note attachments
  • Further information
  • Example: listing all invoice attachments
• Accounts table
  • Microsoft SQL Server linked server examples: INSERT, UPDATE, and DELETE statements
• BankTransactions table
  • SQL INSERT example
  • SQL UPDATE example
• BankTransfers table
  • SQL INSERT example
• BatchPayments table
  • SQL UPDATE example
• BrandingThemes table
  • Example: apply branding theme to a payment service by using JSON
• Contacts tables
  • Example: adding a contact
  • Example: update a contact's primary email address
• ContactGroups table
  • SQL INSERT example
  • SQL UPDATE example
  • Note: SELECT queries
• CreditNotes table
  • SQL INSERT example
  • SQL UPDATE examples
• Currencies table
  • SQL INSERT Example
  • SQL SELECT example that calls Xero API
• Employees table
  • Example: adding employees
• ExpenseClaims table
  • SQL INSERT example
• Invoices tables
  • Example: adding a new invoice
  • Example: adding an invoice line to the end of an invoice
• Items table
  • Example: copying items
  • SQL UPDATE example
  • SQL DELETE example
• LinkedTransactions table
  • SQL INSERT example
  • SQL UPDATE example
  • SQL DELETE example
• ManualJournals table
  • SQL INSERT example
  • SQL UPDATE example
• PurchaseOrders table
  • SQL INSERT example
  • SQL UPDATE example
• Quotes tables
  • SQL INSERT example
• Receipts table
  • SQL UPDATE example
• TaxRates table
  • SQL UPDATE example
• TrackingCategories table
  • SQL INSERT example
• Formating XML
• Using the Xero ODBC driver with SQL Server
• HTTP 400 Bad Request
• SQL format and function support
  • Supported string functions
  • Supported numeric functions
  • Supported date and time functions

Which tables support INSERT, UPDATE, and DELETE statements?

You can query any table exposed by the Xero ODBC driver. For INSERT, UPDATE, or DELETE support, refer to this table:

Notes

1. You can't currently update the Contacts column in the ContactGroup tables.
2. You can't delete BatchPayments or CreditNotes records but you can change their status to DELETED.
3. You can use the Xero XPI to apply BrandingThemes records to a PaymentServices record.

Special tables

LastId table

Use the LastId table to find out the status of your most recent INSERT, UPDATE, or DELETE statement.

200: OK
404: Not Found

For example:

UPDATE Contacts SET EmailAddress='example_user@example.com' WHERE [Name]='example_customer'
SELECT LastID FROM LastId

LastID: a166e4c6-1a89-4d5e-acf3-939a9f82e23c

Note INSERTs always update the LastID table. UPDATEs and DELETEs only change the LastID table if a record is succesfully updated or deleted. For example, if you ran the UPDATE statement shown earlier twice, the second UPDATE would not alter the data and therefore not update LastID.

The LastId table and columns only apply to the current connection. For example, if you connect, insert a record, disconnect, and then re-connect, the LastID table is empty.

If your SQL statement contains a value that's not valid, LastID contains the reason why the statement failed. For example:

UPDATE
 BankTransactions 
SET
 LineItems = replace(LineItems, '<Quantity>1.0000</Quantity>', '<Quantity>2</Quantity>') 
WHERE
 BankTransactionId = '737982b0-2811-44c9-bdb3-3b26a3a6ef8c' -- invalid BankTransactionId
SELECT * from LastId

produces this output:

History table

Use the History table to query the Xero history for:

• BankTransactions
• BatchPayments
• BankTransfers
• Contacts
• Creditnotes
• Invoices
• Items
• ManualJournals
• Overpayments
• Payments
• Prepayments
• PurchaseOrders
• Repeating Invoices
• Quotes

The History table contains these columns:

Pass the endpoint and GUID in your WHERE clause. For example:

SELECT
 * 
FROM
 history 
WHERE
 endpoint = 'Contacts' 
 AND guid = '9ce626d2-14ea-463c-9fff-6785ab5f9bfb'

The Details column is a LONGVARCHAR type. To sort the results by Details, convert the column to a VARCHAR type. For example:

SELECT
 Changes,
 DateUTC,
 CAST(Details as varchar(512)) AS & quot;
Desc & quot;
FROM
 history 
WHERE
 endpoint = 'Contacts' 
 AND guid = '9ce626d2-14ea-463c-9fff-6785ab5f9bfb' 
ORDER BY
 DateUTC DESC

How do I get the GUID?

The GUID is the same as the ID of the primary key in the table, so, for example:

SELECT QuoteId from Quotes

returns all the GUIDs from the Quotes table.

API table

Use this table to query the Xero API.

200: OK
404: Not Found

Example queries:

SELECT * FROM api WHERE url = 'Currencies' AND Method = 'GET';
	 
SELECT
 * 
FROM
 api 
WHERE
 url = 'Accounts/524c9f2d-e680-4861-82f6-4ad171b51de5' 
 AND Method = 'GET';
SELECT
 * 
FROM
 api 
WHERE
 url = 'Currencies' 
 AND Method = 'PUT' 
 AND XMLRequestBody = '<Currency><Code>SGD</Code></Currency>';
SELECT
 * 
FROM
 api 
WHERE
 url = 'ContactGroups/fd33e5d2-3f95-4b1b-a405-7ed7067b9c22/Contacts' 
 AND Method = 'DELETE'

If you send invalid XML in the XMLRequestBody:

SELECT
 * 
FROM
 api 
WHERE
 url = 'Currencies' 
 AND Method = 'PUT' 
 AND XMLRequestBody = '<Currency2><Code>SGD</Code></Currency>'

the Xero API return code is 400, the response is "Bad Request" and the XMLResponseBody is the relevant API error response:

<ApiException xmlns:xsd="http://www.w3.org/2001/XMLSchema"
	 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <ErrorNumber>14</ErrorNumber>
 <Type>PostDataInvalidException</Type>
 <Message>Xml for post data was invalid, The 'Currency2' start tag
 on line 1 position 2 does not match the end tag of 'Currency'.
 Line 1, position 30.</Message>
</ApiException>

Warning PUT and POST work in different ways in the Xero API. Check the Xero XPI documentation carefully to be absolutely sure that you are using the correct method when inserting or updating records.

JSONRequestBody columns

From the Xero API Development team:

"At Xero we use .NET, and used the Microsoft .NET JSON date format available at the time of original development. We know it's ugly but not something we can fix without a breaking change or a new version of the API. We're really sorry about this. To help you along, below we explain how to handle this date format. An example date/time in JSON is returned like this:"

https://developer.xero.com/documentation/api/accounting/requests-and-responses

Here is an example query that uses JSON in the WHERE clause:

SELECT
 * 
FROM
 api 
WHERE
 url = 'TrackingCategories/201a3021-77de-495d-a233-1faaa40e2f64/Options' 
 AND Method = 'PUT' 
 AND JSONRequestBody = '{ "Name": "New Track 5" }'

Note You can't update the BankTransfer table.

XML format columns

Some Xero columns need to be in XML format. For example, invoice line items:

<LineItems>
 <LineItem>
 <Description>Retainer for consulting work</Description>
 <UnitAmount>500.00</UnitAmount>
 <AccountCode>200</AccountCode>
 <Quantity>3</Quantity>
 <AccountID>c563b607-fb0e-4d06-9ddb-76fdeef20ae3</AccountID>
 </LineItem>
</LineItems>

You can use spaces and newlines in your XML to improve readability if you prefer, but you don't need to.

You don't need to include all XML fields. In the example, the field <LineAmount> is missing. Xero automatically calculates the <LineAmount> value and puts that in for you using the formula:

LineAmount = Quantity * Unit Amount * ((100 – DiscountRate)/100)

Encode reserved charaters within your XML tags. For example:

The Xero API always replaces reserved characters with HTML entities when returning data. For INSERTs and UPDATEs, use either URL encoding, entities, or a combination of both.

If you get the error "[Easysoft ODBC-Xero Driver]Invalid XML detected," check your data for reserved characters.

XML element names are case sensitive. For example, <ContactId> is not valid, <ContactID> is. If you don't use the correct case, Xero returns an error similar to the following:

"PostDataInvalidException: The element 'ContactId' was not recognised. Ensure the element name has the correct case and that there are no duplicate elements of the same name."

Using Microsoft Visual Basic for Applications (VBA) to URL encode data

Public Function EncodeHex(byVal sInput As String) As String
 Dim TempAns As String
 Dim CurChr As Long
 Dim x As Variant
 
 CurChr = 1
 Do Until CurChr - 1 = Len(sInput)
 Select Case Asc(Mid(sInput, CurChr, 1))
 Case 48 To 57, 65 To 90, 97 To 122
 TempAns = TempAns & Mid(sInput, CurChr, 1)
 Case Else
 x = Hex(Asc(Mid(sInput, CurChr, 1)))
 If Len(CStr(x)) = 1 Then x = "0" & x
 TempAns = TempAns & "%" & x
 End Select
 
 CurChr = CurChr + 1
 Loop
 
 EncodeHex = TempAns
 
End Function

Using VBA to replace reserved characters with HTML entities

To run this code, add a reference to the "Microsoft HTML Object Library" in the VBA Editor.

Public Function EncodeHTML(ByVal sInput As String) As String
 On Error GoTo ErrHandle
 
 Dim HTMLDoc As New MicrosoftHTML.HTMLDocument
 HTMLDoc.Body.innerText = sInput
 EncodeHTML = HTMLDoc.Body.innerHTML
 
GoTo AllDone
ErrHandle:
 MsgBox "URLEncodeHTML error has occurred" & vbCrLf & vbCrLf & _
 "Error Source: EncodeHTML " & vbCrLf & _
 "Error Number: " & Err.Number & vbCrLf & _
 "Error Description: " & Err.Description, vbCritical, "An Error has Occurred!"
 
AllDone:
 On Error Resume Next
 Set oHTMLFile = Nothing
End Function

Using SQL Server to replace reserved characters with HTML entities

SELECT 'This & that value are < the other.' FOR XML PATH('')

Returns:

This & that value are < the other.

Tables with XML format columns

Attachments

Use the Xero ODBC driver to retrieve attachments from these tables:

• Accounts
• BankTransactions
• BankTransfers
• Contacts
• CreditNotes
• Invoices
• ManualJournals
• PurchaseOrders
• Quotes
• Receipts
• RepeatingInvoices

Example: downloading an attachment

This VBA code sample downloads an invoice attachment. The attachment is a JPEG file.

Sub GETInvoiceImage()
 Dim c As New ADODB.Connection
 Dim r As New ADODB.Recordset
 Dim st As New ADODB.Stream
 Dim arInputByte() As Byte
 Dim FName As String
 Dim i As Integer
 
 FName = "c:\l\WindMill3.jpg"
 
 c.Open "XeroDemo"
 r.Open "SELECT Code, RAWResponseBody from api WHERE url = " _
 & "'Invoices/4953d237-6cf2-4ef4-8f37-044aada0d594/Attachments/WindMill3.jpg'" _
 & "and Method = 'GET'", c
 If Not r.EOF And r("Code") = 200 Then
 st.Type = adTypeBinary
 st.Open
 st.Write r(1)
 st.SaveToFile FName
 st.Close
 Debug.Print "File Saved"
 Else
 Debug.Print "File not found."
 End If
 r.Close
 c.Close
 
 Set r = Nothing
 Set c = Nothing
End Sub

Example: uploading an attachment

Use the API table to upload files. This VBA code sample uploads an invoice attachment.

Sub PUTInvoiceImage()
 Dim c As New ADODB.Connection
 Dim cm As New ADODB.Command
 Dim st As New ADODB.Stream
 Dim SQL As String
 Dim FName As String
 Dim pos As Long
 
 FName = "c:\l\WindMill8.jpg"
 
 SQL = "INSERT INTO api ( url, method, rawrequestbody ) " _
 & " VALUES( 'Invoices/4953d237-6cf2-4ef4-8f37-044aada0d594/Attachments/"
 pos = 0
 Do While InStr(pos + 1, FName, "\", vbBinaryCompare) > 0
 pos = InStr(pos + 1, FName, "\", vbBinaryCompare)
 Loop
 SQL = SQL & Right(FName, Len(FName) - pos)
 SQL = SQL & "', 'PUT', ? )"
 Debug.Print SQL
 
 c.Open "XeroDemo"
 
 st.Type = adTypeBinary
 st.Open
 st.LoadFromFile FName
 With cm
 .ActiveConnection = c
 .CommandText = SQL
 .Parameters.Append .CreateParameter("RawRequestBody", adLongVarBinary, _
 adParamInput, st.Size, st.Read)
 .Execute
 End With
 Debug.Print "Uploaded"
 
 c.Close
 st.Close
 
 Set c = Nothing
End Sub

Online invoice and credit note attachments

Use this query string:

?IncludeOnline=true

to include your attachment in online invoices and credit notes. For example:

SQL = "
INSERT INTO
 api ( url, method, rawrequestbody ) 
VALUES
 (
 'Invoices/4953d237-6cf2-4ef4-8f37-044aada0d594/Attachments/WindMill8.jpg?IncludeOnline=true' 
 )
 "

Further information

The Xero Invoice documentation says:

"You can upload up to 10 attachments (each up to 3 MB in size) per invoice, once the invoice has been created in Xero."

Exceeding this limit generates the error:

"[Easysoft ODBC-Xero Driver]ValidationException: A validation exception occurred. The file couldn't be uploaded because the API limit of 10 attachments has been exceeded for the document."

You can upload files that are greater than 3 MB in size (in our test, WindMill.jpg was 5.5 MB). However, if you try to do this you may get:

"[Easysoft ODBC-Xero Driver]PayloadOversizeException: This request exceeds the maximum size limit. Refer to the documentation at https://developer.xero.com/documentation/oauth2/limits"

Or, if the Xero API ignores the request and returns a NULL response to the Xero ODBC driver:

"[Easysoft ODBC-Xero Driver]server response 0 (null)"

Example: listing all invoice attachments

Sub GetListOfAttachmentsForAllInvoices()
 Dim c As New ADODB.Connection
 Dim i As New ADODB.Recordset
 Dim r As New ADODB.Recordset
 Dim SQL As String
 Dim RB As String
 Dim XMLDoc As New MicrosoftXML2.DOMDocument60
 Dim XMLNode
 Dim XMLNodes
 Dim XMLString As String
 
 c.Open "XeroDevDemo"
 
 i.Open "SELECT InvoiceId from Invoices WHERE HasAttachments <> 0", c
 
 Do While Not i.EOF
 
 SQL = "SELECT Code, XMLResponseBody FROM API WHERE URL='Invoices/" & i("InvoiceID") _
 & "/Attachments/' AND Method='GET'"
 
 r.Open SQL, c
 If r("Code") = 200 Then
 XMLString = r("XMLResponseBody")
 XMLDoc.LoadXML XMLString
 If XMLDoc.parseError <> 0 Then
 Debug.Print XMLDoc.parseError.reason
 Else
 Set XMLNodes = XMLDoc.getElementsByTagName("FileName")
 If XMLNodes.length <> 0 Then Debug.Print "Invoice Id: " & i("InvoiceID")
 For Each XMLNode In XMLNodes
 Debug.Print " " & XMLNode.Text
 DoEvents
 Next
 End If
 End If
 
 r.Close
 i.MoveNext
 
 Loop
 i.Close
 c.Close
 
 Set r = Nothing
 Set i = Nothing
 Set c = Nothing
End Sub

If you have a lot of invoice attachments, this code could take some time to complete.

Accounts table

Supports INSERT, UPDATE, and DELETE statements.

Microsoft SQL Server linked server examples: INSERT, UPDATE, and DELETE statements

This SQL statement updates an accounts description:

EXEC('UPDATE Accounts SET Description=''Expenses incurred in relation to training staff2''
WHERE AccountId=''87e2c007-4a7c-43d7-a19d-b82dcf529313''') AT Xero

To check whether the update succeeded, run this query straight after:

EXEC('SELECT * FROM LastId') AT Xero

This statement creates a new account:

EXEC('INSERT INTO Accounts ( Code, Name, Type, Description, TaxType )
VALUES (''007'', ''Buchanan'' ,''OVERHEADS'', ''Steven Buchanan'', ''NONE'')') AT Xero

This statement deletes the account:

EXEC('DELETE FROM Accounts WHERE Code=''007''') AT Xero

BankTransactions table

Supports INSERT and UPDATE statements.

SQL INSERT example

INSERT INTO
 BankTransactions ( Type, BankAccount_Code, Contact_Name, [Date], CurrencyCode, LineItems ) 
VALUES
 (
 'SPEND', '090', 'Espresso 31', '2024-02-08', 'USD',
 '<LineItems><LineItem><UnitAmount>50.00</UnitAmount>
 <TaxType>INPUT2</TaxType><TaxAmount>8.33</TaxAmount><LineAmount
 >50.00</LineAmount><AccountCode>429</AccountCode><Quantity>
 1.0000</Quantity><LineItemID>e8059c2b-8fe5-4522-9a2f-40d352c4a80f</LineItemID>
 <AccountID>f96c9458-d724-47bf-8f74-a9d5726465ce</AccountID></LineItem></LineItems>'
 )

If the currency code you specify does not match that of the underlying bank account, it will be ignored. In the example, "USD" was specifed but the record's currency code is "GBP."

BankTransactionID", "Type", "ModifiedAfter", "BankAccount_AccountID", "BankAccount_Code",
"BankAccount_Name", "Contact_ContactID", "Contact_Name", "Contact_Status", "Date",
"Status", "LineAmountTypes", "LineItemCount", "SubTotal", "TotalTax", "Total",
"UpdatedDateUTC", "CurrencyCode", "IsReconciled", "HasAttachments", "LineItems"
"db19b6bf-a278-41ff-a0a8-438dd4aa338d", "SPEND", <Null>, "bd9e85e0-0478-433d-ae9f-0b3c4f04bfe4",
 "090", "Business Bank Account", "c82900a5-064c-46e1-9d8b-86404c6bfd01", "Espresso 31",
"ACTIVE", 2024年02月08日, "AUTHORISED", "Inclusive", 1, 41.67, 8.33, 50, 2024年02月08日 08:42:43,
"GBP", 0, 0, "<LineItems><LineItem><UnitAmount>50.00</UnitAmount><TaxType>INPUT2</TaxType>
 <TaxAmount>8.33</TaxAmount><LineAmount>50.00</LineAmount><AccountCode>429</AccountCode>
 <Quantity>1.0000</Quantity><LineItemID>ee0ad9fe-6c0f-40d5-a1f0-4578540af34c</LineItemID>
 <AccountID>f96c9458-d724-47bf-8f74-a9d5726465ce</AccountID></LineItem></LineItems>"

SQL UPDATE example

UPDATE BankTransactions SET LineItems='<LineItems><LineItem><Description>Petrol in
 company car</Description><UnitAmount>59.00</UnitAmount>
 <TaxType>INPUT2</TaxType><TaxAmount>11.80</TaxAmount>
 <AccountCode>449</AccountCode><Quantity>2.0000</Quantity>
 <LineItemID>283b0044-7ebc-482e-b3b7-b58ef6812226</LineItemID>
 <AccountID>0be1631e-cc7e-4c27-951f-308c3307c0fe</AccountID></LineItem>
 </LineItems>' WHERE BankTransactionId='db7dcb47-4fb1-4190-9f56-911d7ee5c560'

Note You can't update BankTransactions that have the DELETED status or that have not been reconciled.

BankTransfers table

Supports INSERT statements.

SQL INSERT example

INSERT INTO
 BankTransfers ( [Date], FromBankAccountCode, ToBankAccountCode, Amount, CurrencyRate ) 
VALUES
 (
 '2024-02-07', '091', '090', 1, 1
 )

This example transfers 1 GBP from Account 091 to 090 at a rate of 1.

BatchPayments table

Supports UPDATE statements. Use this to set Xero batch payment status to DELETED.

SQL UPDATE example

Sets the specified batch payment status to DELETED:

UPDATE BatchPayments SET Status='DELETED' WHERE BatchPaymentId= '4f7d624a-1899-469e-8121-2dc739fda3f7'

BrandingThemes table

Supports INSERT statements.

Example: apply branding theme to a payment service by using JSON

SELECT
 * 
FROM
 api 
WHERE
 url = 'BrandingThemes/201a3021-77de-495d-a233-1faaa40e2f64/PaymentService' 
 AND Method = 'PUT' 
 AND JSONRequestBody = ' { "PaymentServices": [{ "PaymentServiceID": "de5c978d-3cbf-4ebb-9ca9-20d7cb196ab1" }] }'

Contacts tables

Supports INSERT and UPDATE statements.

Example: adding a contact

This sample code inserts a single contact who has one address and two phone numbers. Use XML to specify the address and phone number.

Sub AddContact()
 Dim c As New ADODB.Connection
 Dim r As New ADODB.Recordset
 Dim e As ADODB.Error
 Dim cmdADO As New ADODB.Command
 Dim prmText As New ADODB.Parameter
 Dim i As Integer
 
 Dim SQL As String
 Dim Phones As String
 Dim Addresses As String
 Addresses = "<Addresses><Address><AddressType>POBOX</AddressType><" _
 & "AddressLine1>P O Box 3955</AddressLine1><AddressLine2>South Mailing Centre" _
 & "</AddressLine2><City>Oaktown</City><PostalCode>OK12 3TN</PostalCode>" _
 & "<AttentionTo>Human Resources Manager</AttentionTo></Address><Address><AddressType" _
 & ">STREET</AddressType></Address></Addresses>"
 Phones = "<Phones><Phone><PhoneType>DDI</PhoneType></Phone><Phone><PhoneType>DEFAULT" _
 & "</PhoneType><PhoneNumber>9191</PhoneNumber><PhoneAreaCode>555</PhoneAreaCode>" _
 & "<PhoneCountryCode>01</PhoneCountryCode></Phone><Phone><PhoneType>FAX</PhoneType>" _
 & "</Phone><Phone><PhoneType>MOBILE</PhoneType></Phone></Phones>"
 
 SQL = "INSERT INTO Contacts ( Name, Addresses, Phones ) VALUES ('example_customer',?,?)"
 
 On Error GoTo ErrHandle
 
 c.Open XeroDemo
 cmdADO.ActiveConnection = c
 cmdADO.CommandText = SQL
 Set prmText = cmdADO.CreateParameter(Addresses, adVarWChar, adParamInput, Len(Addresses), Addresses)
 cmdADO.Parameters.Append prmText
 Set prmText = cmdADO.CreateParameter(Phones, adVarWChar, adParamInput, Len(Phones), Phones)
 cmdADO.Parameters.Append prmText
 cmdADO.Execute
 
 r.Open "SELECT * FROM LastId", c
	 
 If Not r.EOF Then
 Debug.Print "Inserted Contact ID: & r(0)"
 SQL = "SELECT * FROM Contacts WHERE ContactID='" & r(0) & "'"
 r.Close
 
 r.Open SQL, c
 
 ' Workaround for ADOdb LONGNVARCHAR issue.
 For i = 0 To r.Fields.Count - 1
 If Not IsNull(r(i)) Then
 Debug.Print "r(i).Name & : & r(i) "
 Else
 Debug.Print "r(i).Name & : NULL "
 End If
 Next
 End If
 
 r.Close
 Set r = Nothing
 GoTo AllDone
 
 ErrHandle:
 For Each e In c.Errors
 Debug.Print " ADODB Error: & e.Description "
 Next
 AllDone:
 c.Close
 Set c = Nothing
 Debug.Print AddContact; Finished
End Sub

Example: update a contact's primary email address

Sub UpdateContactEmailAddress()
 Dim c As New ADODB.Connection
 Dim r As New ADODB.Recordset
 Dim e As ADODB.Error
 Dim cmdADO As New ADODB.Command
 Dim prmText As New ADODB.Parameter
 Dim rc As Long
 Dim i As Integer
 Dim SQL As String
 
 SQL = "UPDATE Contacts SET EmailAddress='my_user@example.com' WHERE [Name]='example_customer'"
 
 ' On Error GoTo ErrHandle
 
 c.Open XeroDemo
 c.Execute SQL, rc
 
 Debug.Print "Records affected: & rc"
 
 r.Open "SELECT LastID from LastId ", c
 If Not r.EOF Then
 Debug.Print "UPDATE Contact ID: & r(0)"
 SQL = "SELECT [Name], EmailAddress FROM Contacts WHERE ContactID='" & r(0) & "'"
 r.Close
 r.Open SQL, c
 
 For i = 0 To r.Fields.Count - 1
 If Not IsNull(r(i)) Then
 Debug.Print "r(i).Name & : & r(i) "
 Else
 Debug.Print "r(i).Name & : NULL "
 End If
 Next
 End If
 
 r.Close
 Set r = Nothing
 GoTo AllDone
 
ErrHandle:
 For Each e In c.Errors
 Debug.Print "ADODB Error: & e.Description "
 Next
 
AllDone:
 c.Close
 Set c = Nothing
 Debug.Print "UpdateContactEmailAddress; Finished "
End Sub

ContactGroups table

Supports INSERT and UPDATE statements.

SQL INSERT example

This SQL statement inserts a contact into a contact group.

INSERT INTO
 ContactGroups ( Name, Contacts ) 
VALUES
 (
 'New Contact Group' , '<Contacts><Contact>
 <ContactID>af0091a9-82ef-4cac-9fd6-22c095ac6a58</ContactID>
 <Name>Hamilton Smith Ltd</Name></Contact></Contacts>'
 )

If you create a contact group without specifying any contacts, Xero automatically adds all contacts.

SQL UPDATE example

This SQL statement updates a contact group's name.

UPDATE
 ContactGroups 
SET
 Name = 'Training 2' 
WHERE
 ContactGroupID = '91dbdc3f-86c5-4bfe-b227-5d1735945cea'

You can't currently update the Contacts column by using the Xero ODBC driver.

Note: SELECT queries

If you run:

SELECT * FROM ContactGroups

the Contacts column is blank. This is a limitation in the Xero API. If you include a valid ContactGroupId:

SELECT * FROM ContactGroups WHERE ContactGroupId='91dbdc3f-86c5-4bfe-b227-5d1735945cea'

your result set will be similar to:

"ContactGroupID", "ModifiedAfter", "Name", "Status", "Contacts"
91dbdc3f-86c5-4bfe-b227-5d1735945cea, <Null>, Training, ACTIVE,
<Contacts><Contact><ContactID>af0091a9-82ef-4cac-9fd6-22c095ac6a58</ContactID>
<Name>Hamilton Smith Ltd</Name></Contact>
<Contact><ContactID>c523e12f-8b74-4d3a-bbd8-32d7a2f598b4</ContactID>
<Name>City Limousines</Name></Contact>
<Contact><ContactID>a852a44c-3d8f-4c4b-a628-3a2c2121b9b1</ContactID>
<Name>Bank West</Name></Contact>
<Contact><ContactID>847933f0-7c35-4e5b-b884-5f9df64c8e4b</ContactID>
<Name>Port &amp; Philip Freight</Name></Contact>
<Contact><ContactID>94a82e91-53da-4f87-a417-63d6a1607ced</ContactID>
<Name>Young Bros Transport</Name></Contact>
<Contact><ContactID>a871a956-05b5-4e2a-9419-7aeb478ca647</ContactID>
<Name>Ridgeway University</Name></Contact><Contact>
<ContactID>2dd82f49-e818-4dd0-955b-b637ccaa5597</ContactID>
<Name>City Agency</Name></Contact>
<Contact><ContactID>b2c1f980-96c9-45ff-a42b-dca141936c6c</ContactID>
<Name>Rex Media Group</Name></Contact></Contacts>

CreditNotes table

Supports INSERT and UPDATE statements.

SQL INSERT example

INSERT INTO
 CreditNotes ( [Date], ContactName, Type, CurrencyCode, CurrencyRate, LineItems ) 
VALUES
 (
 '2024-02-07', 'PowerDirect', 'ACCRECCREDIT', 'GBP', 1, '<LineItems><LineItem>
 <Description>Monthly electricity</Description><UnitAmount>105.75</UnitAmount>
 <TaxType>RRINPUT</TaxType><TaxAmount>5.04</TaxAmount><LineAmount>105.75</LineAmount>
 <AccountCode>445</AccountCode><Quantity>1.0000</Quantity>
 <LineItemID>79393360-50bb-467f-a552-dd2be361201d</LineItemID>
 <AccountID>d50842c3-af67-4233-b8c9-df3180f5b7bd</AccountID>
 </LineItem></LineItems>'
 )

SQL UPDATE examples

This example changes the credited line to be half the amount credited.

UPDATE
 CreditNotes 
set
 SubTotal = 52.88, TotalTax = 2.52, Total = 52.88 + 2.52,
 LineItems = replace(replace(replace(LineItems, '<Quantity>1.0000</Quantity>', 
 '<Quantity>0.5</Quantity>'), '<LineAmount>105.75</LineAmount>', 
 '<LineAmount>52.88</LineAmount>'), '<TaxAmount>5.04</TaxAmount>',
 '<TaxAmount>2.52</TaxAmount>') 
WHERE
 CreditNoteId = '3812831b-b189-46e2-b8cc-3fee508aad83'

You can't delete a credit note but you can change its status to DELETED:

UPDATE
 CreditNotes 
SET
 Status = 'DELETED' 
WHERE
 CreditNoteID = '75e794a1-d09b-46c9-9ef7-bd749a5f8028'

Currencies table

Supports INSERT statements.

The following SQL statements both insert the speoified currency code.

SQL INSERT example

INSERT INTO Currencies ( Code ) VALUES ('USD')

SQL SELECT example that calls Xero API

SELECT
 * 
FROM
 api 
WHERE
 url = 'Currencies' 
 AND Method = 'PUT' 
 AND XMLRequestBody = '<Currency><Code>EUR</Code></Currency>'

Employees table

Supports: INSERT statements.

Example: adding employees

This VBA example checks worksheet cells for employees who don't have a Xero ID. Such employees are then added to Xero and the cells are updated with the relevant employee ID. The spreadsheet contents are:

After running the VBA, the spreadsheet contents become:

Only new employees (spreadsheet entries without an Employee ID) are added on subsequent runs.

Sub SendEmployeeToXero()
 Dim c As New ADODB.Connection
 Dim r As New ADODB.Recordset
 Dim rPos As Long
 Dim rc As Long
 Dim SQL As String
 Dim rInserted As Long
 
 c.Open "XeroDemo"
 rPos = 2
 
 ' Walk down the sheet collecting the rows data
 rInserted = 0
 Do While Not IsNull(Cells(rPos, 2)) And Cells(rPos, 2) <> ""
 
 If Not IsNull(Cells(rPos, 2)) And Cells(rPos, 2) <> "" _
 And (IsNull(Cells(rPos, 4)) Or Cells(rPos, 4) = "") Then
 SQL = "INSERT INTO Employees ( FirstName, LastName ) VALUES " & _
 "('" & Replace(Cells(rPos, 2), "'", "''") & "', '" & _
 Replace(Cells(rPos, 3), "'", "''") & "')"
 c.Execute SQL, rc
 
 If rc <> 0 Then
 r.Open "select LastID from LastId", c
 If Not IsNull(r(0)) Then
 Cells(rPos, 4) = r(0)
 End If
 r.Close
 rInserted = rInserted + 1
 End If
 End If
 
 rPos = rPos + 1
 Loop
 
 MsgBox "Insert rows : " & rInserted
 
 c.Close
 
 Set r = Nothing
 Set c = Nothing
End Sub

ExpenseClaims table

Supports INSERT statements.

Although ExpenseClaims was deprecated in 2018 by Xero, the endpoint is still available.

SQL INSERT example

INSERT INTO
 ExpenseClaims ( UserId, Receipts ) 
VALUES
 (
 '73f0cf2f-ede4-49ea-b93e-6723d9dfca32', '<Receipts>
 <ReceiptID>
 7b67f5d8-bee4-42a2-a184-6742a2f79df1
 </ReceiptID>
 </Receipts>' 
 )

Note Not all Xero accounts have access to ExpenseClaims.

Invoices tables

Supports INSERT and UPDATE statements.

Example: adding a new invoice

This VBA code sample adds a new invoice. The invoice line items are added into the LineItems in the XML format.

Sub CreateBasicInvoice()
 Dim c As New ADODB.Connection
 Dim r As New ADODB.Recordset
 Dim e As ADODB.Error
 Dim i As Integer
 
 Dim SQL As String
 
 ' This is a basic invoice insert. Notes:
 ' Date needs to be enclosed in [] as it is a reserved word
 ' Date format must be yyyy-mm-dd
 ' InvoiceNumber must be unique
 SQL = "INSERT INTO Invoices (InvoiceNumber, Type, ContactID, ContactName, [Date], LineItems ) " & _
 "VALUES ( 'INV-0346', 'ACCREC', '9ce626d2-14ea-463c-9fff-6785ab5f9bfb', " & _
 "'Boom FM', '2024-01-31', '<LineItems><LineItem>" & _
 "<Description>Retainer for consulting work</Description><UnitAmount>500.00</UnitAmount>" & _
 "<AccountCode>200</AccountCode><Quantity>3</Quantity>" & _
 "<AccountID>c563b607-fb0e-4d06-9ddb-76fdeef20ae3</AccountID></LineItem></LineItems>')"
 
 c.Open XeroDemo
 
 On Error GoTo ErrHandle
 
 ' Insert the invoice
 c.Execute SQL
 
 ' If the INSERT succeeds, fetch all the columns.
 r.Open "SELECT * FROM LastId", c
 If Not r.EOF Then
 Debug.Print "Inserted Invoice ID: & r(0)"
 SQL = "SELECT * FROM Invoices WHERE InvoiceID='" & r(0) & "'"
 r.Close
 r.Open SQL, c
 
 ' Workaround for ADOdb LONGNVARCHAR issue.
 For i = 0 To r.Fields.Count - 1
 If Not IsNull(r(i)) Then
 Debug.Print "r(i).Name & : & r(i)"
 Else
 Debug.Print "r(i).Name & : NULL"
 End If
 Next
 End If
 
 r.Close
 Set r = Nothing
 GoTo AllDone
 
ErrHandle:
 For Each e In c.Errors
 Debug.Print "ADODB Error: & e.Description"
 Next
 
AllDone:
 c.Close
 Set c = Nothing
 Debug.Print "CreateBasicInvoice Finished."
End Sub

Example: adding an invoice line to the end of an invoice

Sub AddAdditionalLineToInvoice()
 
 Dim c As New ADODB.Connection
 Dim r As New ADODB.Recordset
 Dim e As ADODB.Error
 Dim cmdADO As New ADODB.Command
 Dim prmText As New ADODB.Parameter
 Dim li As String
 Dim SQL As String
 Dim AddItem As String
 
 ' Add a line to an existing invoice.
 AddItem = "<LineItem><ItemCode>BOOK</ItemCode><Description>Fish out of Water: " & _
 "Finding Your Brand</Description><UnitAmount>19.95</UnitAmount><Item>" & _
 "<ItemID>5b27339e-ecc8-40b6-ace3-0f25bdf5fd63</ItemID><Name>Fish out of Water:" & _
 "Finding Your Brand</Name><Code>BOOK</Code></Item><Quantity>2</Quantity>" & _
 "<AccountID>c563b607-fb0e-4d06-9ddb-76fdeef20ae3</AccountID></LineItem>"
 c.Open XeroDemo
 
 SQL = "SELECT LineItems FROM Invoices WHERE InvoiceNumber='INV-0346'"
 
 r.Open SQL, c
 If r.EOF Then
 Debug.Print "Invoice; missing"
 r.Close
 GoTo AllDone
 Else
 li = r(0)
 r.Close
 
 SQL = "UPDATE Invoices SET LineItems=? WHERE InvoiceNumber='INV-0346'"
 
 ' This puts the new line at the end of the items list.
 li = Replace(li, "</LineItems>, AddItem & </LineItems>")
 
 On Error GoTo ErrHandle
 
 cmdADO.ActiveConnection = c
 cmdADO.CommandText = SQL
 Set prmText = cmdADO.CreateParameter(LineItems, adVarWChar, adParamInput, Len(li), li)
 cmdADO.Parameters.Append prmText
 cmdADO.Execute
 
 End If
 
 GoTo AllDone
 
ErrHandle:
 For Each e In c.Errors
 Debug.Print "ADODB Error: & e.Description"
 Next
 
AllDone:
 c.Close
 Set c = Nothing
 Debug.Print "AddAdditionalLineToInvoice Finished."
End Sub

Items table

Supports INSERT, UPDATE, and DELETE statements.

This table has one row per item.

Example: copying items

This VBA code sample copies items from a Microsoft Access table to Xero. The table structure is:

The example inserts all items as if they are untracked and not purchased from a third party.

Sub LocalItemsIntoXero()
 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 
 Dim c As New ADODB.Connection
 Dim r As New ADODB.Recordset
 Dim e As ADODB.Error
 Dim SQL As String
 
 ' Insert Access table data (LocalProducts) as Xero items
 Set db = CurrentDb
 Set rs = db.OpenRecordset(LocalProducts)
 
 c.Open XeroDemo
 
 Do While Not rs.EOF
 
 ' Check that the item does not already exist
 r.Open "SELECT COUNT(*) FROM Items WHERE Code='" & rs(Code) & "'", c
 If r(0) = 0 Then
 r.Close
 SQL = "insert into Items ( Code, Name, Description, SalesUnitPrice, IsPurchased, " & _
 "SalesTaxType, SalesAccountCode, IsTrackedAsInventory ) VALUES ( " & _
 "'" & rs("Code") & "', '" & rs("Name") & "', '" & rs("Description") & "', " & _
 rs("Price") & ", 0, 'OUTPUT2', '200', 0 )"
 
 c.Execute SQL
 r.Open "SELECT * FROM LastId", c
 If Not r.EOF Then
 Debug.Print Item ' & rs(Name) & ' insert as Item Id: & r(0)
 End If
 End If
 r.Close
 rs.MoveNext
 Loop
 
 rs.Close
 db.Close
 
 Set rs = Nothing
 Set db = Nothing
 
 c.Close
 Set c = Nothing
 
End Sub

SQL UPDATE example

UPDATE Items SET SalesUnitPrice=1000 WHERE ItemID='2033478a-79d4-409d-bfee-c3cc23c15884'

SQL DELETE example

DELETE FROM Items WHERE ItemID='2033478a-79d4-409d-bfee-c3cc23c15884'

LinkedTransactions table

Supports INSERT, UPDATE, and DELETE statements.

SQL INSERT example

INSERT INTO
 LinkedTransactions ( Type, SourceTransactionId, SourceLineItemId,
 SourceTransactionTypeCode, ContactId ) 
VALUES
 (
 'BILLABLEEXPENSE', 'b1b4f969-a6e3-478c-b0e9-e232d6d81724',
 '3e50e9ff-49da-4fc3-8f80-99f97248fef1', 'ACCPAY',
 '9ce626d2-14ea-463c-9fff-6785ab5f9bfb'
 )

SQL UPDATE example

UPDATE LinkedTransactions SET ContactId='6f5b2926-224f-4ac5-9632-c0cd1e5551c5'
WHERE LinkedTransactionId='70ce9c5d-790a-41f8-9695-079cd143de82'

SQL DELETE example

DELETE FROM LinkedTransactions
WHERE LinkedTransactionId='25977bea-2ed6-404c-b770-6c5e00176ac7'

ManualJournals table

Supports INSERT and UPDATE statements.

SQL INSERT example

INSERT INTO
 ManualJournals ( [Date], Narration, JournalLines ) 
VALUES
 (
 '2024-02-06', 'This is a test', '<JournalLines><JournalLine>
<Description>Coded incorrectly Computer Equipment should be Office Equipment</Description>
<TaxType>NONE</TaxType><TaxAmount>0.00</TaxAmount><LineAmount>2569.00</LineAmount>
<AccountCode>710</AccountCode><AccountID>a4602fb6-2e9d-4064-b318-c409032692ba
</AccountID></JournalLine><JournalLine>
<Description>Coded incorrectly Office Equipment should be Computer Equipment</Description>
<TaxType>NONE</TaxType><TaxAmount>0.00</TaxAmount><LineAmount>-2569.00</LineAmount>
<AccountCode>720</AccountCode><AccountID>8d1ae68b-1251-4b44-9d7a-639b3976935c</AccountID>
</JournalLine></JournalLines>'
 )

Date is a reserved word, so enclose this value with square brackets.

SQL UPDATE example

UPDATE
 ManualJournals 
SET
 Narration = 'Narration Updated' 
WHERE
 ManualJournalID = '1811d1fa-fa76-41a2-a0f5-1fa982017591'

PurchaseOrders table

Supports INSERT and UPDATE statements.

SQL INSERT example

INSERT INTO
 PurchaseOrders ( ContactID, [Date], Reference, CurrencyCode, CurrencyRate, LineItems) 
VALUES
 (
 '699f0091-b127-4796-9f15-41a2f42abeb2', '2023-02-07', 'POTest 1', 'USD',
 0.725361, '<LineItems><LineItem><ItemCode>GB9-White</ItemCode>
 <Description>Golf balls - white - 9-pack</Description>
 <UnitAmount>25.0000</UnitAmount><TaxType>INPUT2</TaxType>
 <TaxAmount>41.67</TaxAmount><LineAmount>250.00</LineAmount>
 <AccountCode>300</AccountCode><Quantity>10.0000</Quantity>
 <LineItemID>6a2045bd-c21c-454b-8e99-540ec96748f6</LineItemID>
 </LineItem></LineItems>' 
 )

SQL UPDATE example

UPDATE
 PurchaseOrders 
SET
 LineItems = REPLACE(LineItems, '<Quantity>10.0000</Quantity>', '<Quantity>2</Quantity>') 
WHERE
 PurchaseOrderId = 'd646f075-6bec-47a9-a59c-fe6e97c4f849'

Quotes tables

Supports INSERT and UPDATE statements.

SQL INSERT example

INSERT INTO
 Quotes ( [Date], ExpiryDue, CurrencyCode, CurrencyRate, ContactId, LineItems ) 
VALUES
 (
 '2024-02-01', '2024-03-01', 'USD', 1.3, '9ce626d2-14ea-463c-9fff-6785ab5f9bfb',
 '<LineItems><LineItem><LineItemID>2ce92ec0-a1bd-4782-8cdd-5edbfe498f90</LineItemID>
 <AccountCode>200</AccountCode><Description>Unlimited Clients LAN - 1 Server
 LAN</Description><UnitAmount>4800.0000</UnitAmount><DiscountRate>0.42</DiscountRate>
 <DiscountAmount>20.00</DiscountAmount><LineAmount>4780.00</LineAmount>
 <ItemCode>0017-99-9999-99-37-12-01</ItemCode><Quantity>1.0000</Quantity>
 <TaxAmount>0.00</TaxAmount><TaxType>NONE</TaxType></LineItem><LineItem>
 <LineItemID>51ec56a4-9663-475e-a6b9-1a93b484b0ef</LineItemID><AccountCode>200
 </AccountCode><Description>Unlimited Machines on LAN</Description>
 <UnitAmount>12499.0000</UnitAmount><DiscountRate>0.16</DiscountRate>
 <DiscountAmount>20.00</DiscountAmount><LineAmount>12479.00</LineAmount>
 <ItemCode>0026-99-9999-99-32-12-01</ItemCode><Quantity>1.0000</Quantity>
 <TaxAmount>0.00</TaxAmount><TaxType>NONE</TaxType></LineItem></LineItems>'
 )

The previous example inserts a two-line quote for a customer referenced by the CustomerId only. If you send a quote that uses an existing quote number, Xero will generate a new quote number for the quote.

Receipts table

Supports INSERT and UPDATE statements.

Although Receipts was deprecated in 2018 by Xero, the endpoint is still available.

SQL UPDATE example

UPDATE
 Receipts 
SET
 LineItems = '<LineItems><LineItem><Description>Drinks &amp; snacks for staff meeting</Description>
 <UnitAmount>35.20</UnitAmount><TaxType>INPUT2</TaxType>
 <TaxAmount>5.87</TaxAmount><LineAmount>35.20</LineAmount>
 <AccountCode>429</AccountCode><Quantity>1.0000</Quantity>
 <AccountID>f96c9458-d724-47bf-8f74-a9d5726465ce</AccountID>
 <DiscountEnteredAsPercent>true</DiscountEnteredAsPercent></LineItem></LineItems>' 
WHERE
 ReceiptId = '7b67f5d8-bee4-42a2-a184-6742a2f79df1'

TaxRates table

Supports INSERT and UPDATE statements.

SQL UPDATE example

UPDATE
 TaxRates 
SET
 TaxComponents = replace(TaxComponents, '<Rate>17.5000</Rate>', '<Rate>1.7500</Rate>') 
WHERE
 Name = '17.5% (VAT on Income)'

TrackingCategories table

Supports: INSERT and UPDATE statements.

SQL INSERT example

INSERT INTO TrackingCategories ( Name ) VALUES ('Van Deliveries')

To create a new option for these tracking categories, get the TrackingCategoryID:

SELECT LastID from LastId

then use the ID in a query similar to:

SELECT
 * 
FROM
 api 
WHERE
 url = 'TrackingCategories/201a3021-77de-495d-a233-1faaa40e2f64/Options' 
 AND Method = 'PUT' 
 AND XMLRequestBody = '<Name>TrackOpt 1</Name>'

which returns::

"URL", "Method", "Code", "XMLRequestBody", "Response", "XMLResponseBody"
"TrackingCategories/201a3021-77de-495d-a233-1faaa40e2f64/Options", "PUT", 200,
"<Name>TrackOpt 1</Name>", "OK",
"<Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Id>57a11291-f2b2-49fe-84f2-b8370af16278</Id>
<Status>OK</Status>
<ProviderName>Easysoft</ProviderName>
<DateTimeUTC>2024年02月07日T14:13:28.6181931Z</DateTimeUTC>
</Response>"

to fetch the new tracking category option:

SELECT
 * 
FROM
 api 
WHERE
 url = 'TrackingCategories/201a3021-77de-495d-a233-1faaa40e2f64/Options' 
 AND Method = 'PUT' 
 AND JSONRequestBody = '{ "Name": "New Track 5" }'

Formatting XML

This VBA function formats XML to improve readability. Use the code with the other VBA samples in this guide.

Add a reference to the "Microsoft XML, v6.0" library before running the code.

Public Function PrettyPrintXML(ByRef xml As String, ByRef RemoveXMLFirstLine As Boolean) As String
 Dim Reader As New SAXXMLReader60
 Dim Writer As New MXXMLWriter60
 Dim blHasError As Boolean
 Dim XMLData As String
 
 On Error GoTo ErrHandle
 
 XMLData = xml
 
 Writer.Indent = True
 Writer.standalone = False
 Writer.omitXMLDeclaration = False
 Writer.Encoding = "utf-8"
 
 Set Reader.contentHandler = Writer
 Set Reader.dtdHandler = Writer
 Set Reader.ErrorHandler = Writer
 
 Call Reader.parse(XMLData)
 
 GoTo Finished
 ErrHandle:
 blHasError = True
 Finished:
 If blHasError Then
 PrettyPrintXML = "ERROR: " & vbCrLf & vbCrLf & XMLData
 Else
 XMLData = Writer.output
 If RemoveXMLFirstLine Then XMLData = Right(XMLData, Len(XMLData) - InStr(1, XMLData, ">", vbBinaryCompare) - 2)
 PrettyPrintXML = XMLData
 End If
End Function

Using the Xero ODBC driver with SQL Server

To run INSERT, UPDATE, or DELETE statements with a Xero ODBC driver-based linked server, set both these linked server properties to True: Server Options > RPC and Server Options > RPC Out. If you are only using the API table, leave these properties set to False.

HTTP 400 Bad Request

If you execute multiple INSERT, UPDATE, or DELETE statements in quick succession, the Xero API may return the 400 status and "Bad Request <no further details>." This seems to be a limitation of the Xero API. We worked around this by using a 0.5 second pause between statements.

If you get this error, contact the Easysoft Support team ().

SQL format and function support

The Easysoft ODBC-Xero driver supports the SQL-92 syntax:

• Use the 'yyyy-mm-dd' or the {d 'yyyy-mm-dd'} format if you send a date as a string.
• Enclose column names that are reserved words with square brackets round the column name or quotation marks. For example, "Name" or [Date].

Supported string functions

Supported numeric functions

Supported date and time functions