How I Can Insert Json Web Service Data Into A Sql Table Using Sql Query?

my link is: http://emkan.bstc.ir:8082/bstco/emkan/mgmt/server/index.php?road=webapi/testwebservice/getDataEmkan&Date1=13961210&Date2=13961210&Hour1=0&Hour2=0&us

Solution 1:

This is a very poor question...

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.

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

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 *
FROMOPENJSON(@json) AOUTERAPPLYOPENJSON(A.value)

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

One example value looks like this

["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"]

The silly \u0633 values are unicode code points.

Again one level deeper:

SELECT *
FROMOPENJSON(@json) AOUTERAPPLYOPENJSON(A.value) BOUTERAPPLYOPENJSON(B.value) C

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:

52POWER_FACTOR_PHASE_A53POWER_FACTOR_PHASE_B54POWER_FACTOR_PHASE_C55READ_FLAG00496440098141049644009814204964400981430496440098144کنتورهایمولدبرقشمالغرب51000/561/17آفتاب22(شرقبزگراهآزادگان-بيناتوبانکرجوحکيم(خرگوشدره)پ)

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

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.

The rest is up to you :-D