SQL code check to work with SOAP, problem with getting data

Question

I'm a complete newbie and trying to get data from a SOAP service via SQL query. I tried it first on reqbin.com, there I get the response, it works ok (see pic). With SQL I don't get any data. Does anyone has any ideas how to improve the SQL?

    DECLARE @xmlRequest NVARCHAR(MAX) = 
    '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
        <soapenv:Body>
            <StatusNespolehlivyPlatceRequest xmlns="http://adis.mfcr.cz/rozhraniCRPDPH/">
                <dic>27396819</dic>
            </StatusNespolehlivyPlatceRequest>
        </soapenv:Body>
    </soapenv:Envelope>';

DECLARE @response INT;
    DECLARE @responseText NVARCHAR(MAX);

    EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @response OUT;
    EXEC sp_OAMethod @response, 'open', NULL, 'POST', 'https://adisrws.mfcr.cz/dpr/axis2/services/rozhraniCRPDPH.rozhraniCRPDPHSOAP', 'false';
    EXEC sp_OAMethod @response, 'setRequestHeader', NULL, 'Content-Type', 'text/xml; charset=utf-8';
    EXEC sp_OAMethod @response, 'send', NULL, @xmlRequest;
    EXEC sp_OAMethod @response, 'responseText', @responseText OUT;

    -- Debugging: Print the raw response
    PRINT @responseText;

reqbin

Many thanks on any advice!!

---

Answer 1

sp_oa-procedures don't support nvarchar(max) as parameters. Try NVARCHAR(4000) for @xmlRequest / @responseText.

Also, feel free to not use sp_oa-procedures altogether, they are discouraged, you should either call web services from outside of SQL Server or use CLR or better suited code to do it.