JSON Verilerini Web Servisleri ve Uygulamalarından SQL Server'a Alma

Minimum SQL Server 2016 olmak zorundadır.

versiyon bilgisini sorgulamak ve güvenlik seviyesini ayarlayabilmek için aşağıdaki kodu SQL Server da çalıştırabilirsiniz.

SELECT @@VERSION

ALTER DATABASE binance SET COMPATIBILITY_LEVEL = 130

Birçok uygulamayı desteklemek için, veritabanının JSON verileriyle çalışması mantıklıdır, çünkü bir JavaScript veya TypeScript uygulamasının nesne verilerini temsil etmesi için yerleşik bir yoldur. Bu daha az ağ trafiği, daha gevşek bağlantı ve uygulama geliştiricisinin veritabanının taban tablolarına tam erişim gerektirmesi için daha az ihtiyaç anlamına gelebilir. Ancak, veritabanının içe aktarılmadan önce çok sayıda kontrol yapması gerektiği anlamına gelir.

Veritabanı geliştiricilerinin veya DBA'ların JSON'u şüphe ile görmeleri için çeşitli nedenler var. Daha yüksek düzeydeki kısıtlamaların korunmasında kullanılırlar ve içe aktarılan verileri kontrol ederler: İçeri aktarmadan önce verilerin meta verilerinin doğru olduğundan emin olmak isterler: Verilerin kendisini de kontrol etmek isterler. En basit seviyede, bir veri kaynağından gelen düzenli bir beslemenin mümkün olduğu kadar güçlü olduğundan emin olmak isterler. Neden bu kadar telaşlı? Basitçe, kötü veriler herhangi bir boyutta bir veritabanına girdiğinde, çıkarmak yorucu ve zaman alıcıdır.

JSON verilerini web tabanlı uygulamalardan, özellikle tek sayfalık olanlardan almanın yanı sıra, birçok veritabanı, para birimlerinin listesi ve dolara karşı geçerli değerleri gibi web hizmetlerinden gelen değişken verilere dayanır. Her seferinde, JSON'un normalde olduğu gibi aynı meta veri veya veri yapısına sahip olduğundan ve verilerin geçerli olduğundan emin olmamız gerekir. Günümüzde, uygulama verileri genellikle bir JSON belgesi olarak gelse de, diğer belge tabanlı veri türleriyle benzer bir sorunumuz var: örneğin, iki şekilde var olan XML: iyi ve erdemli olan ancak iyi yaramaz kız kardeşi, şema-az veya yazılmamış XML. Yazılan XML, bir SQL Server veritabanında izin vermeniz gereken tek tür XML'dir. Sadece kullanımı daha güvenli olmakla kalmıyor, aynı zamanda çok daha verimli bir şekilde saklanıyor. Türlenmemiş XML, JSON ve YAML hepsi kontrol gerektirir.
JSON ile aynı şekilde XML yazdığımız gibi yapmak istiyoruz ve JSON Schema'nın eşeğin çalışmasına izin veriyoruz; Ancak, ne yazık ki, SQL Server şu anda herhangi bir JSON şeması bağlamasını desteklememektedir ve bu nedenle JSON'u 'derlenmiş' bir biçimde verimli bir şekilde depolayamamaktadır. 

Baskın JSON Şeması aslında 'JSON Şeması' olarak adlandırılır ve IETF taslak 6'dadır. İnsan tarafından okunabilir JSON'da bir veri formatını tanımlayabilir ve tam bir yapısal doğrulama sağlamak için kullanılabilir. PostgreSQL, JSON'u doğrulamak için postgres-json-şemaya sahiptir, JavaScript'in bunu yapmak için birçok eklentisi vardır. Net programcıların Json.NET Schema ve diğer birçok alternatifi var.

PowerShell, JSON'unuzu JSON.net şeması ile doğrulamak için açık bir yerdir, ancak aslında, JSON verilerinizi kontrol etmek için SQL'de yapabileceğiniz pek çok şey vardır. Bir şeylerin değişip değişmediğini görmek için iki JSON belgesinin meta verilerini kolayca karşılaştırabilir ve JSON Verileri üzerinde kısıtlamalar uygulayabilirsiniz. Bu, yol ifadelerinin kullanımı nedeniyledir. Başlamak için, onları daha iyi tanımlayacağız, çünkü bunlar, JSON'un SQL Server'daki ciddi kullanımı için çok önemlidir.

Yol ifadeleri

