SQL Server
SQL Server
BaFin MVP Portal SOAP Web Service MTOM
See more REST Misc Examples
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.pdfChilkat SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @rest int
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
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
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
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