(SQL Server) Xero Get Invoices

Get Xero company invoices.

For more information, see https://developer.xero.com/documentation/api/invoices

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @jsonToken int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT

    DECLARE @success int
    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 sp_OAMethod @http, 'QuickRequest', @resp OUT, 'GET', 'https://api.xero.com/api.xro/2.0/Invoices'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response Status Code: ' + @iTmp0

    DECLARE @jsonResponse int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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 @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @jsonResponse
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    -- 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 @jsonResponse


END
GO