SQL Server'daki bir JSON belgesindeki değerleri okumanız veya yazmanız gerektiğinde, JSON yol ifadelerini kullanırsınız. Bunlar nesnelere, dizilere veya değerlere referans verebilir.

İlişkisel tablo kaynağını döndürmek için WITH yantümcesine sahip OPENJSON'u çağırırsanız, JSON_VALUE'yu JSON metninden tek bir değer çıkarmak için veya bu değeri güncellemek veya ona eklemek için JSON_MODIFY'yi çağırırsanız, bu yol ifadeleri gerekir.

Yolun kendisi, içerik öğesini temsil eden bir dolar işareti ($) ile başlar. Yol, 'noktalar' veya tam duraklarla ayrılmış öğelerden oluşur.

Özellik yolu, isteğe bağlı olarak 'alıntılanan' Anahtar adlarından oluşan bir dizi yol adımıdır. (örneğin, $ .info.address.county veya $ .info.address. ”posta kodu” .outward ). Her nokta Lvalue (sol taraf değeri), RValue’nin ebeveyni (sağ taraf değeri) gösterir. Anahtar adı bir dolar işaretiyle başlıyorsa veya boşluklar gibi özel karakterler içeriyorsa, anahtar için çift tırnaklı ayırıcılar kullanmanız gerekir (örn. $ .İnfo.Cost. ”La Posche Hotel” ). Anahtar ismi bir diziye atıfta bulunuyorsa, sıfır tabanlı bir dizine göre dizine eklenir. (örneğin, $ .info.tags [0] veya $ .info.tags [1] )

Yol birden fazla var olan bir nesneye başvuruyorsa, ne JSON_Value ne de JSON_modify ikinci veya daha sonraki değerlere erişemez. Bu durumda, tüm değerlere ulaşmak için OpenJSON kullanmanız gerekir.

SQL Server'daki JSON yolları 'lax' veya 'strict' anahtar kelimesiyle başlayabilir. JSON belgesinde bir yol bulunmazsa, hataları bastırmak için alışılmadık bir gereksinimdir, ancak bu varsayılandır ve bunu 'lax' kullanarak belirtebilirsiniz. 'Katı' mod isteme olasılığınız daha yüksek olur, ancak NULL dönüş değerinde bir şeyin yanlış olduğunu bildiğiniz için bir yol değerinin olup olmadığını test etmek istiyorsanız 'lax' daha iyidir

Yol ifadelerini kolayca kullanabilirsiniz. Muhtemelen ihtiyacınız olan ilk işlev, bu ifadelerin gerçekte herhangi bir JSON belgesi için gerçekte ne olduğunu söyleyen bir yordamdır.

Bir JSON dizesinde veya belgesinde hangi yolların bulunduğunu bulma

İşte bir JSON dizesini alan ve belirttiğiniz JSON için ifadeleri, veri türlerini ve değerlerini içeren bir tablo kaynağı döndüren bir yordam.

