0

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.

2
  • Calling web services is not the job of the database. That's the job of client applications Commented Aug 7, 2018 at 11:27
  • @PanagiotisKanavos You are right, I just edited my answer to reflect this... Commented Aug 7, 2018 at 12:36

1 Answer 1

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 *
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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.