(SQL Server) Ibanity XS2A List Financial Institutions

See more Ibanity Examples

Demonstrates how to send a request to get a list of financial institutions.

For more information, see https://documentation.ibanity.com/xs2a/api/curl#authentication

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

    --  Send the following request:

    -- $ curl -X GET https://api.ibanity.com/xs2a/financial-institutions \
    -- --cert certificate.pem \
    -- --key private_key.pem \
    -- -H 'Signature: keyId="75b5d796-de5c-400a-81ce-e72371b01cbc",created=1599659223,algorithm="hs2019",headers="(request-target) digest (created) host",signature="BASE64(RSA-SHA256(SIGNING_STRING))"' \
    -- -H 'Digest: SHA-512=beDaRguyEb8fhh5wnl37bOTDtvhuYZyZNkTZ9LiC9Wc='

    -- Ibanity provides the certificate + private key in PFX format.  This example will use the .pfx instead of the pair of PEM files.
    -- (It is also possible to implement using Chilkat with the PEM files, but PFX is easier.)
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/my_ibanity_certificate.pfx', 'my_pfx_password'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- We need to calculate the Digest and Signature header fields.
    -- For a detailed explanation, see Calculate Ibanity HTTP Signature Example

    -- We'll just write the code here as briefly as possible.

    DECLARE @dtNow int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.CkDateTime', @dtNow OUT

    EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
    DECLARE @created nvarchar(4000)
    EXEC sp_OAMethod @dtNow, 'GetAsUnixTimeStr', @created OUT, 0

    DECLARE @crypt2 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Crypt2', @crypt2 OUT

    EXEC sp_OASetProperty @crypt2, 'HashAlgorithm', 'sha512'
    EXEC sp_OASetProperty @crypt2, 'EncodingMode', 'base64'

    DECLARE @sbDigestHdrValue int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbDigestHdrValue OUT

    EXEC sp_OAMethod @sbDigestHdrValue, 'Append', @success OUT, 'SHA-512='
    -- GET requests have empty payloads.  The SHA-512 hash of the empty string is the same for all GET requests.
    -- Therefore all GET requests will use "z4PhNX7vuL3xVChQ1m2AB9Yg5AULVxXcg/SpIdNs6c5H0NE8XYXysP+DGNKHfuwvY7kxvUdBeoGlODJ6+SfaPg=="
    -- You can eliminate the explicit hash computation (for GET requests) and simply use the above literal string.
    EXEC sp_OAMethod @crypt2, 'HashStringENC', @sTmp0 OUT, ''
    EXEC sp_OAMethod @sbDigestHdrValue, 'Append', @success OUT, @sTmp0


    PRINT 'Generated Digest'
    EXEC sp_OAMethod @sbDigestHdrValue, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @request_target nvarchar(4000)
    SELECT @request_target = 'get /xs2a/financial-institutions'

    DECLARE @sbSigningString int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbSigningString OUT

    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, '(request-target): '
    EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, @request_target, 0
    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, 'host: '
    EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, 'api.ibanity.com', 0
    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, 'digest: '
    EXEC sp_OAMethod @sbDigestHdrValue, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, @sTmp0, 0
    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, '(created): '
    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, @created
    -- ibanity-idempotency-key is not used with GET requests.


    PRINT 'Signing String:'
    EXEC sp_OAMethod @sbSigningString, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @signed_headers_list nvarchar(4000)
    SELECT @signed_headers_list = '(request-target) host digest (created)'

    DECLARE @privKey int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @privKey, 'LoadEncryptedPemFile', @success OUT, 'my_ibanity_signature_private_key.pem', 'pem_password'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @crypt2
        EXEC @hr = sp_OADestroy @sbDigestHdrValue
        EXEC @hr = sp_OADestroy @sbSigningString
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    DECLARE @rsa int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Rsa', @rsa OUT

    EXEC sp_OASetProperty @rsa, 'PssSaltLen', 32
    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
    -- Use the RSASSA-PSS signature algorithm
    EXEC sp_OASetProperty @rsa, 'OaepPadding', 1

    EXEC sp_OAMethod @rsa, 'ImportPrivateKeyObj', @success OUT, @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @crypt2
        EXEC @hr = sp_OADestroy @sbDigestHdrValue
        EXEC @hr = sp_OADestroy @sbSigningString
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- Sign the signing string.
    DECLARE @sigBase64 nvarchar(4000)
    EXEC sp_OAMethod @sbSigningString, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rsa, 'SignStringENC', @sigBase64 OUT, @sTmp0, 'sha-256'
    EXEC sp_OAGetProperty @rsa, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @crypt2
        EXEC @hr = sp_OADestroy @sbDigestHdrValue
        EXEC @hr = sp_OADestroy @sbSigningString
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END


    PRINT 'Signature:'

    PRINT @sigBase64

    -- Build the signature header value.
    DECLARE @sbSigHeaderValue int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbSigHeaderValue OUT

    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, 'keyId="'
    -- Use your identifier for the application's signature certificate, obtained from the Developer Portal
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, 'a0ce296d-84c8-4bd5-8eb4-de0339950cfa'
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '",created='
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, @created
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, ',algorithm="hs2019",headers="'
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, @signed_headers_list
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '",signature="'
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, @sigBase64
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '"'

    -- Send the GET request..
    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT

    EXEC sp_OAMethod @http, 'SetSslClientCert', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @crypt2
        EXEC @hr = sp_OADestroy @sbDigestHdrValue
        EXEC @hr = sp_OADestroy @sbSigningString
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @sbSigHeaderValue
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    EXEC sp_OAMethod @sbSigHeaderValue, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Signature', @sTmp0
    EXEC sp_OAMethod @sbDigestHdrValue, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Digest', @sTmp0

    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://api.ibanity.com/xs2a/financial-institutions', @sbResponseBody
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @crypt2
        EXEC @hr = sp_OADestroy @sbDigestHdrValue
        EXEC @hr = sp_OADestroy @sbSigningString
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @sbSigHeaderValue
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @jResp int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jResp OUT

    EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jResp, 'EmitCompact', 0


    PRINT 'Response Body:'
    EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @http, 'LastStatus', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @crypt2
        EXEC @hr = sp_OADestroy @sbDigestHdrValue
        EXEC @hr = sp_OADestroy @sbSigningString
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @sbSigHeaderValue
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END

    -- Sample output:
    -- (Sample code for parsing the JSON response is shown below)

    -- {
    --   "data": [
    --     {
    --       "attributes": {
    --         "authorizationModels": [
    --           "detailed",
    --           "financialInstitutionOffered"
    --         ],
    --         "bic": "NBBEBEBB203",
    --         "bulkPaymentsEnabled": true,
    --         "bulkPaymentsProductTypes": [
    --           "sepaCreditTransfer"
    --         ],
    --         "country": null,
    --         "financialInstitutionCustomerReferenceRequired": false,
    --         "futureDatedPaymentsAllowed": true,
    --         "logoUrl": "https://s3.eu-central-1.amazonaws.com/ibanity-production-financial-institution-assets/sandbox.png",
    --         "maintenanceFrom": null,
    --         "maintenanceTo": null,
    --         "maintenanceType": null,
    --         "maxRequestedAccountReferences": null,
    --         "minRequestedAccountReferences": 0,
    --         "name": "Bogus Financial",
    --         "paymentsEnabled": true,
    --         "paymentsProductTypes": [
    --           "sepaCreditTransfer"
    --         ],
    --         "periodicPaymentsEnabled": true,
    --         "periodicPaymentsProductTypes": [
    --           "sepaCreditTransfer"
    --         ],
    --         "primaryColor": "#7d39ff",
    --         "requiresCredentialStorage": false,
    --         "requiresCustomerIpAddress": false,
    --         "sandbox": true,
    --         "secondaryColor": "#3DF2C2",
    --         "sharedBrandName": null,
    --         "sharedBrandReference": null,
    --         "status": "beta"
    --       },
    --       "id": "2d3d70a4-cb3c-477c-97e1-cbe495b82841",
    --       "links": {
    --         "self": "https://api.ibanity.com/xs2a/financial-institutions/2d3d70a4-cb3c-477c-97e1-cbe495b82841"
    --       },
    --       "type": "financialInstitution"
    --     },
    --     {
    --       "attributes": {
    --         "authorizationModels": [
    --           "detailed",
    --           "financialInstitutionOffered"
    --         ],
    --         "bic": "NBBEBEBB203",
    --         "bulkPaymentsEnabled": true,
    --         "bulkPaymentsProductTypes": [
    --           "sepaCreditTransfer"
    --         ],
    --         "country": null,
    --         "financialInstitutionCustomerReferenceRequired": false,
    --         "futureDatedPaymentsAllowed": true,
    --         "logoUrl": "https://s3.eu-central-1.amazonaws.com/ibanity-production-financial-institution-assets/sandbox.png",
    --         "maintenanceFrom": null,
    --         "maintenanceTo": null,
    --         "maintenanceType": null,
    --         "maxRequestedAccountReferences": null,
    --         "minRequestedAccountReferences": 0,
    --         "name": "XYZ Trust",
    --         "paymentsEnabled": true,
    --         "paymentsProductTypes": [
    --           "sepaCreditTransfer"
    --         ],
    --         "periodicPaymentsEnabled": true,
    --         "periodicPaymentsProductTypes": [
    --           "sepaCreditTransfer"
    --         ],
    --         "primaryColor": "#7d39ff",
    --         "requiresCredentialStorage": false,
    --         "requiresCustomerIpAddress": false,
    --         "sandbox": true,
    --         "secondaryColor": "#3DF2C2",
    --         "sharedBrandName": null,
    --         "sharedBrandReference": null,
    --         "status": "beta"
    --       },
    --       "id": "d4100f28-936b-4379-a3f8-86314a2014fb",
    --       "links": {
    --         "self": "https://api.ibanity.com/xs2a/financial-institutions/d4100f28-936b-4379-a3f8-86314a2014fb"
    --       },
    --       "type": "financialInstitution"
    --     }
    --   ],
    --   "links": {
    --     "first": "https://api.ibanity.com/xs2a/financial-institutions"
    --   },
    --   "meta": {
    --     "paging": {
    --       "limit": 10
    --     }
    --   }
    -- }

    -- Sample code for parsing the JSON response...
    -- Use the following online tool to generate parsing code from sample JSON:
    -- Generate Parsing Code from JSON

    DECLARE @attributesBic nvarchar(4000)

    DECLARE @attributesBulkPaymentsEnabled int

    DECLARE @attributesCountry nvarchar(4000)

    DECLARE @attributesFinancialInstitutionCustomerReferenceRequired int

    DECLARE @attributesFutureDatedPaymentsAllowed int

    DECLARE @attributesLogoUrl nvarchar(4000)

    DECLARE @attributesMaintenanceFrom nvarchar(4000)

    DECLARE @attributesMaintenanceTo nvarchar(4000)

    DECLARE @attributesMaintenanceType nvarchar(4000)

    DECLARE @attributesMaxRequestedAccountReferences nvarchar(4000)

    DECLARE @attributesMinRequestedAccountReferences int

    DECLARE @attributesName nvarchar(4000)

    DECLARE @attributesPaymentsEnabled int

    DECLARE @attributesPeriodicPaymentsEnabled int

    DECLARE @attributesPrimaryColor nvarchar(4000)

    DECLARE @attributesRequiresCredentialStorage int

    DECLARE @attributesRequiresCustomerIpAddress int

    DECLARE @attributesSandbox int

    DECLARE @attributesSecondaryColor nvarchar(4000)

    DECLARE @attributesSharedBrandName nvarchar(4000)

    DECLARE @attributesSharedBrandReference nvarchar(4000)

    DECLARE @attributesStatus nvarchar(4000)

    DECLARE @id nvarchar(4000)

    DECLARE @linksSelf nvarchar(4000)

    DECLARE @v_type nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @strVal nvarchar(4000)

    DECLARE @linksFirst nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @linksFirst OUT, 'links.first'
    DECLARE @metaPagingLimit int
    EXEC sp_OAMethod @jResp, 'IntOf', @metaPagingLimit OUT, 'meta.paging.limit'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'data'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesBic OUT, 'data[i].attributes.bic'
        EXEC sp_OAMethod @jResp, 'BoolOf', @attributesBulkPaymentsEnabled OUT, 'data[i].attributes.bulkPaymentsEnabled'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesCountry OUT, 'data[i].attributes.country'
        EXEC sp_OAMethod @jResp, 'BoolOf', @attributesFinancialInstitutionCustomerReferenceRequired OUT, 'data[i].attributes.financialInstitutionCustomerReferenceRequired'
        EXEC sp_OAMethod @jResp, 'BoolOf', @attributesFutureDatedPaymentsAllowed OUT, 'data[i].attributes.futureDatedPaymentsAllowed'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesLogoUrl OUT, 'data[i].attributes.logoUrl'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesMaintenanceFrom OUT, 'data[i].attributes.maintenanceFrom'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesMaintenanceTo OUT, 'data[i].attributes.maintenanceTo'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesMaintenanceType OUT, 'data[i].attributes.maintenanceType'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesMaxRequestedAccountReferences OUT, 'data[i].attributes.maxRequestedAccountReferences'
        EXEC sp_OAMethod @jResp, 'IntOf', @attributesMinRequestedAccountReferences OUT, 'data[i].attributes.minRequestedAccountReferences'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesName OUT, 'data[i].attributes.name'
        EXEC sp_OAMethod @jResp, 'BoolOf', @attributesPaymentsEnabled OUT, 'data[i].attributes.paymentsEnabled'
        EXEC sp_OAMethod @jResp, 'BoolOf', @attributesPeriodicPaymentsEnabled OUT, 'data[i].attributes.periodicPaymentsEnabled'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesPrimaryColor OUT, 'data[i].attributes.primaryColor'
        EXEC sp_OAMethod @jResp, 'BoolOf', @attributesRequiresCredentialStorage OUT, 'data[i].attributes.requiresCredentialStorage'
        EXEC sp_OAMethod @jResp, 'BoolOf', @attributesRequiresCustomerIpAddress OUT, 'data[i].attributes.requiresCustomerIpAddress'
        EXEC sp_OAMethod @jResp, 'BoolOf', @attributesSandbox OUT, 'data[i].attributes.sandbox'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesSecondaryColor OUT, 'data[i].attributes.secondaryColor'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesSharedBrandName OUT, 'data[i].attributes.sharedBrandName'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesSharedBrandReference OUT, 'data[i].attributes.sharedBrandReference'
        EXEC sp_OAMethod @jResp, 'StringOf', @attributesStatus OUT, 'data[i].attributes.status'
        EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'data[i].id'
        EXEC sp_OAMethod @jResp, 'StringOf', @linksSelf OUT, 'data[i].links.self'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'data[i].type'
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data[i].attributes.authorizationModels'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data[i].attributes.authorizationModels[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data[i].attributes.bulkPaymentsProductTypes'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data[i].attributes.bulkPaymentsProductTypes[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data[i].attributes.paymentsProductTypes'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data[i].attributes.paymentsProductTypes[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data[i].attributes.periodicPaymentsProductTypes'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data[i].attributes.periodicPaymentsProductTypes[j]'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @dtNow
    EXEC @hr = sp_OADestroy @crypt2
    EXEC @hr = sp_OADestroy @sbDigestHdrValue
    EXEC @hr = sp_OADestroy @sbSigningString
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @sbSigHeaderValue
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO