SQL Server
SQL Server
Xero Get Invoices
See more Xero Examples
Get Xero company invoices.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/xero-access-token.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @jsonToken, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- Replace the value here with an actual tenant ID obtained from this example:
-- Get Xero Tenant IDs
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Xero-tenant-id', '83299b9e-5747-4a14-a18a-a6c94f824eb7'
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://api.xero.com/api.xro/2.0/Invoices', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response Status Code: ' + @iTmp0
DECLARE @jsonResponse int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @jsonResponse, 'Load', @success OUT, @sTmp0
EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonResponse
RETURN
END
-- Sample output...
-- (See the parsing code below..)
--
-- Use the this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "Id": "bda1f62f-0d63-4178-8d54-e91fd226987a",
-- "Status": "OK",
-- "ProviderName": "Chilkat2222",
-- "DateTimeUTC": "\/Date(1587210234493)\/",
-- "Invoices": [
-- {
-- "Type": "ACCPAY",
-- "InvoiceID": "0032f627-3156-4d30-9b1c-4d3b994dc921",
-- "InvoiceNumber": "9871",
-- "Reference": "",
-- "Payments": [
-- {
-- "PaymentID": "22974891-3689-4694-9ee7-fd2ba917af55",
-- "Date": "\/Date(1579737600000+0000)\/",
-- "Amount": 148.50,
-- "Reference": "Chq 409",
-- "HasAccount": false,
-- "HasValidationErrors": false
-- }
-- ],
-- "CreditNotes": [
-- ],
-- "Prepayments": [
-- ],
-- "Overpayments": [
-- ],
-- "AmountDue": 0.00,
-- "AmountPaid": 148.50,
-- "AmountCredited": 0.00,
-- "IsDiscounted": false,
-- "HasAttachments": false,
-- "HasErrors": false,
-- "Contact": {
-- "ContactID": "d6a384fb-f46f-41a3-8ac7-b7bc9e0b5efa",
-- "Name": "Melrose Parking",
-- "Addresses": [
-- ],
-- "Phones": [
-- ],
-- "ContactGroups": [
-- ],
-- "ContactPersons": [
-- ],
-- "HasValidationErrors": false
-- },
-- "DateString": "2020-01-15T00:00:00",
-- "Date": "\/Date(1579046400000+0000)\/",
-- "DueDateString": "2020-01-24T00:00:00",
-- "DueDate": "\/Date(1579824000000+0000)\/",
-- "Status": "PAID",
-- "LineAmountTypes": "Exclusive",
-- "LineItems": [
-- ],
-- "SubTotal": 135.00,
-- "TotalTax": 13.50,
-- "Total": 148.50,
-- "UpdatedDateUTC": "\/Date(1221560931500+0000)\/",
-- "CurrencyCode": "AUD",
-- "FullyPaidOnDate": "\/Date(1579737600000+0000)\/"
-- },
-- {
-- "Type": "ACCPAY",
-- "InvoiceID": "673dd7cc-beb7-4697-83d4-0c47cb400cc2",
-- "InvoiceNumber": "",
-- "Reference": "",
-- "Payments": [
-- {
-- "PaymentID": "4d06f609-5200-4364-9c8b-d4379a945252",
-- "Date": "\/Date(1580688000000+0000)\/",
-- "Amount": 974.60,
-- "Reference": "DD # 96013",
-- "HasAccount": false,
-- "HasValidationErrors": false
-- }
-- ],
-- "CreditNotes": [
-- {
-- "CreditNoteID": "7df8949c-b71f-40c0-bbcf-39f2f450f286",
-- "CreditNoteNumber": "03391",
-- "ID": "7df8949c-b71f-40c0-bbcf-39f2f450f286",
-- "HasErrors": false,
-- "AppliedAmount": 218.90,
-- "DateString": "2020-01-29T00:00:00",
-- "Date": "\/Date(1580256000000+0000)\/",
-- "LineItems": [
-- ],
-- "Total": 218.90
-- }
-- ],
-- "Prepayments": [
-- ],
-- "Overpayments": [
-- ],
-- "AmountDue": 0.00,
-- "AmountPaid": 974.60,
-- "AmountCredited": 218.90,
-- "IsDiscounted": false,
-- "HasAttachments": false,
-- "HasErrors": false,
-- "Contact": {
-- "ContactID": "d0cd2c4f-18a0-4f7c-a32a-2db00f29d298",
-- "Name": "PC Complete",
-- "Addresses": [
-- ],
-- "Phones": [
-- ],
-- "ContactGroups": [
-- ],
-- "ContactPersons": [
-- ],
-- "HasValidationErrors": false
-- },
-- "DateString": "2020-01-28T00:00:00",
-- "Date": "\/Date(1580169600000+0000)\/",
-- "DueDateString": "2020-02-04T00:00:00",
-- "DueDate": "\/Date(1580774400000+0000)\/",
-- "Status": "PAID",
-- "LineAmountTypes": "Exclusive",
-- "LineItems": [
-- ],
-- "SubTotal": 1085.00,
-- "TotalTax": 108.50,
-- "Total": 1193.50,
-- "UpdatedDateUTC": "\/Date(1221561913790+0000)\/",
-- "CurrencyCode": "AUD",
-- "FullyPaidOnDate": "\/Date(1580688000000+0000)\/"
-- },
-- {
-- "Type": "ACCPAY",
-- "InvoiceID": "c12aff7e-12bf-4185-8702-460929f19674",
-- "InvoiceNumber": "",
-- "Reference": "",
-- "Payments": [
-- ],
-- "CreditNotes": [
-- ],
-- "Prepayments": [
-- ],
-- "Overpayments": [
-- ],
-- "AmountDue": 2166.99,
-- "AmountPaid": 0.00,
-- "AmountCredited": 0.00,
-- "CurrencyRate": 1.000000,
-- "IsDiscounted": false,
-- "HasAttachments": false,
-- "HasErrors": false,
-- "Contact": {
-- "ContactID": "d0cd2c4f-18a0-4f7c-a32a-2db00f29d298",
-- "Name": "PC Complete",
-- "Addresses": [
-- ],
-- "Phones": [
-- ],
-- "ContactGroups": [
-- ],
-- "ContactPersons": [
-- ],
-- "HasValidationErrors": false
-- },
-- "DateString": "2020-04-10T00:00:00",
-- "Date": "\/Date(1586476800000+0000)\/",
-- "DueDateString": "2020-05-05T00:00:00",
-- "DueDate": "\/Date(1588636800000+0000)\/",
-- "Status": "AUTHORISED",
-- "LineAmountTypes": "Exclusive",
-- "LineItems": [
-- ],
-- "SubTotal": 1969.99,
-- "TotalTax": 197.00,
-- "Total": 2166.99,
-- "UpdatedDateUTC": "\/Date(1497965301980+0000)\/",
-- "CurrencyCode": "AUD"
-- }
-- ]
-- }
--
DECLARE @Type nvarchar(4000)
DECLARE @InvoiceID nvarchar(4000)
DECLARE @InvoiceNumber nvarchar(4000)
DECLARE @Reference nvarchar(4000)
DECLARE @AmountDue nvarchar(4000)
DECLARE @AmountPaid nvarchar(4000)
DECLARE @AmountCredited nvarchar(4000)
DECLARE @IsDiscounted int
DECLARE @HasAttachments int
DECLARE @HasErrors int
DECLARE @ContactContactID nvarchar(4000)
DECLARE @ContactName nvarchar(4000)
DECLARE @ContactHasValidationErrors int
DECLARE @DateString nvarchar(4000)
DECLARE @Date nvarchar(4000)
DECLARE @DueDateString nvarchar(4000)
DECLARE @DueDate nvarchar(4000)
DECLARE @LineAmountTypes nvarchar(4000)
DECLARE @SubTotal nvarchar(4000)
DECLARE @TotalTax nvarchar(4000)
DECLARE @Total nvarchar(4000)
DECLARE @UpdatedDateUTC nvarchar(4000)
DECLARE @CurrencyCode nvarchar(4000)
DECLARE @FullyPaidOnDate nvarchar(4000)
DECLARE @CurrencyRate nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @PaymentID nvarchar(4000)
DECLARE @Amount nvarchar(4000)
DECLARE @HasAccount int
DECLARE @HasValidationErrors int
DECLARE @CreditNoteID nvarchar(4000)
DECLARE @CreditNoteNumber nvarchar(4000)
DECLARE @ID nvarchar(4000)
DECLARE @AppliedAmount nvarchar(4000)
DECLARE @k int
DECLARE @count_k int
DECLARE @Id nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Id OUT, 'Id'
DECLARE @Status nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Status OUT, 'Status'
DECLARE @ProviderName nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @ProviderName OUT, 'ProviderName'
DECLARE @DateTimeUTC nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @DateTimeUTC OUT, 'DateTimeUTC'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_i OUT, 'Invoices'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'I', @i
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Type OUT, 'Invoices[i].Type'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceID OUT, 'Invoices[i].InvoiceID'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceNumber OUT, 'Invoices[i].InvoiceNumber'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Reference OUT, 'Invoices[i].Reference'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @AmountDue OUT, 'Invoices[i].AmountDue'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @AmountPaid OUT, 'Invoices[i].AmountPaid'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @AmountCredited OUT, 'Invoices[i].AmountCredited'
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @IsDiscounted OUT, 'Invoices[i].IsDiscounted'
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @HasAttachments OUT, 'Invoices[i].HasAttachments'
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @HasErrors OUT, 'Invoices[i].HasErrors'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @ContactContactID OUT, 'Invoices[i].Contact.ContactID'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @ContactName OUT, 'Invoices[i].Contact.Name'
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @ContactHasValidationErrors OUT, 'Invoices[i].Contact.HasValidationErrors'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @DateString OUT, 'Invoices[i].DateString'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Date OUT, 'Invoices[i].Date'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @DueDateString OUT, 'Invoices[i].DueDateString'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @DueDate OUT, 'Invoices[i].DueDate'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Status OUT, 'Invoices[i].Status'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @LineAmountTypes OUT, 'Invoices[i].LineAmountTypes'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @SubTotal OUT, 'Invoices[i].SubTotal'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @TotalTax OUT, 'Invoices[i].TotalTax'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Total OUT, 'Invoices[i].Total'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @UpdatedDateUTC OUT, 'Invoices[i].UpdatedDateUTC'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CurrencyCode OUT, 'Invoices[i].CurrencyCode'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @FullyPaidOnDate OUT, 'Invoices[i].FullyPaidOnDate'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CurrencyRate OUT, 'Invoices[i].CurrencyRate'
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'Invoices[i].Payments'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
EXEC sp_OAMethod @jsonResponse, 'StringOf', @PaymentID OUT, 'Invoices[i].Payments[j].PaymentID'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Date OUT, 'Invoices[i].Payments[j].Date'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Amount OUT, 'Invoices[i].Payments[j].Amount'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Reference OUT, 'Invoices[i].Payments[j].Reference'
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @HasAccount OUT, 'Invoices[i].Payments[j].HasAccount'
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @HasValidationErrors OUT, 'Invoices[i].Payments[j].HasValidationErrors'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'Invoices[i].CreditNotes'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CreditNoteID OUT, 'Invoices[i].CreditNotes[j].CreditNoteID'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CreditNoteNumber OUT, 'Invoices[i].CreditNotes[j].CreditNoteNumber'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @ID OUT, 'Invoices[i].CreditNotes[j].ID'
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @HasErrors OUT, 'Invoices[i].CreditNotes[j].HasErrors'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @AppliedAmount OUT, 'Invoices[i].CreditNotes[j].AppliedAmount'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @DateString OUT, 'Invoices[i].CreditNotes[j].DateString'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Date OUT, 'Invoices[i].CreditNotes[j].Date'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @Total OUT, 'Invoices[i].CreditNotes[j].Total'
SELECT @k = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_k OUT, 'Invoices[i].CreditNotes[j].LineItems'
WHILE @k < @count_k
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'K', @k
SELECT @k = @k + 1
END
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'Invoices[i].Prepayments'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'Invoices[i].Overpayments'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'Invoices[i].Contact.Addresses'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'Invoices[i].Contact.Phones'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'Invoices[i].Contact.ContactGroups'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'Invoices[i].Contact.ContactPersons'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'Invoices[i].LineItems'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonResponse
END
GO