Import Weather Web-service directly into SQL Server

Question

Post reply

Import Weather Web-service directly into SQL Server

netguykb

Ten Centuries

Points: 1310
More actions
June 8, 2020 at 6:54 pm

#3761429

Folks, I am looking to try and come up with a way to INSERT the Output from this URL directly into a SQL Server Table. I would like to see if it's possible to do this directly from the SQL server as a StoredProcedure or TSQL.
https://www.aviationweather.gov/adds/dataserver_current/httpparam?dataSource=metars&requestType=retrieve&format=xml&hoursBeforeNow=3&mostRecent=true&stationString=KCLT

May 15, 2023 at 3:02 pm

This was removed by the editor as SPAM
May 15, 2023 at 3:56 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Can you show us the schema of the table you wish to INSERT to?

Do you wish to insert all of the output into a single XML column? Or are you looking to shred the XML on the way to the table or tables?

Presumably you are not looking to use the proc to access the URL itself.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

netguykb Ten Centuries Points: 1310 More actions · Answer 2

The schema would be based on the resulting data found in the <METAR></METAR> Tag. Sky_Condition gets a little tricky as the results could be one record or many records, so for these tags, we may want to lump them together. for example FEW-3900, SCT-13000, BKN-25000

The XML should be parsed out into separate columns in one table.

Ultimately I would love to use an SP for accessing the URL if possible. IF not then I need a tool to extract this data into the DB, Maybe using SSIS, Excel, or some automated way. Ideally, the table needs to be refreshed every 30 minutes. The purpose to record this data for historical analysis.

IF OBJECT_ID('dbo.AWC_METARS', 'U') IS NOT NULLDROP TABLE dbo.AWC_METARS;GO

CREATE TABLE dbo.AWC_METARS (ID int IDENTITY(1,1) PRIMARY KEY,,raw_text nvarchar(max) NULL DEFAULT (NULL),station_id nvarchar(10) NULL DEFAULT (NULL),observation_time nvarchar(max) NULL DEFAULT (NULL),latitude nvarchar(15) NULL DEFAULT (NULL),longitude nvarchar(15) NULL DEFAULT (NULL),temp_c nvarchar(10) NULL DEFAULT (NULL),dewpoint_c nvarchar(10) NULL DEFAULT (NULL),wind_dir_degrees nvarchar(10) NULL DEFAULT (NULL),wind_speed_kt nvarchar(10) NULL DEFAULT (NULL),visibility_statute_mi nvarchar(10) NULL DEFAULT (NULL),altim_in_hg nvarchar(10) NULL DEFAULT (NULL),sky_condition nvarchar(max) NULL DEFAULT (NULL),flight_category nvarchar(10) NULL DEFAULT (NULL),elevation_m nvarchar(10) NULL DEFAULT (NULL));
GO

This reply was modified 4 years, 5 months ago by netguykb.
This reply was modified 4 years, 5 months ago by netguykb.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Here is some sample code to get you started.

DROP TABLE IF EXISTS #AviationWeather;

CREATE TABLE #AviationWeather
(
    XMLDoc XML NOT NULL
);

INSERT #AviationWeather
(
    XMLDoc
)
VALUES
('<response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XML-Schema-instance" version="1.2" xsi:noNamespaceSchemaLocation="http://aviationweather.gov/adds/schema/metar1_2.xsd">
<request_index>93455468</request_index>
<data_source name="metars"/>
<request type="retrieve"/>
<errors/>
<warnings/>
<time_taken_ms>8</time_taken_ms>
<data num_results="1">
<METAR>
<raw_text>KCLT 082052Z 25004KT 10SM FEW044 BKN250 30/20 A2998 RMK AO2 SLP141 TCU DSNT NE T03000200 56017</raw_text>
<station_id>KCLT</station_id>
<observation_time>2020-06-08T20:52:00Z</observation_time>
<latitude>35.22</latitude>
<longitude>-80.95</longitude>
<temp_c>30.0</temp_c>
<dewpoint_c>20.0</dewpoint_c>
<wind_dir_degrees>250</wind_dir_degrees>
<wind_speed_kt>4</wind_speed_kt>
<visibility_statute_mi>10.0</visibility_statute_mi>
<altim_in_hg>29.97933</altim_in_hg>
<sea_level_pressure_mb>1014.1</sea_level_pressure_mb>
<quality_control_flags>
<auto_station>TRUE</auto_station>
</quality_control_flags>
<sky_condition sky_cover="FEW" cloud_base_ft_agl="4400"/>
<sky_condition sky_cover="BKN" cloud_base_ft_agl="25000"/>
<flight_category>VFR</flight_category>
<three_hr_pressure_tendency_mb>-1.7</three_hr_pressure_tendency_mb>
<metar_type>METAR</metar_type>
<elevation_m>220.0</elevation_m>
</METAR>
</data>
</response>');

SELECT raw_text = p.n1.value('(*:raw_text)[1]', 'nvarchar(max)')
      ,station_id = p.n1.value('(*:station_id)[1]', 'nvarchar(10)')
FROM #AviationWeather aw
    CROSS APPLY aw.XMLDoc.nodes('//*:response/*:data/*:METAR') p(n1);

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin