[SQL Server] Envoyer la commande GET vers un service Web de type REST et récupérer un résultat en format JSON - Troisième méthode

  • Imprimer

DECLARE @Objet         ASInt

DECLARE @Resultat astable (Resultat VARCHAR(max))

DECLARE @json NVARCHAR(MAX)

IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#ResponseTable]'))BEGINDROPTABLE #ResponseTable END;

CREATETABLE #ResponseTable( ResponseText VARCHAR(MAX));

EXECsp_OACreate'MSXML2.XMLHTTP', @Objet OUT;

EXECsp_OAMethod@Objet,'open',NULL,'get','https://www.mektaba.info/data/all?Authorization=zouhaierkharroubi_MonTokenServiceWeb', false;

EXECsp_OAMethod@Objet,'send';

INSERTINTO #ResponseTable EXECsp_OAMethod@Objet,'responseText';

SET @json =(SELECT*from #ResponseTable);

insertinto [MaBase].[dbo].[MaTable](champ1,champ2,champ3)

(SELECT T2.champ1,T2.champ2,T2.champ3

FROM (

SELECT*

FROMOPENJSON(@json)

WITH (

Etat                  CHAR(3)'strict $.Etat',

DonneesJson             NVARCHAR(MAX)ASJSON

)

) T1

OUTERAPPLYOPENJSON(T1.DonneesJson)

WITH

(

champ1                   VARCHAR(250)'$.champ1',

champ2                            VARCHAR(250)'$.champ2',

champ3                            VARCHAR(250)'$.champ3'

) T2);

DROPTABLE #ResponseTable