json - SSMS:OPENJSON 有效,但不适用于直接 REST 调用
问题描述
按照这篇文章之后,我正在尝试实现一个查询,该查询可以从 REST 调用中检索数据并使用 OPENJSON 解析 JSON。
我用 Postman 调用这个 URL来检查数据是否正常:
数据在那里。
我复制粘贴 JSON 并在 SSMS 上测试我的调用:
DECLARE @json nvarchar(max) = N'{
"version": 0.6,
"generator": "Overpass API 0.7.55.4 3079d8ea",
"osm3s": {
"timestamp_osm_base": "2018-10-04T10:07:02Z",
"timestamp_areas_base": "2018-10-04T09:02:02Z",
"copyright": "The data included in this document is from www.openstreetmap.org. The data is made available under ODbL."
},
"elements": [
{
"type": "node",
"id": 501591237,
"lat": 49.4943882,
"lon": -117.2910343,
"tags": {
"amenity": "cinema",
"name": "Civic Theatre",
"website": "http://civictheatre.ca"
}
},
{
"type": "node",
"id": 2293276829,
"lat": -41.2709865,
"lon": 173.2842196,
"tags": {
"amenity": "cinema",
"name": "Cinema 6"
}
}
]
}';
SELECT * FROM OPENJSON(@json, N'$.elements')
WITH (
[type] nvarchar(max) N'$.type' ,
[id] nvarchar(max) N'$.id',
[lat] nvarchar(max) N'$.lat',
[lon] nvarchar(max) N'$.lon',
[amenity] nvarchar(max) N'$.tags.amenity',
[name] nvarchar(max) N'$.tags.name'
)
一切都很好,正在工作:我的查询可以解析 JSON。
我现在遵循本指南,该指南向我展示了如何在SSMS 中进行 REST 调用,我尝试检索相同 URL 的数据并将它们即时查看到 SSMS:
Declare @Object as Int;
Declare @json as nvarchar(max);
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Nelson%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @json OUTPUT
select @json
SELECT * FROM OPENJSON(@json, N'$.elements')
WITH (
[type] nvarchar(max) N'$.type' ,
[id] nvarchar(max) N'$.id',
[lat] nvarchar(max) N'$.lat',
[lon] nvarchar(max) N'$.lon',
[amenity] nvarchar(max) N'$.tags.amenity',
[name] nvarchar(max) N'$.tags.name'
)
Exec sp_OADestroy @Object
哎哟! 不管用!
为什么数据是空的?Declare @json as nvarchar(max);
在上一个查询中工作。为什么不再工作了?
如果我Declare @json as varchar(8000);
说它只是因为我检索不到 8000 个字符但我不能以这种方式工作。
如果我没记错的话OPENJSON
,正是为了避免此类问题并加载到 2GB 数据而开发的。
我想使用nvarchar(max)
和加载大量数据。这就是使用OPENJSON
.
解决方案
完毕。
非常感谢@PanagiotisKanavos 的建议,status
以便发现错误。
解决方案是使用Declare @json as table(Json_Table nvarchar(max))
JSON 并将其存储到表中而不是变量中。它现在可以存储2GB!!!
Declare @Object as Int;
DECLARE @hr int
Declare @json as table(Json_Table nvarchar(max))
Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
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'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
SELECT * FROM OPENJSON((select * from @json), N'$.elements')
WITH (
[type] nvarchar(max) N'$.type' ,
[id] nvarchar(max) N'$.id',
[lat] nvarchar(max) N'$.lat',
[lon] nvarchar(max) N'$.lon',
[amenity] nvarchar(max) N'$.tags.amenity',
[name] nvarchar(max) N'$.tags.name'
)
EXEC sp_OADestroy @Object
这是完成工作的正确方法吗?
一定不行!我不应该使用sp_OAMethod
,我应该使用,或者用, , ,等SQLCLR
编写的任何其他外部进程...Python
PowerShell
VB
C#
但是,你知道,我也应该少喝啤酒,少买培根,但是……YOLO,不管行得通……就去做吧。
多亏了这一点,我现在可以创建一个进行 REST 调用并将数据自动保存到 SQL Server 的存储过程。我寻找了几天的解决方案,终于在这里找到了答案。
我希望这会有所帮助,我看到有几百人在那里搜索这个回复。
推荐阅读
- haskell - 为什么运算符/函数转换不可逆?
- swift - 是否有任何解决方法可以快速指定内部 func 捕获列表及其修饰符
- excel - 在 VBA 中搜索特定文件
- javascript - angularjs datepicker不加载当前日期
- sql - 基于范围的窗口框架只能有 1 个排序键
- c# - Unity - 在生成时获得随机颜色
- c# - XNA/Monogame 中的 2d ZigZag 运动
- javascript - 将对象从屏幕传递到另一个屏幕 | 反应原生
- java - Filnet API 复制文件
- android - 如何以编程方式生成 11 个字符的应用程序唯一密钥以进行发布?