IF Object_Id('dbo.JSONPathsAndValues') IS NOT NULL DROP FUNCTION dbo.JSONPathsAndValues;
  GO
  CREATE FUNCTION dbo.JSONPathsAndValues
    /**
    Summary: >
     This function takes a JSON string and returns
    a table containing the JSON paths to the data,
    and the data itself. The JSON paths are compatible with  OPENjson, 
    JSON_Value and JSON_Modify. 
    Author: PhilFactor
    Date: 06/10/2017
    Version: 2 
    Database: PhilFactor
    Examples:
       - Select * from dbo.JSONPathsAndValues(N'{"person":{"info":{"name":"John", "name":"Jack"}}}')
       - Select * from MyTableWithJson cross apply dbo.JSONPathsAndValues(MyJSONColumn)
    Returns: >
    A table listing the paths to all the values in the JSON document 
    with their type and their order and nesting depth in the document
   **/
    (@JSONData NVARCHAR(MAX))
  RETURNS @TheHierarchyMetadata TABLE
    (
    -- columns returned by the function
    element_id INT NOT NULL,
    Depth INT NOT NULL,
    Thepath NVARCHAR(2000),
    ValueType VARCHAR(10) NOT NULL,
    TheValue NVARCHAR(MAX) NOT NULL
    )
  AS
    -- body of the function
    BEGIN
      DECLARE @ii INT = 1, @rowcount INT = -1;
      DECLARE @null INT = 0, @string INT = 1, @int INT = 2, --
        @boolean INT = 3, @array INT = 4, @object INT = 5;
      DECLARE @TheHierarchy TABLE
        (
        element_id INT IDENTITY(1, 1) PRIMARY KEY,
        Depth INT NOT NULL, /* effectively, the recursion level. =the depth of nesting*/
        Thepath NVARCHAR(2000) NOT NULL,
        TheName NVARCHAR(2000) NOT NULL,
        TheValue NVARCHAR(MAX) NOT NULL,
        ValueType VARCHAR(10) NOT NULL
        );
      INSERT INTO @TheHierarchy
        (Depth, Thepath, TheName, TheValue, ValueType)
        SELECT @ii, '$', '$', @JSONData, 'object';
      WHILE @rowcount <> 0
        BEGIN
          SELECT @ii = @ii + 1;
          INSERT INTO @TheHierarchy
            (Depth, Thepath, TheName, TheValue, ValueType)
            SELECT @ii,
              CASE WHEN [Key] NOT LIKE '%[^0-9]%' THEN Thepath + '[' + [Key] + ']' --nothing but numbers 
                WHEN [Key] LIKE '%[$ ]%' THEN Thepath + '."' + [Key] + '"' --got a space in it
                ELSE Thepath + '.' + [Key] END, [Key], Coalesce(Value,''),
              CASE Type WHEN @string THEN 'string'
                WHEN @null THEN 'null'
                WHEN @int THEN 'int'
                WHEN @boolean THEN 'boolean'
                WHEN @int THEN 'int'
                WHEN @array THEN 'array' ELSE 'object' END
            FROM @TheHierarchy AS m
              CROSS APPLY OpenJson(TheValue) AS o
            WHERE ValueType IN
          ('array', 'object') AND Depth = @ii - 1;
          SELECT @rowcount = @@RowCount;
        END;
      INSERT INTO @TheHierarchyMetadata
        SELECT element_id, Depth, Thepath, ValueType, TheValue 
        FROM @TheHierarchy
        WHERE ValueType NOT IN
      ('array', 'object');
      RETURN;
    END;
  GO

Bununla, bir json verisinin değerlerinde ne olduğunu görebilirsiniz.

DECLARE @JSONData NVARCHAR(4000) = N'
  {
      "info": {
          "type": 1,
          "address": {
              "town": "Colchester",
              "county": "Essex",
              "country": "England"
          },
          "Hotels": {
              "La Posche Hotel": "$400",
              "The Salesmans Rest": "$35",
              "The Middling Inn": "$100"}
      },
      "Sights": ["the Castle","The Barracks","the Hythe","St Bartolphs"]
  } ';
  SELECT * from dbo.JSONPathsAndValues(@JSONData) 

İki JSON dizesinin meta verileri arasındaki farklar

Aynı meta verilere sahip olduklarından emin olmak için iki JSON dizesini kontrol etmek ve bulduğu farklılıkları rapor etmek çok kolaydır.

IF Object_Id('dbo.DifferenceBetweenJSONstrings') IS NOT NULL
     DROP function dbo.DifferenceBetweenJSONstrings
  GO
  CREATE FUNCTION dbo.DifferenceBetweenJSONstrings
  /**
  Summary: >
    This checks two JSON strings and  returns
    a table listing any differences in the schema between them
  Author: PhilFactor
  Date: 20/10/2017
  Database: PhilFactor
  Examples:
     - Select * from dbo.DifferenceBetweenJSONstrings(@Original,@new)
     - Select  from MyTable cross apply dbo.DifferenceBetweenJSONstrings(FirstJ)
  Returns: >
    A table
          **/
    (
    @Original nvarchar (max),-- the original JSON string
    @New nvarchar (max)      -- the New JSON string
    )
  RETURNS TABLE
   --WITH ENCRYPTION|SCHEMABINDING, ..
  AS
  RETURN
    (
    SELECT Coalesce(old.thePath, new.thepath) AS JSONpath,
      Coalesce(old.valuetype, '')
      + CASE WHEN old.valuetype + new.valuetype IS NOT NULL THEN ' \ ' ELSE '' END
      + Coalesce(new.valuetype, '') AS ValueType,
      CASE WHEN old.valuetype + new.valuetype IS NOT NULL THEN 'value type changed'
        WHEN old.thePath IS NULL THEN 'added or key changed'
        WHEN new.thePath IS NULL THEN 'missing' ELSE 'dunno' END AS TheDifference
      FROM dbo.JSONPathsAndValues(@New) AS new
        FULL OUTER JOIN dbo.JSONPathsAndValues(@original) AS old
          ON old.ThePath = new.ThePath --AND old.Valuetype=new.Valuetype
      WHERE old.thepath IS NULL OR new.thepath IS NULL OR old.ValueType <> new.ValueType
       
    );
  Go

Yapının bir parçası olarak, işlevin beklediğiniz şeyi yapmasını sağlamak için böyle bir kod çalıştırarak test edebilirsiniz.

Select * from dbo.DifferenceBetweenJSONstrings(
    '[12,32,35,49,15,56,37]','[1,2,3,4,5,6]')
      Select * from dbo.DifferenceBetweenJSONstrings(
    '[1,2,3,4,5,6]','[1,"2",3,4,5,6]')
       Select * from dbo.DifferenceBetweenJSONstrings(
    '{"id": "001","type": "Coupe","name": "Cougar","year": "2012"}',
    '{"id": "004","type": "Coupe","name": "Jaguar","year": "2012"}')
        Select * from dbo.DifferenceBetweenJSONstrings(
    '{"id": "001","type": "Coupe","name": "Cougar","year": "2012"}',
    '{"id": "001","type": "Coupe","name": "Cougar","start": "2012"}')

DECLARE @TestData TABLE (JSONpath NVARCHAR(MAX),ValueType VARCHAR(20), TheDifference VARCHAR(20))
    INSERT INTO @testdata 
  	SELECT JSONpath,ValueType,TheDifference 
  	  FROM dbo.DifferenceBetweenJSONstrings(
         '[12,32,35,49,15,56,37]','[1,2,3,4,5,6]')
      UNION ALL SELECT JSONpath,ValueType,TheDifference 
  	  FROM dbo.DifferenceBetweenJSONstrings(
         '[1,2,3,4,5,6]','[1,"2",3,4,5,6]')
      UNION all Select JSONpath,ValueType,TheDifference 
  	  FROM dbo.DifferenceBetweenJSONstrings(
    '{"id": "001","type": "Coupe","name": "Cougar","year": "2012"}',
    '{"id": "004","type": "Coupe","name": "Jaguar","year": "2012"}')
      UNION all  Select JSONpath,ValueType,TheDifference 
  	  FROM dbo.DifferenceBetweenJSONstrings(
    '{"id": "001","type": "Coupe","name": "Cougar","year": "2012"}',
    '{"id": "001","type": "Coupe","name": "Cougar","start": "2012"}')
  IF (EXISTS(  
    SELECT * FROM @testData g
      FULL OUTER JOIN 
       (VALUES('$[6]','int','missing'),
         ('$[1]','int \ string','value type changed'),
         ('$.START','string','added or key changed'),
         ('$.year','string','missing'))f(JSONpath,ValueType,TheDifference)
      ON f.JSONpath=g.JSONpath
    WHERE f.JSONpath IS NULL OR g.JSONpath IS NULL))
  	RAISERROR ('the dbo.DifferenceBetweenJSONstrings routine is
  	giving unexpected results;',16,1)

Şimdi iki JSON dizesinin meta verilerini karşılaştırabiliriz, ancak iki JSON belgesinin aynı verileri temsil ettiğini kontrol etmemiz gerekirse, değerleri de karşılaştırabiliriz.

JSON'un beklediğiniz gibi olup olmadığını kontrol etme

Web Servislerinin çoğu, beklediğiniz veriler yerine, bilgi mesajları veya uyarı mesajları JSON formatında gönderilir. Hizmet kesintisi, abonelik sonlandırmaları veya ulaşılan günlük kredi limitleri gibi çeşitli mesajlar içerebilir. Bunların kaydedilmesi ve ithalat rutini atlamanız gerekir. Muhtemelen JSON'unuzu kontrol etmenin en hızlı yolu, NULL elde edip etmediğinizi görmek için orada olması gereken bir anahtar / değer çiftinde basit bir JSON_VALUE çağrısı yapmaktır. Bu yolu ilk etapta almak için JSONPathsAndValues işlevini kullanabilirsiniz . Ancak, orada olması gereken birkaç anahtar / değer çifti olması mümkündür. İhtiyacınız olan yolların bir listesi veya tablosu varsa, bunu yapmanın birçok yolu vardır. Noktayı göstermek için VALUES tablo kaynağı kullanacağım.

DECLARE @json NVARCHAR(MAX) = N'[  
    {  
      "Order": {  
        "Number":"SO43659",  
        "Date":"2011-05-31T00:00:00"  
      },  
      "AccountNumber":"AW29825",  
      "Item": {  
        "Price":2024.9940,  
        "Quantity":1  
      }  
    },  
    {  
      "Order": {  
        "Number":"SO43661",  
        "Date":"2011-06-01T00:00:00"  
      },  
      "AccountNumber":"AW73565",  
      "Item": {  
        "Price":2024.9940,  
        "Quantity":3  
      }  
    }
  ]'  
  IF EXISTS(
  SELECT thepath
  FROM 
  (VALUES
  ('$[0].AccountNumber'),
  ('$[0].Order.Number'),
  ('$[0].Order.Date'),
  ('$[0].Item.Price'),
  ('$[0].Item.Quantity'))WhatThereShouldBe(path)
  LEFT OUTER JOIN  dbo.JSONPathsAndValues( @json )  
  ON WhatThereShouldBe.path=ThePath
  WHERE ThePath IS NULL)
  RAISERROR ('an essential key is missing',16,1)

