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' )Īnd this produces some nice nested (but not in my post!) xml ready for use in the API previewer. IsSalTaxLib )=1 THEN 'INPUT' ELSE 'NONE' END AS TaxType, 469 AS AccountCode FROM CdrLgr LEFT JOIN Lgr ON Lgr. Dsc AS, 1 AS Quantity, CONVERT ( money, Amt ) AS UnitAmount, CASE WHEN Abs ( CdrLgr. CntNum FOR XML PATH ( '' ), TYPE ) AS Contact, Dat AS, ISNULL ( DatPytDue, Dat ) AS DueDate, Ref AS InvoiceNumber, ( SELECT Cod FROM Cur Cur WHERE EntNum = CurNum ) AS Currenc圜ode, '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. In a simple SQL statement I can acheive all of the SELECT, TRANSFORM and serialization work in one step! SELECT 'ACCPAY' AS, -Nested Single Contact Element, note path is empty as it is a single element ( SELECT Cnt AS Name FROM Cnt WHERE Cdr. 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!!Ĭonsider 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). 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.Įnter MSSQL and the ‘FOR XML’ clause. Xero’s great API Previewer that allows you to GET, CREATE and UPDATE a good range of the systems accounting entities.įirstly, 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.Me brushing up on my vague notions about MSSQL’s XML handling abilities. 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.Įnter 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. 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. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |