OPENJSON | SQL Server Portal

Posts Tagged ‘OPENJSON’

SQL Server – How to check weather of any location using API

Posted in Solutions, SQL SERVER, Uncategorized, tagged OPENJSON, sp_OACreate, sp_OAMethod, SQL Server, WeatherApi on April 7, 2023| Leave a Comment »

WeatherAPI provides an API to get Weather condition of any location. In this article, we will use this API in SQL Server in order to get weather conditions of any location.

Let me explain it step by step.

Step 1:

Please Sign Up in WeatherAPI and create an account as shown below.

Step 2:

After Sign Up, login to the account, created in Step 1, it will direct to the screen where you can find your API key as shown below, please make a note of this Key.

Step 3:

You need to run given below script to enable OLE Automation Procedures. This is a prerequisite for next step.

 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Step 4:

Let’s create given below stored procedure which takes location as an input and provides weather condition of that location.

 
USE tempdb
GO
 
CREATE OR ALTER PROCEDURE sp_Weather_Details
@Location NVARCHAR(250)

AS
DECLARE @URL NVARCHAR(250) = 'https://api.weatherapi.com/v1/current.json';
DECLARE @key VARCHAR(250) ='xxxxxxxxxxxxxxxxxxxxxxxxx'; --Enter your own API Key generated in Step 2
DECLARE @ConsolidatedURL NVARCHAR(250) = CONCAT(@URL,'?key=',@Key,'&q=',@location,'&aqi=no'); 
DECLARE @Object INT;
DECLARE @Json TABLE(DATA NVARCHAR(MAX));
DECLARE @ResponseText NVARCHAR(MAX);
 
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'OPEN', NULL, 'GET', @ConsolidatedURL, 'FALSE';
EXEC sp_OAMethod @Object, 'SEND';
EXEC sp_OAMethod @Object, 'RESPONSETEXT', @ResponseText OUTPUT , @Json OUTPUT;
 
INSERT INTO @Json exec sp_OAGetProperty @Object, 'RESPONSETEXT';
 
DECLARE @Data NVARCHAR(MAX) = (SELECT DATA FROM @Json);
SELECT * FROM OPENJSON(@Data)
WITH
( 
  [Country] NVARCHAR(500)  '$.location.country',
  [Region] NVARCHAR(500) '$.location.region',
  [City] NVARCHAR(500)  '$.location.name',
  [Current Temprature] NUMERIC(18,1) '$.current.temp_c',
  [Humidity] NUMERIC(18,1) '$.current.humidity',
  [Weather Condition] NVARCHAR(500) '$.current.condition.text',
  [Wind Speed] NUMERIC(18,1) '$.current.wind_kph'
);
GO

Let’s run few examples and see the weather conditions.

Example 1:

Location: Karachi, Pakistan

 

USE tempdb
GO

EXEC sp_Weather_Details 'Karachi';
GO

Example 2:

Location: Dubai, UAE

 

USE tempdb
GO

EXEC sp_Weather_Details 'Dubai';
GO

Example 3:

Location: London, UK

 

USE tempdb
GO

EXEC sp_Weather_Details 'London';
GO

Example 4:

Location: New York, USA

 

USE tempdb
GO

EXEC sp_Weather_Details 'New York';
GO

Conclusion:

I found this API very useful whenever I need to find the weather of any location. Do let me know your feedback.

Read Full Post »

SQL Server – How to translate text from one language to another using google translate

Posted in SQL SERVER, Solutions, tagged SQL Server, google translate, stored procedure, API, Translate text, sp_OACreate, sp_OAMethod, OPENJSON on April 5, 2023| Leave a Comment »

Google translate provides API to translate text from one language to another. In this article, we will use this API in SQL Server and translate some text. Please sign up in google translate and create an account which provides you the Key.
Please use this Key in the given below stored procedure.

Before creating the stored procedure, you need to run given below script to enable Ole Automation Procedures. This is a prerequisite for the given below solution.

Enable OLE Automation procedures:

 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Solution:

 

USE tempdb
GO

CREATE OR ALTER PROCEDURE sp_translate_text
@TextToBeTranslated NVARCHAR(500),
@SourceLanguage NVARCHAR(50),
@TargetLanguage NVARCHAR(50),
@Key NVARCHAR(250) = 'Please use Translate API key provided by google'  

AS

DECLARE @URL NVARCHAR(250) = 'https://translation.googleapis.com/language/translate/v2';
DECLARE @ConsolidatedURL NVARCHAR(250) = CONCAT(@URL,'?key=',@Key); 
DECLARE @Object INT;
DECLARE @Json TABLE(DATA NVARCHAR(MAX));
DECLARE @ResponseText NVARCHAR(MAX);
DECLARE @Body NVARCHAR(MAX)  = CONCAT('{','"q":','"',@TextToBeTranslated,'"',',','"target":','"',@TargetLanguage,'"',',','"source":','"',@SourceLanguage,'"','}');

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'OPEN', NULL, 'POST', @ConsolidatedURL, 'FALSE';
EXEC sp_OAMethod @Object, 'SETREQUESTHEADER', NULL, 'CONTENT-TYPE', 'APPLICATION/JSON';
EXEC sp_OAMethod @Object, 'SEND', NULL, @Body;

EXEC sp_OAMethod @Object, 'RESPONSETEXT', @ResponseText OUTPUT , @Json OUTPUT;
INSERT INTO @Json exec sp_OAGetProperty @Object, 'RESPONSETEXT';

DECLARE @Data NVARCHAR(MAX) = (SELECT DATA FROM @Json);

SELECT @SourceLanguage AS [Source Language]
	 , @TargetLanguage AS [Target Language]
	 , TranslatedText  AS [Translated Text]
FROM OPENJSON(@Data,'$.data.translations')
WITH
(
 [TranslatedText] NVARCHAR(500) '$.translatedText'  
);
GO

Example 1:

Text to be translated: “How are you”

Source Language: English

Target Language: Urdu

 

USE tempdb
GO

EXEC sp_translate_text N'how are you','en','ur';
GO

Example 2:

Text to be translated: “آپ کیسے ہو”

Source Language: Urdu

Target Language: English

 

USE tempdb
GO

EXEC sp_translate_text N'آپ کیسے ہو','ur','en';
GO

Example 3:

Text to be translated: “How are you”

Source Language: English

Target Language: Arabic

 

USE tempdb
GO

EXEC sp_translate_text N'how are you','en','ar';
GO

Example 4:

Text to be translated: “كيف حالكم”

Source Language: Arabic

Target Language: English

 

USE tempdb
GO

EXEC sp_translate_text N'كيف حالكم','ar','en';
GO

Example 5:

Text to be translated: “How are you”

Source Language: English

Target Language: Hindi

 

USE tempdb
GO

EXEC sp_translate_text N'how are you','en','hi';
GO

Example 6:

Text to be translated: “आप कैसे हैं”

Source Language: Hindi

Target Language: English

 

USE tempdb
GO

EXEC sp_translate_text N'आप कैसे हैं','hi','en';
GO

Conclusion:

I found Google translate API very useful whenever I need to convert any text from one language to another. It works very smoothly. Do let me know if you use it and how did you find it.

Read Full Post »