rest - SSMS 和 sp_OAMethod : is there a Data Type greater than VARCHAR(8000)?

Question

通过 SSMS 调用 REST 服务确实不是一个好主意。

顺便说一句，自从微软创建了 Stored Procedure sp_OAMethod甚至来自 Red Gate 的 Phil Factor show us how to use it我想试一试。

我想从 OpenStreetMap 中直接导入一些数据到 MSSQL，所以我复制了一个很好的查询 from here我根据自己的意愿进行调整。

在这个例子中，我将返回 Nelson 的所有电影院:

DECLARE @obj AS INT
DECLARE @Uri AS NVARCHAR(4000)
DECLARE @Response AS VARCHAR(8000)

SET @Uri = 'http://overpass-api.de/api/interpreter?data=area[name="Nelson"]->.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
EXEC sp_OAMethod @obj, 'open', NULL, 'GET', @Uri, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'ResponseText', @Response OUTPUT

SELECT @Response [response] 
EXEC sp_OADestroy @obj

非常简单，我可以在 Postman 和 SSMS 中看到 REST 调用响应:

当我尝试从像奥克兰这样的大城市检索所有电影院时，问题就出现了:

DECLARE @obj AS INT
DECLARE @Uri AS NVARCHAR(4000)
DECLARE @Response AS VARCHAR(8000)

SET @Uri = 'http://overpass-api.de/api/interpreter?data=area[name="Auckland"]->.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
EXEC sp_OAMethod @obj, 'open', NULL, 'GET', @Uri, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'ResponseText', @Response OUTPUT

SELECT @Response [response] 
EXEC sp_OADestroy @obj

REST 调用正在检索更多数据，变量 @Response AS VARCHAR(8000) 无法容纳所有数据:

当然，我尝试使用 DECLARE @Response AS VARCHAR(MAX) 但这也无济于事。

难道 VARCHAR(MAX) 不应该包含 65,535 个字符 和直到 2GB 的数据吗？

我应该改用什么？

有没有办法拆分数据并稍后连接？

编辑:我想我越来越接近了:我可以使用 OPENJSON this way ，但我仍然不知道如何构建查询...任何帮助将不胜感激

Answer 1

我在给自己鼓掌。

我承认，这是一个噩梦般的解决方案，但它可以解决问题。解决方案是设置:

将@Response 声明为表(Json_Table nvarchar(max))

通过这种方式，我创建了一个数据类型为 nvarchar(max) 的表，现在是的，它可以容纳 65,535 个字符，直到 2GB 的数据。

Declare @Object as Int;
DECLARE @hr  int
Declare @Response as table(Json_Table nvarchar(max))

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://overpass-api.de/api/interpreter?data=[out:json];area[name="Auckland"]->.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
                 'false'
Exec @hr=sp_OAMethod @Object, 'send'
Exec @hr=sp_OAMethod @Object, 'responseText', @Response OUTPUT

INSERT into @Response (Json_Table) exec sp_OAGetProperty @Object, 'responseText'

select * from @Response

EXEC sp_OADestroy @Object

如果您找到更好的解决方案，请发布，我们将不胜感激。