Meta verilerin yanı sıra değerleri karşılaştırma

Anahtarları kontrol etmek için kodda çok küçük bir değişiklik, daha önce yapmanız gerektiğinde değerleri karşılaştırmanıza da izin verecektir. Listelerin sırasını da kontrol ettiğini unutmayın.

SELECT FirstVersion.Thepath, SecondVersion.Thepath, FirstVersion.TheValue, SecondVersion.TheValue
    FROM dbo.JSONPathsAndValues('[1,2,3,4,5,7,8]') AS FirstVersion
      FULL OUTER JOIN dbo.JSONPathsAndValues('[1,2,3,4,5,6]') AS SecondVersion
        ON FirstVersion.Thepath = SecondVersion.Thepath
    WHERE FirstVersion.TheValue <> SecondVersion.TheValue
       OR FirstVersion.Thepath IS NULL
       OR SecondVersion.Thepath IS NULL;

Gördüğünüz gibi, JSON dizeleri arasında iki fark var. Aynı dizi elemanı için farklı değerler var (sonuçtaki satır 1) ve farklı sayıda dizi elemanı var (satır 2).

Geçerli SQL Veri Tipleri denetleniyor.

Olduğu kadarıyla sorun değil, ama verilerin gerçekten SQL Server'a girip girmeyeceğini kontrol etmeye ne dersiniz? Örneğin, JSON'da tarih depolamak için iyi uygulamalar olmasına rağmen, örneğin, JSON standardı yoktur. Kısıtlarımızı biliyorsak, kontrol etmesi çok kolaydır. Belirli bir veri türünde olan bazı JSON'larımız olduğunu hayal edin. Sadece kontrol etmek istediğimiz değerlerin yolunu tanımlıyoruz ve JSON'da ne gerekiyorsa yapalım kontrol ediyoruz. Bu tekniği gösterebiliriz.

SELECT g.ThePath, g.TheValue, CASE Coalesce(f.Datatype,'') 
  		WHEN '' THEN  '' --it hasnt had a check specified 
  		WHEN 'int' THEN --need to check that it is a valid int
  		 CASE WHEN Try_Convert(INT,g.TheValue) IS NULL THEN 'Bad int' ELSE 'good' end
  		WHEN 'DateTime' THEN --need to check that it is a valid DateTime
  		 CASE WHEN Try_Convert(DateTime,g.TheValue) IS NULL THEN 'Bad datetime' ELSE 'good' end
  		WHEN 'Money' THEN --need to check that it is a valid Money value
  		 CASE WHEN Try_Convert(Money,g.TheValue) IS NULL THEN 'Bad money value' ELSE 'good' END
          WHEN 'ISO8601' THEN --need to check that it is a valid ISO8601 datetime
  		 CASE WHEN Try_Convert(DateTime,g.TheValue) IS NULL THEN 'Bad ISO8601 datetime' ELSE 'good' end	
  	   ELSE '' end
  	
  FROM dbo.JSONPathsAndValues('["1.7","2","23/4/2008","1 Jun 2017","5.6d","$456,000","2017-09-12T18:26:20.000","2017/10/20T18:26:20.000"]')g
  LEFT outer JOIN
       (VALUES('$[0]','int'),
  	   ('$[1]','int'),
         ('$[2]','DateTime'),
  	   ('$[3]','DateTime'),
  	   ('$[4]','money'),
  	   ('$[5]','money'),
  	   ('$[6]','ISO8601'),
  	   ('$[7]','ISO8601'))f(Thepath,DataType)
      ON f.thepath=g.Thepath

JSON'un yanı sıra, her bir değerin ne tür bir veri olması gerektiğini söylemek için ayrı bir tablo kaynağına ihtiyaç duyacağınızı ve kısıtlamanızı daraltabileceğinizi göstermek için özel bir tarih biçimi yaptım. Bir VALUES tablo kaynağı kullandım, ancak kolayca bir JSON içinden takas edebilirsiniz. Bu tablo kaynağının çalışması için case ifadesiyle senkronize tutulması gerekir. Çalışan bir sistemde, tüm bunları bir işlevde saklamak istersiniz.

Değerlerin sınır kontrollerini geçip geçmeyeceğini kontrol etme

Peki, tüm bu IP adreslerinin geçerli olduğundan emin olmak için bu basit örneği kontrol etme görevini nasıl ele alırsınız? Bu örnek için sadece üç nokta olup olmadığını kontrol edeceğiz. Belli ki adı kontrol etmek istemiyoruz.

{  
   "name":[  
      "Philip",
      "Mildew",
      "Factor"
   ],
   "ipAddress":[  
      "80.243.543.4",
      "45.85.678.68",
      "5.8.7.9",
      "192.168.0.123",
      "34.8.8"
   ]
}

Bu son IP adresini veritabanında istemezsin. Geçerli değil. Sadece bu IP değerlerini kontrol etmek istiyorsun. Burada, kontrolü çok kolay bir şekilde yapabilirsiniz.

SELECT ThePath, TheValue 
   FROM dbo.JSONPathsAndValues('{"name":["Philip","Mildew","Factor"],"ipAddress":[ "80.243.543.4","45.85.678.68","5.8.7.9","192.168.0.123","34.8.8"]}')
  WHERE ThePath LIKE '$.ipAddress%' AND TheValue NOT LIKE  '%.%.%.%'

Ve kötü IP adresini göreceksiniz, ancak yalnızca istediğiniz IP adresleri listesindeki denetimi gerçekleştiriyor.

Yapmanız gereken çok sayıda çek varsa, tablo kaynağını bir tablo değişkeni veya geçici tablo olarak kaydeder ve üzerinde birkaç sorgu çalıştırırsınız.

Bir şeyleri büyütmek

