(SQL Server) Akeneo: Get List of Products (using StringBuilder)

Using a previously obtained request token, demonstrates how to get a list of products using a Chilkat StringBuilder. Using the StringBuilder is convenient because it prevents returning a potentially large string. Instead, the result is deposited into the StringBuilder object.

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

    -- Use your previously obtained access token.
    -- See Get Akeneo Access Token
    EXEC sp_OASetProperty @http, 'AuthToken', 'access_token'

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

    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'http://pim.my-akeneo-site.com/api/rest/v1/products', @sbJson
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbJson
        RETURN
      END

    -- A sample response looks like this.
    -- See below for sample code to parse the JSON response..

    -- {
    --   "_links": {
    --     "self": {
    --       "href": "https://demo.akeneo.com/api/rest/v1/products?page=3&limit=3"
    --     },
    --     "first": {
    --       "href": "https://demo.akeneo.com/api/rest/v1/products?page=1&limit=3"
    --     },
    --     "previous": {
    --       "href": "https://demo.akeneo.com/api/rest/v1/products?page=2&limit=3"
    --     },
    --     "next": {
    --       "href": "https://demo.akeneo.com/api/rest/v1/products?page=4&limit=3"
    --     }
    --   },
    --   "current_page": 3,
    --   "_embedded": {
    --     "_items": [
    --       {
    --         "_links": {
    --           "self": {
    --             "href": "https://demo.akeneo.com/api/rest/v1/product/top"
    --           }
    --         },
    --         "identifier": "top",
    --         "family": "tshirt",
    --         "groups": [],
    --         "parent": null,
    --         "categories": [
    --           "summer_collection"
    --         ],
    --         "enabled": true,
    --         "values": {
    --           "name": [
    --             {
    --               "data": "Top",
    --               "locale": "en_US",
    --               "scope": null
    --             },
    --             {
    --               "data": "D�bardeur",
    --               "locale": "fr_FR",
    --               "scope": null
    --             }
    --           ],
    --           "description": [
    --             {
    --               "data": "Summer top",
    --               "locale": "en_US",
    --               "scope": "ecommerce"
    --             },
    --             {
    --               "data": "Top",
    --               "locale": "en_US",
    --               "scope": "tablet"
    --             },
    --             {
    --               "data": "D�bardeur pour l'�t�",
    --               "locale": "fr_FR",
    --               "scope": "ecommerce"
    --             },
    --             {
    --               "data": "D�bardeur",
    --               "locale": "fr_FR",
    --               "scope": "tablet"
    --             }
    --           ],
    --           "price": [
    --             {
    --               "locale": null,
    --               "scope": null,
    --               "data": [
    --                 {
    --                   "amount": "15.5",
    --                   "currency": "EUR"
    --                 },
    --                 {
    --                   "amount": "15",
    --                   "currency": "USD"
    --                 }
    --               ]
    --             }
    --           ],
    --           "color": [
    --             {
    --               "locale": null,
    --               "scope": null,
    --               "data": "black"
    --             }
    --           ],
    --           "size": [
    --             {
    --               "locale": null,
    --               "scope": null,
    --               "data": "m"
    --             }
    --           ]
    --         },
    --         "created": "2016-06-23T18:24:44+02:00",
    --         "updated": "2016-06-25T17:56:12+02:00",
    --         "associations": {
    --           "PACK": {
    --             "products": [
    --               "sunglasses"
    --             ],
    --             "groups": []
    --           }
    --         }
    --       },
    --       {
    --         "_links": {
    --           "self": {
    --             "href": "https://demo.akeneo.com/api/rest/v1/product/cap"
    --           }
    --         },
    --         "identifier": "cap",
    --         "family": "caps",
    --         "groups": [],
    --         "parent": null,
    --         "categories": [
    --           "summer_collection"
    --         ],
    --         "enabled": true,
    --         "values": {
    --           "name": [
    --             {
    --               "data": "Cap",
    --               "locale": "en_US",
    --               "scope": null
    --             },
    --             {
    --               "data": "Casquette",
    --               "locale": "fr_FR",
    --               "scope": null
    --             }
    --           ],
    --           "description": [
    --             {
    --               "data": "Cap unisex",
    --               "locale": "en_US",
    --               "scope": "ecommerce"
    --             },
    --             {
    --               "data": "Cap unisex",
    --               "locale": "en_US",
    --               "scope": "tablet"
    --             },
    --             {
    --               "data": "Casquette unisexe",
    --               "locale": "fr_FR",
    --               "scope": "ecommerce"
    --             },
    --             {
    --               "data": "Casquette unisexe",
    --               "locale": "fr_FR",
    --               "scope": "tablet"
    --             }
    --           ],
    --           "price": [
    --             {
    --               "locale": null,
    --               "scope": null,
    --               "data": [
    --                 {
    --                   "amount": "20",
    --                   "currency": "EUR"
    --                 },
    --                 {
    --                   "amount": "20",
    --                   "currency": "USD"
    --                 }
    --               ]
    --             }
    --           ],
    --           "color": [
    --             {
    --               "locale": null,
    --               "scope": null,
    --               "data": "black"
    --             }
    --           ]
    --         },
    --         "created": "2016-06-23T18:24:44+02:00",
    --         "updated": "2016-06-25T17:56:12+02:00",
    --         "associations": {
    --           "PACK": {
    --             "products": [
    --               "sunglasses"
    --             ],
    --             "groups": []
    --           }
    --         }
    --       },
    --       {
    --         "_links": {
    --           "self": {
    --             "href": "https://demo.akeneo.com/api/rest/v1/product/sweat"
    --           }
    --         },
    --         "identifier": "sweat",
    --         "family": null,
    --         "groups": [],
    --         "parent": null,
    --         "categories": [
    --           "winter_collection"
    --         ],
    --         "enabled": true,
    --         "values": {},
    --         "created": "2016-06-23T11:24:44+02:00",
    --         "updated": "2016-06-23T11:24:44+02:00",
    --         "associations": {}
    --       }
    --     ]
    --   }
    -- }
    -- 

    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_OAMethod @json, 'LoadSb', @success OUT, @sbJson

    DECLARE @v_linksSelfHref nvarchar(4000)

    DECLARE @v_linksFirstHref nvarchar(4000)

    DECLARE @current_page int

    DECLARE @i int

    DECLARE @count_i int

    DECLARE @identifier nvarchar(4000)

    DECLARE @family nvarchar(4000)

    DECLARE @parent nvarchar(4000)

    DECLARE @enabled int

    DECLARE @created nvarchar(4000)

    DECLARE @updated nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @strVal nvarchar(4000)

    DECLARE @locale nvarchar(4000)

    DECLARE @scope nvarchar(4000)

    DECLARE @data int

    DECLARE @data_str nvarchar(4000)

    DECLARE @v_linksDownloadHref nvarchar(4000)

    DECLARE @data_int int

    DECLARE @dataAmount nvarchar(4000)

    DECLARE @dataUnit nvarchar(4000)

    EXEC sp_OAMethod @json, 'StringOf', @v_linksSelfHref OUT, '_links.self.href'
    EXEC sp_OAMethod @json, 'StringOf', @v_linksFirstHref OUT, '_links.first.href'
    EXEC sp_OAMethod @json, 'IntOf', @current_page OUT, 'current_page'
    SELECT @i = 0
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, '_embedded.items'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @v_linksSelfHref OUT, '_embedded.items[i]._links.self.href'
        EXEC sp_OAMethod @json, 'StringOf', @identifier OUT, '_embedded.items[i].identifier'
        EXEC sp_OAMethod @json, 'StringOf', @family OUT, '_embedded.items[i].family'
        EXEC sp_OAMethod @json, 'StringOf', @parent OUT, '_embedded.items[i].parent'
        EXEC sp_OAMethod @json, 'BoolOf', @enabled OUT, '_embedded.items[i].enabled'
        EXEC sp_OAMethod @json, 'StringOf', @created OUT, '_embedded.items[i].created'
        EXEC sp_OAMethod @json, 'StringOf', @updated OUT, '_embedded.items[i].updated'
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].groups'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @strVal OUT, '_embedded.items[i].groups[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].categories'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @strVal OUT, '_embedded.items[i].categories[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.blocked'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.blocked[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.blocked[j].scope'
            EXEC sp_OAMethod @json, 'BoolOf', @data OUT, '_embedded.items[i].values.blocked[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.can_cut'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.can_cut[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.can_cut[j].scope'
            EXEC sp_OAMethod @json, 'BoolOf', @data OUT, '_embedded.items[i].values.can_cut[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.not_used'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.not_used[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.not_used[j].scope'
            EXEC sp_OAMethod @json, 'BoolOf', @data OUT, '_embedded.items[i].values.not_used[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.deny_delivery'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.deny_delivery[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.deny_delivery[j].scope'
            EXEC sp_OAMethod @json, 'BoolOf', @data OUT, '_embedded.items[i].values.deny_delivery[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.can_split_package'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.can_split_package[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.can_split_package[j].scope'
            EXEC sp_OAMethod @json, 'BoolOf', @data OUT, '_embedded.items[i].values.can_split_package[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.transfer_to_warehouse'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.transfer_to_warehouse[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.transfer_to_warehouse[j].scope'
            EXEC sp_OAMethod @json, 'BoolOf', @data OUT, '_embedded.items[i].values.transfer_to_warehouse[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.product_code'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.product_code[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.product_code[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.product_code[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.nuotrauka'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.nuotrauka[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.nuotrauka[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.nuotrauka[j].data'
            EXEC sp_OAMethod @json, 'StringOf', @v_linksDownloadHref OUT, '_embedded.items[i].values.nuotrauka[j]._links.download.href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.minimum_quantity'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.minimum_quantity[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.minimum_quantity[j].scope'
            EXEC sp_OAMethod @json, 'IntOf', @data_int OUT, '_embedded.items[i].values.minimum_quantity[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.code'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.code[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.code[j].scope'
            EXEC sp_OAMethod @json, 'IntOf', @data_int OUT, '_embedded.items[i].values.code[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.name'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.name[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.name[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.name[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.fsc_code'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.fsc_code[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.fsc_code[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.fsc_code[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.warehouse'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.warehouse[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.warehouse[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.warehouse[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.item_volume'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.item_volume[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.item_volume[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @dataAmount OUT, '_embedded.items[i].values.item_volume[j].data.amount'
            EXEC sp_OAMethod @json, 'StringOf', @dataUnit OUT, '_embedded.items[i].values.item_volume[j].data.unit'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.customs_code'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.customs_code[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.customs_code[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.customs_code[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.product_type'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.product_type[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.product_type[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.product_type[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.weight_netto'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.weight_netto[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.weight_netto[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @dataAmount OUT, '_embedded.items[i].values.weight_netto[j].data.amount'
            EXEC sp_OAMethod @json, 'StringOf', @dataUnit OUT, '_embedded.items[i].values.weight_netto[j].data.unit'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.supplier_code'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.supplier_code[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.supplier_code[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.supplier_code[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.tariff_number'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.tariff_number[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.tariff_number[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.tariff_number[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.weight_brutto'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.weight_brutto[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.weight_brutto[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @dataAmount OUT, '_embedded.items[i].values.weight_brutto[j].data.amount'
            EXEC sp_OAMethod @json, 'StringOf', @dataUnit OUT, '_embedded.items[i].values.weight_brutto[j].data.unit'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.package_code_1'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.package_code_1[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.package_code_1[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.package_code_1[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.package_code_2'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.package_code_2[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.package_code_2[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.package_code_2[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.package_code_3'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.package_code_3[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.package_code_3[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.package_code_3[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.supplier_number'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.supplier_number[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.supplier_number[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.supplier_number[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.package_volume_1'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.package_volume_1[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.package_volume_1[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @dataAmount OUT, '_embedded.items[i].values.package_volume_1[j].data.amount'
            EXEC sp_OAMethod @json, 'StringOf', @dataUnit OUT, '_embedded.items[i].values.package_volume_1[j].data.unit'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.package_volume_2'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.package_volume_2[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.package_volume_2[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @dataAmount OUT, '_embedded.items[i].values.package_volume_2[j].data.amount'
            EXEC sp_OAMethod @json, 'StringOf', @dataUnit OUT, '_embedded.items[i].values.package_volume_2[j].data.unit'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.package_volume_3'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.package_volume_3[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.package_volume_3[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @dataAmount OUT, '_embedded.items[i].values.package_volume_3[j].data.amount'
            EXEC sp_OAMethod @json, 'StringOf', @dataUnit OUT, '_embedded.items[i].values.package_volume_3[j].data.unit'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.package_weight_1'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.package_weight_1[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.package_weight_1[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @dataAmount OUT, '_embedded.items[i].values.package_weight_1[j].data.amount'
            EXEC sp_OAMethod @json, 'StringOf', @dataUnit OUT, '_embedded.items[i].values.package_weight_1[j].data.unit'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.package_weight_2'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.package_weight_2[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.package_weight_2[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @dataAmount OUT, '_embedded.items[i].values.package_weight_2[j].data.amount'
            EXEC sp_OAMethod @json, 'StringOf', @dataUnit OUT, '_embedded.items[i].values.package_weight_2[j].data.unit'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.package_weight_3'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.package_weight_3[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.package_weight_3[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @dataAmount OUT, '_embedded.items[i].values.package_weight_3[j].data.amount'
            EXEC sp_OAMethod @json, 'StringOf', @dataUnit OUT, '_embedded.items[i].values.package_weight_3[j].data.unit'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.country_of_origin'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.country_of_origin[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.country_of_origin[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.country_of_origin[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.product_group_code'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.product_group_code[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.product_group_code[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.product_group_code[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.base_measurement_unit'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.base_measurement_unit[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.base_measurement_unit[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.base_measurement_unit[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.measurement_unit_code'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.measurement_unit_code[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.measurement_unit_code[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.measurement_unit_code[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.product_category_code'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.product_category_code[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.product_category_code[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.product_category_code[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.sale_measurement_unit'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.sale_measurement_unit[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.sale_measurement_unit[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.sale_measurement_unit[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.country_of_origin_code'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.country_of_origin_code[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.country_of_origin_code[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.country_of_origin_code[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.vat_registration_group'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.vat_registration_group[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.vat_registration_group[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.vat_registration_group[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.critical_amount_of_stock'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.critical_amount_of_stock[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.critical_amount_of_stock[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.critical_amount_of_stock[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.stock_registration_group'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.stock_registration_group[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.stock_registration_group[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.stock_registration_group[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.purchase_measurement_unit'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.purchase_measurement_unit[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.purchase_measurement_unit[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.purchase_measurement_unit[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.product_registration_group'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.product_registration_group[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.product_registration_group[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.product_registration_group[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.base_measurement_unit_quantity'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.base_measurement_unit_quantity[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.base_measurement_unit_quantity[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.base_measurement_unit_quantity[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.comment'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.comment[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.comment[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.comment[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.units_in_pallet'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.units_in_pallet[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.units_in_pallet[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.units_in_pallet[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.first_description'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.first_description[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.first_description[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.first_description[j].data'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_embedded.items[i].values.second_description'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @locale OUT, '_embedded.items[i].values.second_description[j].locale'
            EXEC sp_OAMethod @json, 'StringOf', @scope OUT, '_embedded.items[i].values.second_description[j].scope'
            EXEC sp_OAMethod @json, 'StringOf', @data_str OUT, '_embedded.items[i].values.second_description[j].data'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbJson
    EXEC @hr = sp_OADestroy @json


END
GO