如何从第三方xml Web服务获取和存储数据到sql表

Is there any way to get and store 3rd party Web Service XML data directly into SQL Server DB?

有没有办法将第三方Web服务XML数据直接存入SQL Server DB？

I know there is a lot of other easy solutions like Rest or WCF but in my case I want to add new job to SQL that will trigger every day and get the data from the Web Service.

我知道有许多其他简单的解决方案，如Rest或WCF，但在我的情况下，我想向SQL添加新工作，每天都会触发并从Web服务获取数据。

1 个解决方案

#1

To connect to some resources and get XML response you can use this code:

要连接到某些资源并获取XML响应，您可以使用以下代码：

DECLARE @URI nvarchar(max) = 'http://example.com/',
        @methodName nvarchar(50) ='GET',
        @objectID int,
        @setTimeouts nvarchar(255) = 'setTimeouts(9000,90000,900000,9000000)'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @ObjectID OUT

EXEC sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false' 
EXEC sp_OAMethod @objectID, @setTimeouts
EXEC sp_OAMethod @objectID, 'send', null 

INSERT INTO SomeTable (xmlResponse)
EXEC sp_OAGetProperty @objectID, 'responseText'

EXEC sp_OAGetErrorInfo @objectID
EXEC sp_OADestroy @objectID

You can comment @setTimeouts part, it is needed if response time may be big.

您可以评论@setTimeouts部分，如果响应时间可能很大，则需要它。

Put this in stored proc. Pass @URI as parameter and this will write response in SomeTable. Then you can put EXEC YourStoredProv @URI into job and schedule each day launch.

把它放在存储过程中。将@URI作为参数传递，这将在SomeTable中写入响应。然后你可以把EXEC YourStoredProv @URI投入工作并安排每天发布。

EDIT

编辑

With authorization:

授权：

DECLARE @URI nvarchar(max) = 'http://example.com/',
        @auth nvarchar(max),
        @methodName nvarchar(50),
        @objectID int,
        @setTimeouts nvarchar(255) = 'setTimeouts(9000,90000,900000,9000000)'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @ObjectID OUT

SELECT  @auth= @URI + 'AppEngine_2/api_login?user_name=myuserName&password=myPassword%20&TOKEN=chinaurl&CLIENT_TYPE=1%22',
        @methodName='POST'

EXEC sp_OAMethod @objectID, 'open', null, @methodName, @auth, 'false' 
EXEC sp_OAMethod @objectID, 'send', null 

SELECT  @methodName='GET'

EXEC sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false' 
EXEC sp_OAMethod @objectID, @setTimeouts
EXEC sp_OAMethod @objectID, 'send', null 

INSERT INTO SomeTable (xmlResponse)
EXEC sp_OAGetProperty @objectID, 'responseText'

EXEC sp_OAGetErrorInfo @objectID
EXEC sp_OADestroy @objectID

You can pass myuserName, myPassword and TOKEN as variables as URL to get response.

您可以将myuserName，myPassword和TOKEN作为变量传递给URL以获取响应。

#1