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&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
I want to retrieve data from webservice , then , insert it into a SQL table.
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 *
FROM OPENJSON(@json) A
OUTER APPLY OPENJSON(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 *
FROM OPENJSON(@json) A
OUTER APPLY OPENJSON(A.value) B
OUTER APPLY OPENJSON(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:
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(شرق بزگراه آزادگان-بين اتوبان کرج و حکيم(خرگوش دره) پ)
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
