(SQL Server) BaFin MVP Portal SOAP Web Service MTOM

Demonstrate how to send a SOAP request with MTOM attachment to the BaFin MVP Portal as indicated at https://www.bafin.de/SharedDocs/Downloads/EN/dl_mvp-portal_soap_webservice_a26mifir_en.pdf

For more information, see https://www.bafin.de/SharedDocs/Downloads/EN/dl_mvp-portal_soap_webservice_a26mifir_en.pdf

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 @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Connect to portal.mvp.bafin.de:444
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 444
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'portal.mvp.bafin.de', @port, @bTls, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- The SOAP request we'll be sending is multipart/related and it will have two parts
    -- 1) the SOAP Envelope
    -- 2) the MTOM attachment (in this case an XML document)

    -- Let's build the SOAP envelope:
    -- Note: The data posted here, including the username, are not actual accounts.  It is the sample data provided
    -- at https://www.bafin.de/SharedDocs/Downloads/EN/dl_mvp-portal_soap_webservice_a26mifir_en.pdf

    -- Use this online tool to generate code from sample XML: 
    -- Generate Code to Create XML

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

    EXEC sp_OASetProperty @soapXml, 'Tag', 'soapenv:Envelope'
    EXEC sp_OAMethod @soapXml, 'AddAttribute', @success OUT, 'xmlns:a26mifir', 'http://www.bafin.de/mvp/a26mifir/'
    EXEC sp_OAMethod @soapXml, 'AddAttribute', @success OUT, 'xmlns:soapenv', 'http://schemas.xmlsoap.org/soap/envelope/'
    EXEC sp_OAMethod @soapXml, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'soapenv:mustUnderstand', '1'
    EXEC sp_OAMethod @soapXml, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'xmlns:wsse', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'
    EXEC sp_OAMethod @soapXml, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'xmlns:wsu', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurityutility-1.0.xsd'
    EXEC sp_OAMethod @soapXml, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:UsernameToken', 1, 'wsu:Id', 'UsernameToken44C3662D584567FE6A14672949268541'
    EXEC sp_OAMethod @soapXml, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsse:UsernameToken|wsse:Username', 'karl.meier1234#hg_05_1234567890'
    EXEC sp_OAMethod @soapXml, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:UsernameToken|wsse:Password', 1, 'Type', 'http://docs.oasis-open.org/wss/2004/01/oasis200401-wss-username-token-profile-1.0#PasswordText'
    EXEC sp_OAMethod @soapXml, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsse:UsernameToken|wsse:Password', 'XXXXXXXXXX'
    EXEC sp_OAMethod @soapXml, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:UsernameToken|wsse:Nonce', 1, 'EncodingType', 'http://docs.oasis-open.org/wss/2004/01/oasis200401-wss-soap-message-security1.0#Base64Binary'
    EXEC sp_OAMethod @soapXml, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsse:UsernameToken|wsse:Nonce', '+WifMh5kFw/1fHgMahFu7A=='
    EXEC sp_OAMethod @soapXml, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsse:UsernameToken|wsu:Created', '2016-06-30T13:55:26.852Z'
    EXEC sp_OAMethod @soapXml, 'UpdateChildContent', NULL, 'soapenv:Body|a26mifir:submitDATTRA|inDATTRA|a26mifir:dateiname', 'A26MiFIR_Testfile_001.xml'
    EXEC sp_OAMethod @soapXml, 'UpdateChildContent', NULL, 'soapenv:Body|a26mifir:submitDATTRA|kundenreferenz', 'ClientRef_001'
    EXEC sp_OAMethod @soapXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|a26mifir:submitDATTRA|datei|inc:Include', 1, 'href', 'cid:139580394822'
    EXEC sp_OAMethod @soapXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|a26mifir:submitDATTRA|datei|inc:Include', 1, 'xmlns:inc', 'http://www.w3.org/2004/08/xop/include'

    -- We built the following XML:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <soapenv:Envelope xmlns:a26mifir="http://www.bafin.de/mvp/a26mifir/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    --     <soapenv:Header>
    --         <wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurityutility-1.0.xsd">
    --             <wsse:UsernameToken wsu:Id="UsernameToken44C3662D584567FE6A14672949268541">
    --                 <wsse:Username>karl.meier1234#hg_05_1234567890</wsse:Username>
    --                 <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis200401-wss-username-token-profile-1.0#PasswordText">XXXXXXXXXX</wsse:Password>
    --                 <wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis200401-wss-soap-message-security1.0#Base64Binary">+WifMh5kFw/1fHgMahFu7A==</wsse:Nonce>
    --                 <wsu:Created>2016-06-30T13:55:26.852Z</wsu:Created>
    --             </wsse:UsernameToken>
    --         </wsse:Security>
    --     </soapenv:Header>
    --     <soapenv:Body>
    --         <a26mifir:submitDATTRA>
    --             <inDATTRA>
    --                 <a26mifir:dateiname>A26MiFIR_Testfile_001.xml</a26mifir:dateiname>
    --             </inDATTRA>
    --             <kundenreferenz>ClientRef_001</kundenreferenz>
    --             <datei>
    --                 <inc:Include href="cid:139580394822" xmlns:inc="http://www.w3.org/2004/08/xop/include"/>
    --             </datei>
    --         </a26mifir:submitDATTRA>
    --     </soapenv:Body>
    -- </soapenv:Envelope>

    -- ------------------------------------------------------------
    -- We want the top-most header of our request to look like this:

    -- POST https://portal.mvp.bafin.de:444/services/ws/t_a26mifir HTTP/1.1
    -- Content-Encoding: gzip
    -- Accept-Encoding: gzip,deflate
    -- Content-Type: multipart/related; type="application/xop+xml";
    --    start="<rootpart@soapui.org>"; start-info="text/xml"; boundary="----=_Part_3_19706996.1467294979823"
    -- SOAPAction: "http://www.bafin.de/mvp/a26mifir/submitDATTRA"
    -- MIME-Version: 1.0
    -- Transfer-Encoding: chunked
    -- Host: portal.mvp.bafin.de:444
    -- Connection: Keep-Alive
    -- User-Agent: Apache-HttpClient/4.1.1 (java 1.5)

    -- Some header fields are unimportant.  
    -- 1) We can omit the User-Agent.
    -- 2) Also, the Transfer-Encoding doesn't need to be "chunked".  It shouldn't matter whether it's chunked or non-chunked.
    -- 3) We do need the Content-Encoding to be "gzip", because the documentation at https://www.bafin.de/SharedDocs/Downloads/EN/dl_mvp-portal_soap_webservice_a26mifir_en.pdf
    -- explicitly states that it wants a gzip compressed request body.
    -- 4) Don't worry about specifying a boundary.  Chilkat will auto-generate a boundary and add it to the Content-Type.
    -- 5) If we're sending many requests on the same connection, then set the Connection: Keep-Alive header.  Otherwise, there is no need for it.

    -- Begin specifying header fields for the topmost header:
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'multipart/related; type="application/xop+xml"; start="<rootpart@soapui.org>"; start-info="text/xml"'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Encoding', 'gzip'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Accept-Encoding', 'gzip,deflate'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'SOAPAction', 'http://www.bafin.de/mvp/a26mifir/submitDATTRA'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'MIME-Version', '1.0'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Connection', 'Keep-Alive'

    -- ------------------------------------------------------------
    -- Build the header for the 1st sub-part, which will contain the SOAP envelope.
    -- It will look like this:

    -- Content-Type: application/xop+xml; charset=UTF-8; type="text/xml"
    -- Content-Transfer-Encoding: 8bit
    -- Content-ID: <rootpart@soapui.org>

    EXEC sp_OASetProperty @rest, 'PartSelector', '1'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/xop+xml; charset=UTF-8; type="text/xml"'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Transfer-Encoding', '8bit'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-ID', '<rootpart@soapui.org>'
    EXEC sp_OASetProperty @soapXml, 'EmitCompact', 1
    EXEC sp_OASetProperty @soapXml, 'EmitXmlDecl', 0
    EXEC sp_OAMethod @soapXml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'SetMultipartBodyString', @success OUT, @sTmp0

    -- ------------------------------------------------------------
    -- Build the header for the 1st sub-part, which will contain the SOAP envelope.
    -- It will look like this:

    -- Content-Type: application/octet-binary; name=A26MiFIR_Testfile_001.xml
    -- Content-Transfer-Encoding: binary
    -- Content-ID: <139580394822>
    -- Content-Disposition: attachment; name=" A26MiFIR_Testfile_001.xml"; filename="A26MiFIR_Testfile_001.xml"

    EXEC sp_OASetProperty @rest, 'PartSelector', '2'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/octet-binary; name=A26MiFIR_Testfile_001.xml'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Transfer-Encoding', 'binary'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-ID', '<139580394822>'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Disposition', 'attachment; name="A26MiFIR_Testfile_001.xml"; filename="A26MiFIR_Testfile_001.xml"'

    -- We'll just use a fake attachment XML body for this example
    EXEC sp_OAMethod @rest, 'SetMultipartBodyString', @success OUT, '<test>blah blah blah</test>'

    -- ------------------------------------------------------------
    -- The request is fully specified.  Now send the POST...

    -- Turn on DebugMode.  This is a mode where no request is sent.  Instead, the request that would've been
    -- sent is recorded and can be retrieved via the GetLastDebugRequest method.
    EXEC sp_OASetProperty @rest, 'DebugMode', 1

    DECLARE @responseBody nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestMultipart', @responseBody OUT, 'POST', '/services/ws/t_a26mifir'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @soapXml
        RETURN
      END

    -- Because we set DebugMode, no request was actually sent.  Instead, we can examine the exact
    -- request that would've been sent:
    EXEC sp_OAGetProperty @rest, 'DebugMode', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN
        DECLARE @bdRequest int
        -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
        EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdRequest OUT

        EXEC sp_OAMethod @rest, 'GetLastDebugRequest', @success OUT, @bdRequest
        EXEC sp_OAMethod @bdRequest, 'WriteFile', @success OUT, 'qa_output/bafin_request.txt'
      END


    PRINT 'Success.'

    -- Clear the REST object for any subsequent requests..
    EXEC sp_OAMethod @rest, 'ClearAllHeaders', @success OUT
    EXEC sp_OAMethod @rest, 'ClearAllParts', @success OUT
    EXEC sp_OASetProperty @rest, 'PartSelector', ''

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @soapXml
    EXEC @hr = sp_OADestroy @bdRequest


END
GO