Use Msxml2.serverxmlhttp to assemble soap calls in sqlserver2008 WebService

interface method to invoke: Up_acc_inst_info (string xml)

Interface parameters: string in XML format

Interface function: Pass the personnel number, the note to the interface to update, the interface returns the update result.

Instance:

DECLARE @strXML varchar (5000)
DECLARE @obj int
DECLARE @sUrl varchar (5000)
DECLARE @response varchar (5000)
DECLARE @hr int
--Parameters
SET @strXML = ' <root><accountid>654</accountid> <innotes>654 's remarks </innotes></root > '

--Convert <,> in argument to escape character, otherwise the interface cannot recognize @strxml as a string type and will report 400 error message
Set @strxml =replace (@strXML, ' < ', ' &lt; ')
Set @strxml =replace (@strXML, ' > ', ' &gt; ')

--Interface Address
Set @sUrl = ' Http://localhost/WebService.asmx '
--Assemble SOAP 1.2 format information
Set @strXML = ' <?xml version= ' 1.0 ' encoding= ' utf-8 '? ><soap12:envelope xmlns:xsi= ' http://www.w3.org/2001/ Xmlschema-instance "xmlns:xsd=" Http://www.w3.org/2001/XMLSchema "xmlns:soap12=" http://www.w3.org/2003/05/ Soap-envelope "><soap12:body><up_acc_inst_info xmlns=" http://tempuri.org/"><strXML>" [email protected]+ ' </strXML></UP_ACC_inst_Info></soap12:Body></soap12:Envelope> '

exec sp_oacreate ' MSXML2. ServerXMLHTTP ', @obj out

exec sp_OAMethod @obj, ' Open ', null, ' POST ', @sUrl, FALSE

exec sp_OAMethod @obj, ' setRequestHeader ', null, ' Content-type ', ' application/soap+xml; Charset=utf-8 '
exec sp_OAMethod @obj, ' Send ', NULL, @strXML
exec sp_oagetproperty @obj, ' status ', @response out
IF @hr <> 200
BEGIN
EXEC sp_OAGetErrorInfo @obj
Print @obj
Return
END
exec sp_oagetproperty @obj, ' responsetext ', @response out

--because the result of the interface return is also a string in XML format, convert the inside <,> back
Set @response =replace (@response, ' &lt; ', ' < ')
Set @response =replace (@response, ' &gt; ', ' > ')
Print @response
exec sp_OADestroy @obj

Use Msxml2.serverxmlhttp to assemble soap calls in sqlserver2008 WebService