Normalde büyük miktarlarda JSON verisi ile ilgileniyorsunuz, bu nedenle tüm meta veri denetimlerinizi çalıştırmadan önce yalnızca bir kez geçici bir tabloya veya tablo değişkenine ayrıştırmak istiyorsunuz. Bu şekilde yapmanın avantajı, yola iyi bir birincil anahtar yerleştirdiğinizde (yinelenen JSON anahtarlarına dikkat edin: bunlar geçerli JSON - RFC 4627'dir), öncelikle verilerin başarılı bir şekilde zorlanabildiğini kontrol etme süreci Hedef tablonun uygun sütununa girdikten sonra, hiyerarşik JSON verilerini tüm sırayla ilişkisel tablolara doğru sırayla açmadan önce sınırlar dahilinde olduğunu kontrol etmek çok daha kolay ve iyi kontrol edilir.

SQL Server’dan bir JSON Web sunucusu kullanma

Normalde, normal bir üretim veri beslemesi için SSIS veya Powershell kullanırsınız, ancak bunu SQL'de yapmak kesinlikle mümkündür. Dezavantajı ise, SQL Server'ınızın internet erişimine sahip olması, bu da bir güvenlik riskidir ve ayrıca OLE otomasyonuna izin vererek güvenlik yüzey alanınızı açmanız gerekir. Bu, nasıl yaptığınızı söyledi.

IF NOT EXISTS (SELECT * FROM sys.configurations WHERE name ='Ole Automation Procedures' AND value=1)
  	BEGIN
     EXECUTE sp_configure 'Ole Automation Procedures', 1;
     RECONFIGURE;  
     end 
  SET ANSI_NULLS ON;
  SET QUOTED_IDENTIFIER ON;
  GO
  IF Object_Id('dbo.GetWebService','P') IS NOT NULL 
  	DROP procedure dbo.GetWebService
  GO
  CREATE PROCEDURE dbo.GetWebService
    @TheURL VARCHAR(255),-- the url of the web service
    @TheResponse NVARCHAR(4000) OUTPUT --the resulting JSON
  AS
    BEGIN
      DECLARE @obj INT, @hr INT, @status INT, @message VARCHAR(255);
      /**
  Summary: >
    This is intended for using web services that 
    utilize JavaScript Object Notation (JSON). You pass it the link to
    a webservice and it returns the JSON string
  Note: >
    OLE Automation objects can be used within a Transact-SQL batch, but 
    SQL Server blocks access to OLE Automation stored procedures because
    this component is turned off as part of the security configuration.
   
  Author: PhilFactor
  Date: 26/10/2017
  Database: PhilFactor
  Examples:
     - >
     DECLARE @response NVARCHAR(MAX) 
     EXECUTE dbo.GetWebService 'http://headers.jsontest.com/', @response OUTPUT
     SELECT  @response 
  Returns: >
    nothing
  **/
      EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT;
      SET @message = 'sp_OAMethod Open failed';
      IF @hr = 0 EXEC @hr = sp_OAMethod @obj, 'open', NULL, 'GET', @TheURL, false;
      SET @message = 'sp_OAMethod setRequestHeader failed';
      IF @hr = 0
        EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
          'application/x-www-form-urlencoded';
      SET @message = 'sp_OAMethod Send failed';
      IF @hr = 0 EXEC @hr = sp_OAMethod @obj, send, NULL, '';
      SET @message = 'sp_OAMethod read status failed';
      IF @hr = 0 EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT;
      IF @status <> 200 BEGIN
                          SELECT @message = 'sp_OAMethod http status ' + Str(@status), @hr = -1;
        END;
      SET @message = 'sp_OAMethod read response failed';
      IF @hr = 0
        BEGIN
          EXEC @hr = sp_OAGetProperty @obj, 'responseText', @Theresponse OUT;
          END;
      EXEC sp_OADestroy @obj;
      IF @hr <> 0 RAISERROR(@message, 16, 1);
      END;
  GO

Bunu kullanmak basittir. Bazı json test örenkleri ile test edebilirsiniz.

DECLARE @response NVARCHAR(MAX) 
  EXECUTE dbo.GetWebService 'http://headers.jsontest.com/', @response OUTPUT
  SELECT  @response

Sonuç?

{
     "X-Cloud-Trace-Context": "54e570f5620dc6ef3b087ac6042dca03/10421626717945848480",
     "Host": "headers.jsontest.com",
     "User-Agent": "Mozilla/4.0 (compatible; Win32; WinHttp.WinHttpRequest.5)",
     "Accept": "*/*",
     "Content-Type": "application/x-www-form-urlencoded"
  }

Şimdi gerçek bir hizmet alma imkanımız var, deneyebiliriz. Bu web servisi verileriniz yerine size mesaj gönderebilir, bu yüzden bunları kontrol edip kaydettiğinizden emin olun.

status": {
    "message": "the hourly limit of 2000 credits for demo has been exceeded. Please use an application specific account. Do not use the demo account for your application.",
    "value": 19
  }}

Sonraki grupta, bu tür bir mesajı yalnızca hata olarak gösteriyoruz.

Msg 50000, Level 16, State 1, Line 30
  The import failed ({"status": {
    "message": "the hourly limit of 2000 credits for demo has been exceeded. Please use an application specific account. Do not use the demo account for your application.",
    "value": 19
  }})

Bu durumda, demo modundan çok kendi hesabımı kullanmalıydım. URL’deki 'demo ’adı saatte birkaç kez çalışacak olsa da, takılıp kalıyorsanız, kendi kayıtlı adınız için bunu değiştirmeniz gerekecektir.

DECLARE @response NVARCHAR(4000);
  --get the data from the provider as JSON
  EXECUTE dbo.GetWebService 'http://api.geonames.org/citiesJSON?formatted=false&north=44.1&south=-9.9&east=-22.4&west=55.2&username=demo&style=full',
    @response OUTPUT;
  --now check to see if it is all there.
  IF EXISTS
    (
    SELECT * FROM dbo.JSONPathsAndValues(@response)
      WHERE Thepath IN
  ('$.geonames[0].lng', '$.geonames[0].geonameId', '$.geonames[0].countrycode', '$.geonames[0].name',
    '$.geonames[0].fclName', '$.geonames[0].toponymName', '$.geonames[0].fcodeName',
    '$.geonames[0].wikipedia', '$.geonames[0].lat', '$.geonames[0].fcl', '$.geonames[0].population',
    '$.geonames[0].fcode'
  )
    )
    BEGIN
      SELECT CountryCode, name, population, latitude, longitude, id, WikipediaURL
        FROM OpenJson(@response) --we have to walk to the level of the array that
  	  --we are interested in. OPENjson doesn't support accessing an array at 
  	  --a higher level when using the WITH clause.
          OUTER APPLY
        OpenJson(Value)
        WITH
          (CountryCode CHAR(2) '$.countrycode', Latitude NUMERIC(38, 15) '$.lat',
          Longitude NUMERIC(38, 15) '$.lng', Name VARCHAR(200) '$.name',
          Population BIGINT '$.population', wikipediaURL VARCHAR(200) '$.wikipedia',
          id INT '$.geonameId'
          );
    END;
  ELSE RAISERROR('The import failed (%s)', 16, 1, @response);

