(SQL Server) MWS SubmitFeed (Amazon Marketplace Web Service)

Uploads a feed for processing by Amazon MWS.

See Amazon MWS SubmitFeed for the Amazon MWS SubmitFeed reference documentation.

Important: The Chilkat v9.5.0.75 release accidentally breaks Amazon MWS (not AWS) authentication. If you need MWS with 9.5.0.75, send email to support@chilkatsoft.com for a hotfix, or revert back to v9.5.0.73, or update to a version after 9.5.0.75.

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 @success int

    -- This example will send an XML file in the HTTP request body.  
    -- First we'll construct the XML, then we'll compute the MD5 digest which needs to be added as a query param..

    -- Construct the following XML.   (This is just a sample XML body..)

    -- <AmazonEnvelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    --     xsi:noNamespaceSchemaLocation="amzn-envelope.xsd">
    --   <Header>
    --     <DocumentVersion>1.01</DocumentVersion>
    --     <MerchantIdentifier>M_EXAMPLE_123456</MerchantIdentifier>
    --   </Header>
    --   <MessageType>Product</MessageType>
    --   <PurgeAndReplace>false</PurgeAndReplace>
    --   <Message>
    --     <MessageID>1</MessageID>
    --     <OperationType>Update</OperationType>
    --     <Product>
    --       <SKU>56789</SKU>
    --       <StandardProductID>
    --         <Type>ASIN</Type>
    --         <Value>B0EXAMPLEG</Value>
    --       </StandardProductID>
    --       <ProductTaxCode>A_GEN_NOTAX</ProductTaxCode>
    --       <DescriptionData>
    --         <Title>Example Product Title</Title>
    --         <Brand>Example Product Brand</Brand>
    --         <Description>This is an example product description.</Description>
    --         <BulletPoint>Example Bullet Point 1</BulletPoint>
    --         <BulletPoint>Example Bullet Point 2</BulletPoint>
    --         <MSRP currency="USD">25.19</MSRP>
    --         <Manufacturer>Example Product Manufacturer</Manufacturer>
    --         <ItemType>example-item-type</ItemType>
    --       </DescriptionData>
    --       <ProductData>
    --         <Health>
    --           <ProductType>
    --             <HealthMisc>
    --               <Ingredients>Example Ingredients</Ingredients>
    --               <Directions>Example Directions</Directions>
    --             </HealthMisc>
    --           </ProductType>
    --         </Health>
    --       </ProductData>
    --     </Product>
    --   </Message>
    -- </AmazonEnvelope>

    -- This code was generated by pasting the above XML into the online tool at http://tools.chilkat.io/xmlCreate.cshtml
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xml, 'Tag', 'AmazonEnvelope'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xsi:noNamespaceSchemaLocation', 'amzn-envelope.xsd'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|DocumentVersion', '1.01'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MerchantIdentifier', 'M_EXAMPLE_123456'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'MessageType', 'Product'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'PurgeAndReplace', 'false'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|MessageID', '1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|OperationType', 'Update'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|SKU', '56789'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|StandardProductID|Type', 'ASIN'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|StandardProductID|Value', 'B0EXAMPLEG'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|ProductTaxCode', 'A_GEN_NOTAX'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|DescriptionData|Title', 'Example Product Title'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|DescriptionData|Brand', 'Example Product Brand'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|DescriptionData|Description', 'This is an example product description.'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|DescriptionData|BulletPoint', 'Example Bullet Point 1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|DescriptionData|BulletPoint[1]', 'Example Bullet Point 2'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Message|Product|DescriptionData|MSRP', 1, 'currency', 'USD'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|DescriptionData|MSRP', '25.19'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|DescriptionData|Manufacturer', 'Example Product Manufacturer'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|DescriptionData|ItemType', 'example-item-type'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|ProductData|Health|ProductType|HealthMisc|Ingredients', 'Example Ingredients'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Message|Product|ProductData|Health|ProductType|HealthMisc|Directions', 'Example Directions'

    -- Get the XML in the most compact form for the feed submission.
    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT

    EXEC sp_OASetProperty @xml, 'EmitCompact', 1
    EXEC sp_OAMethod @xml, 'GetXmlSb', @success OUT, @sbXml

    -- Get the MD5 hash..
    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'md5'
    DECLARE @contentMd5Value nvarchar(4000)
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @contentMd5Value OUT, @sTmp0

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

    -- Connect to the Amazon MWS REST server.
    -- 
    -- Make sure to connect to the correct Amazon MWS Endpoing, otherwise
    -- you'll get an HTTP 401 response code.
    -- 
    -- The possible servers are:
    -- 
    -- North America (NA) 	https://mws.amazonservices.com
    -- Europe (EU) 	https://mws-eu.amazonservices.com
    -- India (IN) 	https://mws.amazonservices.in
    -- China (CN) 	https://mws.amazonservices.com.cn
    -- Japan (JP) 	https://mws.amazonservices.jp 
    -- 
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'mws.amazonservices.com', @port, @bTls, @bAutoReconnect

    EXEC sp_OASetProperty @rest, 'Host', 'mws.amazonservices.com'

    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'AWSAccessKeyId', '0PB842ExampleN4ZTR2'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Action', 'SubmitFeed'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'FeedType', '_POST_PRODUCT_DATA_'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'MWSAuthToken', 'amzn.mws.4ea38b7b-f563-7709-4bae-87aeaEXAMPLE'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'MarketplaceId.Id.1', 'ATVExampleDER'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'SellerId', 'A1XExample5E6'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'ContentMD5Value', @contentMd5Value
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'SignatureMethod', 'HmacSHA256'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'SignatureVersion', '2'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Version', '2009-01-01'

    -- Add the MWS Signature param.  (Also adds the Timestamp parameter using the curent system date/time.)
    EXEC sp_OAMethod @rest, 'AddMwsSignature', @success OUT, 'POST', '/Feeds/2009-01-01', 'mws.amazonservices.com', 'MWS_SECRET_ACCESS_KEY_ID'

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'text/xml'
    DECLARE @responseXml nvarchar(4000)
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'FullRequestString', @responseXml OUT, 'POST', '/Feeds/2009-01-01', @sTmp0
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        -- Examine the request/response to see what happened.

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'response status code = ' + @iTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'response status text = ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT 'response header: ' + @sTmp0

        PRINT 'response body: ' + @responseXml

        PRINT '---'

        EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
        PRINT 'LastRequestStartLine: ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
        PRINT 'LastRequestHeader: ' + @sTmp0
      END

    -- Examine the XML returned in the response body.

    PRINT @responseXml

    PRINT '----'

    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @rest


END
GO