Xero Effort -as easy as falling off a log

We have been moving our accounting from an in house system that took too much time to look after and was not core business to starting to use Xero. I have been keen to get using an accounting  system that has a decent API to give us a chance to do some integration work from our Winery Software. To date I have looked at MYOB and Quickbooks but have been put off by their desktop centric view and how difficult this is when trying to get even some simple integration working.
Enter Xero stage left, after one day I had written an automated routine to post our sales orders into Xero as draft invoices, deal with any validation messages and sync the results back to our system.
2 things made this fall over easy:

1. Me brushing up on my vague notions about MSSQL’s XML handling abilities.
2. Xero’s great API Previewer that allows you to GET, CREATE and UPDATE a good range of the systems accounting entities.

Firstly, having done a few integration jobs in the past, getting data out of one system and knocked into shape (literally), suitable for inserting into another, is an often tedious job. Previous jobs have been variations of: Start with some object that in no way resembles what you need it to be, then try to kludge it into another object that resembles the destination, and finally try to get this object to serialize to XML just-so, finally you are ready to spend hours of painful integration testing with long slow iterations to figure out if your request and response handling is up to scratch.

Enter MSSQL and the ‘FOR XML’ clause. Previously I was stuck with doing XSL transformations of DataTables, or writing custom serialization to get these transformation done, however extracting and shaping an XML result from a SELECT statement was so easy!!
Consider the source db of a Contact table (Cnt), an AP Invoice header table (Cdr) , Line Items (CdrLgr) as well as currency (Cur) and chart of accounts info (Lgr).  In a simple SQL statement I can acheive all of the SELECT, TRANSFORM and serialization work in one step!

SELECT 'ACCPAY' AS [Type], --Nested Single Contact Element, note path is empty as it is a single element (SELECT Cnt AS Name FROM Cnt WHERE Cdr.CntNum=Cnt.CntNum FOR XML PATH(''),TYPE) AS Contact, Dat AS [Date], ISNULL(DatPytDue,Dat) AS DueDate, Ref AS InvoiceNumber, (SELECT Cod FROM Cur Cur WHERE EntNum=CurNum) AS CurrencyCode, 'Exclusive' AS LineAmountTypes, --Nested 'LineItems' Node Collection, with LineItem elements, --note path is set with the element name LineItem and subquery is aliased to LineItems --to give the node collection element name (SELECT Cdr.Dsc AS [Description], 1 AS Quantity, CONVERT(money,Amt) AS UnitAmount, CASE WHEN Abs(CdrLgr.IsSalTaxLib)=1 THEN 'INPUT' ELSE 'NONE' END AS TaxType, 469 AS AccountCode FROM CdrLgr LEFT JOIN Lgr ON Lgr.EntNum=CdrLgr.LgrNum WHERE CdrLgr.CdrNum=Cdr.CdrNum AND CdrLgr.Amt>=0 FOR XML PATH('LineItem'),TYPE) AS LineItems FROM Cdr WHERE CdrNum IN (1,2,5,6) --Root Invoices Element with a collection of Invoice nodes FOR XML PATH('Invoice'),ROOT('Invoices')

And this produces some nice nested (but not in my post!) xml ready for use in the API previewer.

From here, it is a simple matter of logging in to the API previewer and pasting and executing to your heart’s content.

The previewer has a request body area and a response body area so you can try out variations of the entities you wish to post and see the results in the response area.

This was particularly useful in error handling for invalid request data. The one area they could work on here is that if you send multiple entities, some valid and some invalid, only the validation errors come back in the response, which means that you cannot parse the response and retrieve the genereted invoice numbers for the successful AR invoices.

The other kick arse thing is that, really the Xero API Previewer is an app in itself, I used it to import all the adhoc transactions that I wasn’t going to reuse again once Xero was setup.

I hope the team at Xero continue to improve the API and extend it as it truely enables some easy app extension into the world of accounting.