如何使用SQL查询将JSON Web服务数据插入到SQL表中?

问题描述

我的链接是: http://emkan.bstc.ir:8082/bstco/emkan/mgmt/server/index.php?road=webapi/testwebservice/getDataEmkan& Date1 = 13961210& Date2 = 13961210& Hour1 = 0& Hour2 = 0& usr = movalled& pwd = 123456& pulist = 3,17366,17368,17370,17364,17372,17374,17378,17377,17379， 17380,17381,17382,17392,17394,17396,17388,17390,17409,17411,17413,17405,17407，& ctpt = 2

我想从webservice检索数据，然后将其插入到SQL表中.

I want to retrieve data from webservice , then , insert it into a SQL table.

推荐答案

这是一个非常糟糕的问题...

This is a very poor question...

SQL-Server不是从Web服务读取JSON的工具.这应该由您的应用程序完成.您可以在应用程序中解析JSON并传递已解析的数据，也可以按原样传递JSON 并使用SQL Server的功能.

SQL-Server is not the tool to read the JSON from the web-service. This should be done by your application. You can resolve the JSON within your application and pass the resolved data or you can pass the JSON as is and use SQL-Server's abilities.

请注意，原生JSON支持在v2008 R2中不可用. 这是是SQL Server 2016引入的.

Be aware, that native JSON support is not available in v2008 R2. This was introduced with SQL Server 2016.

请发布您的JSON的简化示例(！)，以及您希望如何插入该表的表结构.最好是DDL和一些代码来展示您自己的尝试并重现您的问题.讨厌链接的人...
而且:SO并不是做我的工作平台...

Please post a reduced(!) example of your JSON and the table structure how you want to insert this. Best is DDL and some code to show your own attempt and reproduce your issues. People on SO hate links...
And: SO is not a do-my-work platform...

仅提示如何进行操作(对于任何应用程序的代码均有效):

Just some hints how to proceed (the same is valid for any application's code):

DECLARE @json NVARCHAR(MAX)=
N'PlaceTheJsonHere';

SELECT *
FROM OPENJSON(@json)

在我从您的链接获得两行的情况下，其中一行包含成功"和一个数组，第二行包含结果"和一个空数组.
所以我尝试进入数组

returns in the case I got from your link two rows, one with "success" and an array, the second with "result" and an empty array.
So I try to get into the array

SELECT *
FROM OPENJSON(@json) A
OUTER APPLY OPENJSON(A.value)

现在我得到成功"的402行.值再次是一个数组:

Now I get 402 rows with "success". The value is - again - an array:

一个示例值如下

["049644010478","049644010478","049644010478","049644010478","\u06a9\u0646\u062a\u0648\u0631\u0647\u0627\u06cc \u0645\u0648\u0644\u062f \u0628\u0631\u0642 \u0634\u0645\u0627\u0644 \u063a\u0631\u0628","600\/5","1\/1","\u0633\u0627\u0632\u0645\u0627\u0646 \u0635\u0646\u0627\u064a\u0639 \u062f\u0641\u0627\u0639","\u062f\u0627\u0646\u0634\u06af\u0627\u0647-\u0645\u0648\u0644\u062f\u0628\u0631\u0642","\u0633\u0627\u0632\u0645\u0627\u0646 \u0635\u0646\u0627\u064a\u0639 \u062f\u0641\u0627\u0639",null,null,"13961210","0","12","10","1396","\u067e\u0646\u062c \u0634\u0646\u0628\u0647","1210","1210.0",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"OFFLINE"]

愚蠢的\u0633值是Unicode代码点.

The silly \u0633 values are unicode code points.

再上一层:

SELECT *
FROM OPENJSON(@json) A
OUTER APPLY OPENJSON(A.value) B
OUTER APPLY OPENJSON(B.value) C

现在，我发现第一行提供了56个列名，而其他行则提供了内容.这似乎是一张桌子.下图显示了最后一个列名和第一个值的摘录(现在总共有超过22500行:

Now I find, that the first row presents 56 column names, while the other rows provide content. This seems to be a table. the following shows an excerpt with the last column names and the first values (all together there are more than 22500 rows now:

52  POWER_FACTOR_PHASE_A
53  POWER_FACTOR_PHASE_B
54  POWER_FACTOR_PHASE_C
55  READ_FLAG
0   049644009814
1   049644009814
2   049644009814
3   049644009814
4   کنتورهای مولد برق شمال غرب
5   1000/5
6   1/1
7   آفتاب 22(شرق بزگراه آزادگان-بين اتوبان کرج و حکيم(خرگوش دره) پ)

太好了！ JSON引擎隐式地将unicode代码点显示为可读字母！

Great! the JSON engine implicitly shows the unicode code points as readable letters!

我们现在知道什么?该json提供了成功"和结果"部分. 成功部分包含一个数组数组，其中第一个索引是列名称的数组，而其余的是表格数据.

What do we know now? This json provides a "success" and a "result" section. The "success section contains an array of arrays, where the first index is an array of column names, while the rest is tabular data.

其余的取决于您:-D

The rest is up to you :-D

这篇关于如何使用SQL查询将JSON Web服务数据插入到SQL表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！