(SQL Server) Xero Get Payroll Employees

Download Xero employees information

For more information, see https://developer.xero.com/documentation/payroll-api/employees

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/payroll.xro/1.0/Employees'
    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 @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, '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 @json
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    -- Sample response
    -- (See the parsing code below..)
    -- 
    -- Use the this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    -- {
    --   "Id": "796cae61-a7c3-425b-a621-372ab1f29460",
    --   "Status": "OK",
    --   "ProviderName": "Chilkat2222",
    --   "DateTimeUTC": "\/Date(1587075322768)\/",
    --   "Employees": [
    --     {
    --       "EmployeeID": "f12e3b0b-4b4c-4523-b8c0-f481a07bfd13",
    --       "FirstName": "James",
    --       "LastName": "Lebron",
    --       "Status": "ACTIVE",
    --       "Email": "JL@madeup.email.com",
    --       "DateOfBirth": "\/Date(492739200000+0000)\/",
    --       "Gender": "M",
    --       "Phone": "0400-000-123",
    --       "Mobile": " 408-230-9732",
    --       "StartDate": "\/Date(1548806400000+0000)\/",
    --       "OrdinaryEarningsRateID": "e648f7bf-af43-41bc-98ea-e7ca94c3e3b1",
    --       "PayrollCalendarID": "2cf6326a-d48b-41bf-bf36-7a73b42dd71b",
    --       "UpdatedDateUTC": "\/Date(1587075120000+0000)\/"
    --     },
    --     {
    --       "EmployeeID": "d9f5979f-1de8-4040-aa1d-f1fdd327f893",
    --       "FirstName": "Odette",
    --       "LastName": "Garrison",
    --       "Status": "ACTIVE",
    --       "Email": "ogg@madeup.email.com",
    --       "DateOfBirth": "\/Date(390528000000+0000)\/",
    --       "Gender": "F",
    --       "Phone": "9000 1234",
    --       "StartDate": "\/Date(1554422400000+0000)\/",
    --       "OrdinaryEarningsRateID": "e648f7bf-af43-41bc-98ea-e7ca94c3e3b1",
    --       "PayrollCalendarID": "ec0f2f68-f4d1-4490-af70-78e5d7f5dcbd",
    --       "UpdatedDateUTC": "\/Date(1587075120000+0000)\/"
    --     },
    --     {
    --       "EmployeeID": "22879fb4-d13b-46ed-9c07-e43aba25b576",
    --       "FirstName": "Oliver",
    --       "LastName": "Gray",
    --       "Status": "ACTIVE",
    --       "Email": "og@madeup.email.com",
    --       "DateOfBirth": "\/Date(94176000000+0000)\/",
    --       "Gender": "M",
    --       "Phone": "0401 123 456",
    --       "StartDate": "\/Date(1562112000000+0000)\/",
    --       "OrdinaryEarningsRateID": "e648f7bf-af43-41bc-98ea-e7ca94c3e3b1",
    --       "PayrollCalendarID": "2cf6326a-d48b-41bf-bf36-7a73b42dd71b",
    --       "UpdatedDateUTC": "\/Date(1587075120000+0000)\/"
    --     },
    --     {
    --       "EmployeeID": "c4d82c18-b1a9-4b6c-982c-14b45b95c10a",
    --       "FirstName": "Sally ",
    --       "LastName": "Martin",
    --       "Status": "ACTIVE",
    --       "Email": "sm@madeup3993.com",
    --       "DateOfBirth": "\/Date(659577600000+0000)\/",
    --       "Gender": "F",
    --       "Phone": "0400 123 456",
    --       "StartDate": "\/Date(1577750400000+0000)\/",
    --       "OrdinaryEarningsRateID": "e648f7bf-af43-41bc-98ea-e7ca94c3e3b1",
    --       "PayrollCalendarID": "2cf6326a-d48b-41bf-bf36-7a73b42dd71b",
    --       "UpdatedDateUTC": "\/Date(1587075120000+0000)\/"
    --     },
    --     {
    --       "EmployeeID": "e1a5eed0-721e-482d-b1f1-0b73506f26fb",
    --       "FirstName": "Sonia",
    --       "LastName": "Michaels",
    --       "Status": "ACTIVE",
    --       "Email": "som@madeup3993.com",
    --       "DateOfBirth": "\/Date(727660800000+0000)\/",
    --       "Gender": "F",
    --       "Phone": "02 2345 6789",
    --       "Mobile": " ",
    --       "UpdatedDateUTC": "\/Date(1587075120000+0000)\/"
    --     },
    --     {
    --       "EmployeeID": "882587ac-1cfe-4f55-9b37-932b0705cd04",
    --       "FirstName": "Tracy",
    --       "LastName": "Green",
    --       "Status": "ACTIVE",
    --       "Email": "tg@madeup.email.com",
    --       "DateOfBirth": "\/Date(712886400000+0000)\/",
    --       "Gender": "F",
    --       "Phone": "0402 123 456",
    --       "StartDate": "\/Date(1556755200000+0000)\/",
    --       "OrdinaryEarningsRateID": "e648f7bf-af43-41bc-98ea-e7ca94c3e3b1",
    --       "PayrollCalendarID": "ec0f2f68-f4d1-4490-af70-78e5d7f5dcbd",
    --       "UpdatedDateUTC": "\/Date(1587075120000+0000)\/"
    --     }
    --   ]
    -- }

    DECLARE @EmployeeID nvarchar(4000)

    DECLARE @FirstName nvarchar(4000)

    DECLARE @LastName nvarchar(4000)

    DECLARE @v_Email nvarchar(4000)

    DECLARE @DateOfBirth nvarchar(4000)

    DECLARE @Gender nvarchar(4000)

    DECLARE @Phone nvarchar(4000)

    DECLARE @Mobile nvarchar(4000)

    DECLARE @StartDate nvarchar(4000)

    DECLARE @OrdinaryEarningsRateID nvarchar(4000)

    DECLARE @PayrollCalendarID nvarchar(4000)

    DECLARE @UpdatedDateUTC nvarchar(4000)

    DECLARE @Id nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @Id OUT, 'Id'
    DECLARE @Status nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @Status OUT, 'Status'
    DECLARE @ProviderName nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @ProviderName OUT, 'ProviderName'
    DECLARE @DateTimeUTC nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @DateTimeUTC OUT, 'DateTimeUTC'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'Employees'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @EmployeeID OUT, 'Employees[i].EmployeeID'
        EXEC sp_OAMethod @json, 'StringOf', @FirstName OUT, 'Employees[i].FirstName'
        EXEC sp_OAMethod @json, 'StringOf', @LastName OUT, 'Employees[i].LastName'
        EXEC sp_OAMethod @json, 'StringOf', @Status OUT, 'Employees[i].Status'
        EXEC sp_OAMethod @json, 'StringOf', @v_Email OUT, 'Employees[i].Email'
        EXEC sp_OAMethod @json, 'StringOf', @DateOfBirth OUT, 'Employees[i].DateOfBirth'
        EXEC sp_OAMethod @json, 'StringOf', @Gender OUT, 'Employees[i].Gender'
        EXEC sp_OAMethod @json, 'StringOf', @Phone OUT, 'Employees[i].Phone'
        EXEC sp_OAMethod @json, 'StringOf', @Mobile OUT, 'Employees[i].Mobile'
        EXEC sp_OAMethod @json, 'StringOf', @StartDate OUT, 'Employees[i].StartDate'
        EXEC sp_OAMethod @json, 'StringOf', @OrdinaryEarningsRateID OUT, 'Employees[i].OrdinaryEarningsRateID'
        EXEC sp_OAMethod @json, 'StringOf', @PayrollCalendarID OUT, 'Employees[i].PayrollCalendarID'
        EXEC sp_OAMethod @json, 'StringOf', @UpdatedDateUTC OUT, 'Employees[i].UpdatedDateUTC'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @json


END
GO