Tabii ki yapabileceğin daha çok şey var. Örneğin, verilerin tablodaki veri tiplerine uyduğunu kontrol edebilir ve ihtiyacınız olan diğer kısıtlama kontrollerini yapabilirsiniz.

Dso çıktısındakileri kullanarak, OpenJSON'un son parçasının jimnastikçiliğinden vazgeçebilirsin.JSONPathsAndValues

DECLARE @response NVARCHAR(4000);
  --get the data from the provider as JSON
  EXECUTE dbo.GetWebService 'http://api.geonames.org/citiesJSON?formatted=false&north=44.1&south=-9.9&east=-22.4&west=55.2&username=demo&style=full',
    @response OUTPUT;
  DECLARE @TheData  table (
    -- columns returned by the function
    element_id INT NOT NULL,
    Depth INT NOT NULL,
    Thepath NVARCHAR(2000),
    ValueType VARCHAR(10) NOT NULL,
    TheValue NVARCHAR(MAX) NOT NULL
    )
   INSERT INTO @TheData SELECT * FROM dbo.JSONPathsAndValues(@response)
   SELECT 
   Max(Convert(CHAR(2),CASE WHEN Thepath LIKE '%.countrycode' THEN Thevalue ELSE '' END)) AS countycode,
   Max(Convert(NVARCHAR(200),CASE WHEN Thepath LIKE '%.name' THEN Thevalue ELSE '' END)) AS name,
   Max(Convert(NUMERIC(38, 15),CASE WHEN Thepath LIKE '%.lat' THEN Thevalue ELSE '-90' END)) AS latitude,
   Max(Convert(NUMERIC(38, 15),CASE WHEN Thepath LIKE '%.lng' THEN Thevalue ELSE '-180' END)) AS longitude,
   Max(Convert(BigInt,CASE WHEN Thepath LIKE '%.population' THEN Thevalue ELSE '-1' END)) AS population,
   Max(Convert(VARCHAR(200),CASE WHEN Thepath LIKE '%.wikipedia' THEN Thevalue ELSE '' END)) AS wikipediaURL,
   Max(Convert (INT,CASE WHEN Thepath LIKE '%.geonameId' THEN Thevalue ELSE '0' END)) AS ID
   FROM @TheData GROUP BY Left(ThePath,CharIndex(']',ThePath+']'))

özet

Bazı JSON'ları SQL Server'a içe aktarmadan önce, her türlü kontrolü nasıl çalıştırabileceğinizi göstermeye karar verdim, örneğin JSON meta verilerinin beklediğiniz gibi olmasını sağlamak, istediğiniz bilgilerin tam yollarını bulmak veya değerler, veri türleri ve aralık içinde geçerlidir. JSON belgesinde ne olduğunu incelemek zorunda kalmadan çözmek kolaydır. Size bir web servisinden basit bir özet akışı göstererek, umarım istediğiniz kadar sağlam hale getirebileceğinizi göstermişimdir. Tabii ki yapabileceğiniz daha çok şey var, ama umarım size JSON formatında veri kabul etme konusunda kendinizden emin olmanız için yeterince şey göstermiştim.

Bu site kuruluş amacının özelliği gereği aslında bir nevi kendime online help desk oluşturmak ve bu deski de herkesle paylaşmak istediğimden bazı makaleler alıntı olabilmektedir. Anlayışınıza sığınarak orjinal emeğe saygımız da sonsuz olduğundan makale linkine tıklayarak ulaşabilirsiniz.

Bilgi paylaştıkça çoğalır ve insanlar güzelleşir..  :-) 

Sizde paylaşmayı unutmayın lütfen..

İyi çalışmalar.