SqlServer调用webapi和webService接口

1.通过http协议post调用webapi接口（json数据格式）

declare @ServiceUrl as varchar(1000) 
set @ServiceUrl = 'http://127.0.01/sh-oms/openApi/order/create' --restful api 接口
DECLARE @data varchar(max);
set @data='{"orderNo":"GJF0220402286","businessMan":"11096周涛"}'   --入参


DECLARE @Object as Int
Declare @ResponseText AS  varchar(8000);      
Exec sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ServiceUrl,'false'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/json;charset=UTF-8'
Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
--EXEC sp_OAGetErrorInfo @Object --异常输出

Select  @ResponseText as  Response
Exec sp_OADestroy @Object
GO

2.调用WebService

DECLARE @sUrl NVARCHAR(100)=N'http://127.0.0.1/WebService/Dye.asmx?op=ColorSystem'

DECLARE @sSoapContent NVARCHAR(2000)=N''

SELECT @sSoapContent=N'<?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>
    <ColorSystem xmlns="http://tempuri.org/">
      <iCompanyId>1</iCompanyId>
      <sColorNo>2</sColorNo>
    </ColorSystem>
  </soap12:Body>
</soap12:Envelope>'

DECLARE @sResult NVARCHAR(MAX)
SELECT @sResult=dbo.fnpbWebServiceInvoke(@sUrl,N'',N'',@sSoapContent).value(N'(/Result[1])','NVARCHAR(MAX)')
SELECT @sResult=REPLACE(@sResult,N'"',N'')
SELECT @sResult=REPLACE(@sResult,N'{',N'')
SELECT @sResult=REPLACE(@sResult,N'}',N'')
SELECT @sResult=REPLACE(@sResult,N'[',N'')
SELECT @sResult=REPLACE(@sResult,N']',N'')