SQLSERVER调用web接口教程

一、打开Ole Automation Procedures支持

二、创建解析web接口的存储过程

CREATE PROC [dbo].[spHTTPRequest] 
      @URI VARCHAR(2000) = '',      
      @methodName VARCHAR(50) = '', 
      @requestBody VARCHAR(8000) = '', 
      @SoapAction VARCHAR(255), 
      @UserName NVARCHAR(100), -- Domain\UserName or UserName 
      @Password NVARCHAR(100), 
      @responseText VARCHAR(8000) OUTPUT
AS
SET NOCOUNT ON
IF    @methodName = ''
BEGIN
      select FailPoint = 'Method Name must be set'
      RETURN
END
set   @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255) 
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
                  source = @source, 
                  description = @desc, 
                  FailPoint = 'Create failed', 
                  MedthodName = @methodName 
      goto destroy 
      RETURN
END
-- open the destination URI with Specified method 
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'Open failed', 
            MedthodName = @methodName 
      goto destroy 
      RETURN
END
-- set request headers 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      RETURN
END
-- set soap action 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction 
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      RETURN
END
declare @len int
set @len = len(@requestBody) 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len 
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      return
END
/* 
-- if you have headers in a table called RequestHeader you can go through them with this 
DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500) 
DECLARE RequestHeader CURSOR
LOCAL FAST_FORWARD 
FOR
      SELECT      HeaderKey, HeaderValue 
      FROM RequestHeaders 
      WHERE       Method = @methodName 
OPEN RequestHeader 
FETCH NEXT FROM RequestHeader 
INTO @HeaderKey, @HeaderValue 
WHILE @@FETCH_STATUS = 0 
BEGIN
      --select @HeaderKey, @HeaderValue, @methodName 
      EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue 
      IF @hResult <> 0 
      BEGIN
            EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
            SELECT      hResult = convert(varbinary(4), @hResult), 
                  source = @source, 
                  description = @desc, 
                  FailPoint = 'SetRequestHeader failed', 
                  MedthodName = @methodName 
            goto destroy 
            return
      END
      FETCH NEXT FROM RequestHeader 
      INTO @HeaderKey, @HeaderValue 
END
CLOSE RequestHeader 
DEALLOCATE RequestHeader 
*/ 
-- send the request 
EXEC @hResult = sp_OAMethod @objectID, 'send', NULL, @requestBody 
IF    @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = CONVERT(VARBINARY(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'Send failed', 
            MedthodName = @methodName 
      GOTO destroy 
      RETURN
END
--declare @statusText varchar(1000), @status varchar(1000) 
---- Get status text 
--exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
--exec sp_OAGetProperty @objectID, 'Status', @status out
--select @status, @statusText, @methodName 
-- Get response text 
EXEC sp_OAGetProperty @objectID, 'responseText', @responseText OUT
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = CONVERT(VARBINARY(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'ResponseText failed', 
            MedthodName = @methodName 
      GOTO destroy 
      RETURN
END
destroy: 
      EXEC sp_OADestroy @objectID 
SET NOCOUNT OFF
 

GO

三、调用公共web接口例子

DECLARE @xmlOut NVARCHAR(max);
DECLARE @RequestText AS VARCHAR(8000);

SET @RequestText = '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://WebXml.com.cn/">
   <soapenv:Header/>
   <soapenv:Body>
      <web:getSupportCity>
         <!--Optional:-->
         <web:byProvinceName>北京</web:byProvinceName>
      </web:getSupportCity>
   </soapenv:Body>
</soapenv:Envelope>';

/**
调用这个存储过程的几个参数都可以在SoapUI上找出

1.@URI: 在步骤3中的第一行的地址
2.@MethodName: 在步骤3中第一行的POST
3.@RequestBody: 在步骤2中左侧的XML文档
4.@SoapAction: 在步骤3中的SOAPAction，本例中为“”，所以调用存储过程中这个参数写成了‘“”’
5.@UserName: 空
6.@Password: 空
7.@ResponseText: 返回结果
**/
EXEC spHTTPRequest 'http://www.webxml.com.cn/WebServices/WeatherWebService.asmx',
    'POST', @RequestText, '"http://WebXml.com.cn/getSupportCity"', '', '', @xmlOut OUT;

--SELECT @xmlOut

/**去掉返回xml的无用部分**/
DECLARE @data XML = REPLACE(REPLACE(@xmlOut,'<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><getSupportCityResponse xmlns="http://WebXml.com.cn/">',''),
'</getSupportCityResponse></soap:Body></soap:Envelope>', '');

/**将xml转换成sql结构数据**/
SELECT  T.c.value('.', 'varchar(50)') AS city 
FROM    @data.nodes('/getSupportCityResult/string') AS  T ( c )

四、一些转换输出的例子

/**转换例子
例子

DECLARE @data XML 
SET @data='<getSupportCityResult>
				<string>北京 (54511)</string>
				<string>上海 (58367)</string>
				<string>天津 (54517)</string>
				<string>重庆 (57516)</string>
			</getSupportCityResult>'


SELECT  T.c.value('.', 'varchar(50)') 
FROM    @data.nodes('/getSupportCityResult/string') AS  T ( c ) 


例子1

DECLARE @strxml XML
SET @strxml='<DataSet><ID>1</ID><ID>2</ID><ID>3</ID></DataSet>'

SELECT T.C.value('.','varchar(max)') As ID from @strxml.nodes('/DataSet/ID') as T(C)



例子2

Declare @Xml xml
Set @Xml = '
<DataSet>
 <Table><ID>1</ID><Count>2</Count></Table>
 <Table><ID>3</ID><Count>4</Count></Table>
 <Table><ID>5</ID><Count>6</Count></Table>
</DataSet>'

select  T.C.value('ID[1]','varchar(max)') as ID,
        T.C.value('Count[1]','varchar(max)') as COUNT
from    @xml.nodes('/DataSet/Table') as T(C)


例子3

XML--将XML中数据提取出转换成表
DECLARE @xml XML
SET @xml='
<Students>
    <Student  id="1001">
        <name>aaa</name>
        <age>20</age>
        <birthday>1991-2-20</birthday>
    </Student>
    <Student  id="1002">
        <name>bbb</name>
        <age>21</age>
        <birthday>1990-2-20</birthday>
    </Student>    
</Students>
'
SELECT T.C1.value('../@id','VARCHAR(20)') AS StudentId,
T.C1.value('.','VARCHAR(20)') AS StudentName,
T.C1.value('../age[1]','VARCHAR(20)') AS StudentAge,
T.C1.value('../birthday[1]','datetime') AS StudentBirthDay
FROM @xml.nodes('/Students/Student/name') AS T(C1)

select  T.C.value('@id','varchar(max)') as StudentId,
        T.C.value('name[1]','varchar(max)') as StudentName,
        T.C.value('age[1]','varchar(max)') as StudentAge,
        T.C.value('birthday[1]','datetime') as StudentBirthDay
from    @xml.nodes('/Students/Student') as T ( C )

 

Note:

1> .表示当前节点

2>  ..表示上级节点

3>  @表示属性

4>  第一个节点为[0]
**/

五、通过soapui查看调用web解析存储过程的步骤方法
步骤1：打开web地址

步骤2：选择要使用的接口方法

步骤3：打开左边raw查看参数。

步骤4：SQL Server 通过TSQL(存储过程)用MSXML去调用Webservice
调用这个存储过程的几个参数都可以再SoapUI上找出
1.@URI: 在步骤3中的第一行的地址
2.@MethodName: 在步骤3中第一行的POST
3.@RequestBody: 在步骤2中左侧的XML文档
4.@SoapAction: 在步骤3中的SOAPAction，本例中为“”，所以调用存储过程中这个参数写成了‘“”’
5.@UserName: 空
6.@Password: 空
7.@ResponseText: 返回结果

目录CONTENT

SQLSERVER调用web接口教程

评论区