(SQL Server) Get GMail Message format=full

Examines the JSON returned when getting a message using format=full.

For more information, see https://developers.google.com/gmail/api/v1/reference/users/messages/get

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.

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @http, 'AuthToken', 'ACCESS_TOKEN'

    EXEC sp_OASetProperty @http, 'Accept', 'application/json'

    -- This is the ID for a particular message in our mailbox...
    DECLARE @id nvarchar(4000)
    SELECT @id = '1712bc1dc22da2a2'

    -- Download the message using format=full
    DECLARE @success int
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'id', @id

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'QuickRequest', @resp OUT, 'GET', 'https://www.googleapis.com/gmail/v1/users/userId/messages/{$id}?format=full'
    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
        RETURN
      END


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

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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 @json
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    -- Here is a sample "format=full" response that contains 4 attachments:
    -- The JSON parsing source code is shown below...

    -- {
    --   "id": "1712bc1dc22da2a2",
    --   "threadId": "1712bc1dc22da2a2",
    --   "labelIds": [
    --     "UNREAD",
    --     "IMPORTANT",
    --     "CATEGORY_PERSONAL",
    --     "INBOX"
    --   ],
    --   "snippet": "Hello, this is a test email with attachments..",
    --   "historyId": "759379",
    --   "internalDate": "1585577056000",
    --   "payload": {
    --     "partId": "",
    --     "mimeType": "multipart/mixed",
    --     "filename": "",
    --     "headers": [
    --       {
    --         "name": "Delivered-To",
    --         "value": "chilkat.support@gmail.com"
    --       },
    --       {
    --         "name": "Received",
    --         "value": "..."
    --       },
    --       {
    --         "name": "X-Received",
    --         "value": "..."
    --       },
    --       {
    --         "name": "X-Google-Smtp-Source",
    --         "value": "..."
    --       },
    --       {
    --         "name": "X-Received",
    --         "value": "..."
    --       },
    --       {
    --         "name": "ARC-Seal",
    --         "value": "..."
    --       },
    --       {
    --         "name": "ARC-Message-Signature",
    --         "value": "..."
    --       },
    --       {
    --         "name": "ARC-Authentication-Results",
    --         "value": "..."
    --       },
    --       {
    --         "name": "Return-Path",
    --         "value": "..."
    --       },
    --       {
    --         "name": "Received",
    --         "value": "..."
    --       },
    --       {
    --         "name": "Received-SPF",
    --         "value": "..."
    --       },
    --       {
    --         "name": "Authentication-Results",
    --         "value": "..."
    --       },
    --       {
    --         "name": "DKIM-Signature",
    --         "value": "..."
    --       },
    --       {
    --         "name": "DKIM-Signature",
    --         "value": "..."
    --       },
    --       {
    --         "name": "Subject",
    --         "value": "Test email with attachments"
    --       },
    --       {
    --         "name": "From",
    --         "value": "..."
    --       },
    --       {
    --         "name": "To",
    --         "value": "..."
    --       },
    --       {
    --         "name": "Date",
    --         "value": "Mon, 30 Mar 2020 14:04:16 +0000"
    --       },
    --       {
    --         "name": "Mime-Version",
    --         "value": "1.0"
    --       },
    --       {
    --         "name": "Content-Type",
    --         "value": "multipart/mixed;  boundary=\"=_wNvwW3YTnQBZ24pmfFNfay3S6M-pHT7KdZIkXwYF-BFiPYNE\""
    --       },
    --       {
    --         "name": "References",
    --         "value": "..."
    --       },
    --       {
    --         "name": "X-Mailer",
    --         "value": "..."
    --       },
    --       {
    --         "name": "Thread-Index",
    --         "value": "AQHWBpwYB0UZvgGPT1KluJJUCF0BGg=="
    --       },
    --       {
    --         "name": "Thread-Topic",
    --         "value": "Test email with attachments"
    --       },
    --       {
    --         "name": "Message-ID",
    --         "value": "..."
    --       },
    --       {
    --         "name": "X-SES-Outgoing",
    --         "value": "2020.03.30-54.240.27.106"
    --       },
    --       {
    --         "name": "Feedback-ID",
    --         "value": "..."
    --       }
    --     ],
    --     "body": {
    --       "size": 0
    --     },
    --     "parts": [
    --       {
    --         "partId": "0",
    --         "mimeType": "multipart/alternative",
    --         "filename": "",
    --         "headers": [
    --           {
    --             "name": "Content-Type",
    --             "value": "multipart/alternative;  boundary=\"=_wNvwHW2hdOZkYPaJAhdOY4+XBEFNAcvIZ1sQcW4JkBcAdDqA\""
    --           }
    --         ],
    --         "body": {
    --           "size": 0
    --         },
    --         "parts": [
    --           {
    --             "partId": "0.0",
    --             "mimeType": "text/plain",
    --             "filename": "",
    --             "headers": [
    --               {
    --                 "name": "Content-Type",
    --                 "value": "text/plain; charset=UTF-8"
    --               },
    --               {
    --                 "name": "Content-Transfer-Encoding",
    --                 "value": "7bit"
    --               }
    --             ],
    --             "body": {
    --               "size": 129,
    --               "data": "SGVsbG8sIHRoaXMgaXMgYSB0ZXN0IGVtYWlsIHdpdGggYXR0YWNobWVudHMuLg0KIA0KIA0KIA0KLS0gDQogQmVzdCBSZWdhcmRzLA0KIE1hdHQgRmF1c2V5DQogQ2hpbGthdCBTb2Z0d2FyZSwgSW5jLiANCg0KIA0KDQogDQog"
    --             }
    --           },
    --           {
    --             "partId": "0.1",
    --             "mimeType": "multipart/related",
    --             "filename": "",
    --             "headers": [
    --               {
    --                 "name": "Content-Type",
    --                 "value": "multipart/related;  boundary=\"=_wNvw-LQZP2xD5vBm4q4esgoD5F4j3w+DRPUkkzOidpnpXAre\""
    --               }
    --             ],
    --             "body": {
    --               "size": 0
    --             },
    --             "parts": [
    --               {
    --                 "partId": "0.1.0",
    --                 "mimeType": "text/html",
    --                 "filename": "",
    --                 "headers": [
    --                   {
    --                     "name": "Content-Type",
    --                     "value": "text/html; charset=utf-8"
    --                   },
    --                   {
    --                     "name": "Content-Transfer-Encoding",
    --                     "value": "7bit"
    --                   }
    --                 ],
    --                 "body": {
    --                   "size": 530,
    --                   "data": "PGh0bWw-DQogIDxoZWFkPg0KDQogICAgPG1ldGEgaHR0cC1lcXVpdj0iY29udGVudC10eXBlIiBjb250ZW50PSJ0ZXh0L2h0bWw7IGNoYXJzZXQ9VVRGLTgiPg0KICA8L2hlYWQ-DQogIDxib2R5Pg0KICAgIEhlbGxvLCB0aGlzIGlzIGE8Zm9udCBjb2xvcj0iI2ZmMDAwMCI-IHRlc3Q8L2ZvbnQ-IGVtYWlsIHdpdGgNCiAgICBhdHRhY2htZW50cy4uPGJyPg0KICAgIDxicj4NCiAgICA8aW1nIG1vei1kby1ub3Qtc2VuZD0iZmFsc2UiDQogICAgICBzcmM9ImNpZDpwYXJ0MS5GMEI1MzVEQi5DQjZFN0ExMkBjaGlsa2F0c29mdC5jb20iIGFsdD0ic2VhaG9yc2UiDQogICAgICB3aWR0aD0iMTIwIiBoZWlnaHQ9IjEyMCI-PGJyPg0KICAgIDxkaXYgY2xhc3M9Im1vei1zaWduYXR1cmUiPi0tIDxicj4NCiAgICAgIEJlc3QgUmVnYXJkcyw8YnI-DQogICAgICBNYXR0IEZhdXNleTxicj4NCiAgICAgIENoaWxrYXQgU29mdHdhcmUsIEluYy4NCiAgICAgIDxwPg0KICAgICAgPC9wPg0KICAgIDwvZGl2Pg0KICA8L2JvZHk-DQo8L2h0bWw-DQo="
    --                 }
    --               },
    --               {
    --                 "partId": "0.1.1",
    --                 "mimeType": "image/jpeg",
    --                 "filename": "seahorse.jpg",
    --                 "headers": [
    --                   {
    --                     "name": "Content-Type",
    --                     "value": "image/jpeg"
    --                   },
    --                   {
    --                     "name": "Content-Id",
    --                     "value": "\u003cpart1.F0B535DB.CB6E7A12@chilkatsoft.com\u003e"
    --                   },
    --                   {
    --                     "name": "Content-Disposition",
    --                     "value": "inline; filename=seahorse.jpg"
    --                   },
    --                   {
    --                     "name": "Content-Transfer-Encoding",
    --                     "value": "base64"
    --                   }
    --                 ],
    --                 "body": {
    --                   "attachmentId": "ANGjdJ-Z1c9gUGsEzexYYEsF5nLHGUaNiZQs5HkuFQVPo5jq1eze8poigmarvERUIRj1coKUaftiQPWhiZWaFyLDcyjFbfLdjWyyO3AxRO8pY5liAhVp1bt_5f79Nk0vfnZXL0OnZmNAMdrcFe645DbsajwDZcoYnWVcsV9Fe6PCNq41e6XW8Ko4Ex7zMzkATiwlLyx0RLcR0hl-FuZ-UVpQI0JeMTkNi4eN5-9L4dRU7HTo9qEyFW_p3__vXrnEJvmEvWXNUqxKLktcZvkvql6mnAVMDiIapqsE6x0EAvFobre7sf1_T6ieVlA-m92vrtELC2HHdxzkU-qhC_LuoOSOz8nd8tfWsrB8zex0v6NFFP9mJmBUb1QGtCGTxLdI40z_LdMOSiaqmeCq4o_s",
    --                   "size": 24388
    --                 }
    --               }
    --             ]
    --           }
    --         ]
    --       },
    --       {
    --         "partId": "1",
    --         "mimeType": "application/pdf",
    --         "filename": "helloWorld.pdf",
    --         "headers": [
    --           {
    --             "name": "Content-Type",
    --             "value": "application/pdf; name=helloWorld.pdf"
    --           },
    --           {
    --             "name": "Content-Transfer-Encoding",
    --             "value": "base64"
    --           },
    --           {
    --             "name": "Content-Disposition",
    --             "value": "attachment; filename=helloWorld.pdf"
    --           }
    --         ],
    --         "body": {
    --           "attachmentId": "ANGjdJ-oy3aCuZISJKLAhUdaEksCEklbAPyMaWzFgqOMGbPCRkgwgeu_Kttd99C17OBTHROkDZGekibTKWXGfscB5ww7fw4E65_V1dQ-jHhb2TD1Cdm58-BbNw2iDxzptco8iILPiSnLLfFn5Ps7nsRcxHaGTt3r0yqFKCuIYNnPK1vM04BXI_cfzo-HnI4I3tD6oHNHOGVQrL01MdShFQjPELPUjXM8z1qs7Kom-QyvV1iOldUN-66UuhynsmDX-CMM5TIdB-8KD_lmdhf-0DqG8JnCA20XpXyfqwS8XFkPA-t-QSjb7SdkHQFtQ4lz2PcBREFzZ2eI5j0l0Y_dQHRPYTeMwkVl1yl4MfFT4C4iso3VSF-eqaIjiFCbXKCFNyeEIW5WFsv189dhlSqU",
    --           "size": 934
    --         }
    --       },
    --       {
    --         "partId": "2",
    --         "mimeType": "application/x-gzip",
    --         "filename": "hamlet.xml.gz",
    --         "headers": [
    --           {
    --             "name": "Content-Type",
    --             "value": "application/x-gzip; name=hamlet.xml.gz"
    --           },
    --           {
    --             "name": "Content-Transfer-Encoding",
    --             "value": "base64"
    --           },
    --           {
    --             "name": "Content-Disposition",
    --             "value": "attachment; filename=hamlet.xml.gz"
    --           }
    --         ],
    --         "body": {
    --           "attachmentId": "ANGjdJ9rvj1ebIc-f0cXG0kwsb6pDBkRCNk3IQXbJ5z0-s2mEcVat3Pp6pVm3rTkRLOyeXHzRcTuel7nxRFJX6WVAKtG-5B-GL_b6DlsOWOzH4A_IKlyJ9U4aeWT02CgQL8oRwlCu1h0BtnY6lT8zureboGOqR7qoa-i5jH8xr6ds0nUDZp71lAwfOjji3eG6Wg6HfzWzWUThYPH3ba0_q35Jii8JIMhyJwwZnGjN3lvW_VhN6UIL1DIpop95b8rdwJdpyUavm7VhGtdNrI-mpgWQtT7WIAdIe6a-O74Zf4AB7uG7bp9R-IMBqQT-1jQqkN2_xlSpe3meWbuNu-4zsYfXN-IqM-Iy4dudAXjt0aY3Y8zxuHfW5qT7o4u8wSszhTIjM1j8lt-N7GZqnwJ",
    --           "size": 77409
    --         }
    --       },
    --       {
    --         "partId": "3",
    --         "mimeType": "text/plain",
    --         "filename": "brasil_cert.pem",
    --         "headers": [
    --           {
    --             "name": "Content-Type",
    --             "value": "text/plain; name=brasil_cert.pem"
    --           },
    --           {
    --             "name": "Content-Transfer-Encoding",
    --             "value": "base64"
    --           },
    --           {
    --             "name": "Content-Disposition",
    --             "value": "attachment; filename=brasil_cert.pem"
    --           }
    --         ],
    --         "body": {
    --           "attachmentId": "ANGjdJ8nGD_xRHvjDwNIaSUleWCWk15ksUleks0fJ29gfGVOebzjBOUnAHYgKPefUQ6d6ExtqYubpvGCka1kJAB4gFwsnhQeflBhFNkORBqtb6FAGq0FcNgrbuxyKywmoHOUV4GDsNs4OGroEcFhBeZNQ8XKEdEoaO8ht8DARn2LncjxioQ4TmZ6jawmMF-mLbW1mz4QjwNir5jLOsE11tYZV2tLDoqjsubKeJJA8xojWo9r50zWfJFgWJKXZJwyNvKyZTwLmmBL_ATFxLFp4Nvo4K37uuVdUCG8WXiWwNr0oIV1RZOd_ZOA7M6Jif0wUcppJdxXRqlsFJqGlcVsLowkVGrJNAMe-fLY1x4w5od3axZIdZQgBPlVRKYYu5xopPTrLJsn4ld7pNyAhpZW",
    --           "size": 2952
    --         }
    --       },
    --       {
    --         "partId": "4",
    --         "mimeType": "application/x-zip-compressed",
    --         "filename": "helloWorld.zip",
    --         "headers": [
    --           {
    --             "name": "Content-Type",
    --             "value": "application/x-zip-compressed; name=helloWorld.zip"
    --           },
    --           {
    --             "name": "Content-Transfer-Encoding",
    --             "value": "base64"
    --           },
    --           {
    --             "name": "Content-Disposition",
    --             "value": "attachment; filename=helloWorld.zip"
    --           }
    --         ],
    --         "body": {
    --           "attachmentId": "ANGjdJ9nWglkRNneBVGFk5GwSg83COa0zBeiTAvoMYniPklY03-w93dmEVHJTB9CAu5WMFW_yhV4ou2oX2w9SlpZjjyTgSc7sfYWeuX3pt29z67agy9WUVYeNGpo_30LSPgeyNk_Nm0DTAaektJTEMSy65rjRyK03VkHahNpMLY6Kd_pdfrk50oceuXzwNJZ-V-bftmIluueoQMqLcWQSGBRvEdIcl6EcPFFw8KuMLrjZZJwq8_bb8uM6zUvV23Q9LsLTgWg8FaBWejBf0wuIFXl3ZhtSLa-Y8OXCApHPdUnn45hccAzaQEmy4ZG7cgvCdN_jA9eFX46HYHty7ewleKPFGLTVof2JWsMiIi9BPqfohqQ5FPm1WXlGG_bOHvaMdoWcoqbKMMvF8lWZsfz",
    --           "size": 174
    --         }
    --       }
    --     ]
    --   },
    --   "sizeEstimate": 152847
    -- }

    DECLARE @strVal nvarchar(4000)

    DECLARE @name nvarchar(4000)

    DECLARE @value nvarchar(4000)

    DECLARE @partId nvarchar(4000)

    DECLARE @mimeType nvarchar(4000)

    DECLARE @filename nvarchar(4000)

    DECLARE @bodySize int

    DECLARE @bodyAttachmentId nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @bodyData nvarchar(4000)

    DECLARE @k int

    DECLARE @count_k int

    DECLARE @json1 int

    DECLARE @i1 int

    DECLARE @count_i1 int

    DECLARE @id nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'id'
    DECLARE @threadId nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @threadId OUT, 'threadId'
    DECLARE @snippet nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @snippet OUT, 'snippet'
    DECLARE @historyId nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @historyId OUT, 'historyId'
    DECLARE @internalDate nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @internalDate OUT, 'internalDate'
    DECLARE @payloadPartId nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @payloadPartId OUT, 'payload.partId'
    DECLARE @payloadMimeType nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @payloadMimeType OUT, 'payload.mimeType'
    DECLARE @payloadFilename nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @payloadFilename OUT, 'payload.filename'
    DECLARE @payloadBodySize int
    EXEC sp_OAMethod @json, 'IntOf', @payloadBodySize OUT, 'payload.body.size'
    DECLARE @sizeEstimate int
    EXEC sp_OAMethod @json, 'IntOf', @sizeEstimate OUT, 'sizeEstimate'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'labelIds'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @strVal OUT, 'labelIds[i]'
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'payload.headers'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'payload.headers[i].name'
        EXEC sp_OAMethod @json, 'StringOf', @value OUT, 'payload.headers[i].value'
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'payload.parts'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @partId OUT, 'payload.parts[i].partId'
        EXEC sp_OAMethod @json, 'StringOf', @mimeType OUT, 'payload.parts[i].mimeType'
        EXEC sp_OAMethod @json, 'StringOf', @filename OUT, 'payload.parts[i].filename'
        EXEC sp_OAMethod @json, 'IntOf', @bodySize OUT, 'payload.parts[i].body.size'
        EXEC sp_OAMethod @json, 'StringOf', @bodyAttachmentId OUT, 'payload.parts[i].body.attachmentId'
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'payload.parts[i].headers'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'payload.parts[i].headers[j].name'
            EXEC sp_OAMethod @json, 'StringOf', @value OUT, 'payload.parts[i].headers[j].value'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'payload.parts[i].parts'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @partId OUT, 'payload.parts[i].parts[j].partId'
            EXEC sp_OAMethod @json, 'StringOf', @mimeType OUT, 'payload.parts[i].parts[j].mimeType'
            EXEC sp_OAMethod @json, 'StringOf', @filename OUT, 'payload.parts[i].parts[j].filename'
            EXEC sp_OAMethod @json, 'IntOf', @bodySize OUT, 'payload.parts[i].parts[j].body.size'
            EXEC sp_OAMethod @json, 'StringOf', @bodyData OUT, 'payload.parts[i].parts[j].body.data'
            SELECT @k = 0
            EXEC sp_OAMethod @json, 'SizeOfArray', @count_k OUT, 'payload.parts[i].parts[j].headers'
            WHILE @k < @count_k
              BEGIN
                EXEC sp_OASetProperty @json, 'K', @k
                EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'payload.parts[i].parts[j].headers[k].name'
                EXEC sp_OAMethod @json, 'StringOf', @value OUT, 'payload.parts[i].parts[j].headers[k].value'
                SELECT @k = @k + 1
              END
            SELECT @k = 0
            EXEC sp_OAMethod @json, 'SizeOfArray', @count_k OUT, 'payload.parts[i].parts[j].parts'
            WHILE @k < @count_k
              BEGIN
                EXEC sp_OASetProperty @json, 'K', @k
                EXEC sp_OAMethod @json, 'StringOf', @partId OUT, 'payload.parts[i].parts[j].parts[k].partId'
                EXEC sp_OAMethod @json, 'StringOf', @mimeType OUT, 'payload.parts[i].parts[j].parts[k].mimeType'
                EXEC sp_OAMethod @json, 'StringOf', @filename OUT, 'payload.parts[i].parts[j].parts[k].filename'
                EXEC sp_OAMethod @json, 'IntOf', @bodySize OUT, 'payload.parts[i].parts[j].parts[k].body.size'
                EXEC sp_OAMethod @json, 'StringOf', @bodyData OUT, 'payload.parts[i].parts[j].parts[k].body.data'
                EXEC sp_OAMethod @json, 'StringOf', @bodyAttachmentId OUT, 'payload.parts[i].parts[j].parts[k].body.attachmentId'

                EXEC sp_OAMethod @json, 'ObjectOf', @json1 OUT, 'payload.parts[i].parts[j].parts[k]'
                SELECT @i1 = 0
                EXEC sp_OAMethod @json1, 'SizeOfArray', @count_i1 OUT, 'headers'
                WHILE @i1 < @count_i1
                  BEGIN
                    EXEC sp_OASetProperty @json1, 'I', @i1
                    EXEC sp_OAMethod @json1, 'StringOf', @name OUT, 'headers[i].name'
                    EXEC sp_OAMethod @json1, 'StringOf', @value OUT, 'headers[i].value'
                    SELECT @i1 = @i1 + 1
                  END
                EXEC @hr = sp_OADestroy @json1

                SELECT @k = @k + 1
              END
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

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


END
GO