Re: weather and xml

But you are comparing Apples to Cabbages Birgitta.

I can also make the XML-INTO much simpler if I:
a) Use a much simpler XML structure (the depth on this one appears to be only about 50% of the OPs example) and
b) Restrict the range of values I retrieve by using a path option (which is what you have effectively done)

That said - I'm glad that someone can make sense of the SQL stuff because it confuses the heck out of me and the documentation is (to be charitable) bloody awful.


Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Dec 5, 2017, at 12:08 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

Here an example that IMHO will prove SQL is NOT more complex than RPG.

I just executed the following request:
api.wunderground.com/api/MYKEY/conditions/q/CA/MyCity.xml
-->
Values(Systools.httpgetClob('http://api.wunderground.com/api/MYKEY/condition
s/q/CA/Kaufering.xml', ''))

Which returned the following XML-Answer
<response>
<version>0.1</version>
<termsofService>http://www.wunderground.com/weather/api/d/terms.html
</termsofService>
<features>
<feature>conditions</feature>
</features>
<current_observation>
<image>

<url>http://icons.wxug.com/graphics/wu2/logo_130x80.png</url>
<title>Weather Underground</title>
<link>http://www.wunderground.com</link>
</image>
<display_location>
<full>Kaufering, Germany</full>
<city>Kaufering</city>
<state>OB</state>
<state_name>Germany</state_name>
<country>DL</country>
<country_iso3166>DE</country_iso3166>
<zip>00000</zip>
<magic>105</magic>
<wmo>10857</wmo>
<latitude>48.11000061</latitude>
<longitude>10.86999989</longitude>
<elevation>600.2</elevation>
</display_location>
<observation_location>
<full>Allgäustraße, Kaufering, </full>
<city>Allgäustraße, Kaufering</city>
<state></state>
<country>DE</country>
<country_iso3166>DE</country_iso3166>
<latitude>48.085468</latitude>
<longitude>10.861280</longitude>
<elevation>1925 ft</elevation>
</observation_location>
<estimated>
</estimated>
<station_id>IKAUFERI2</station_id>
<observation_time>Last Updated on December 5, 5:38 PM CET
</observation_time>
<observation_time_rfc822>Tue, 05 Dec 2017 17:38:56 +0100
</observation_time_rfc822>
<observation_epoch>1512491936</observation_epoch>
<local_time_rfc822>Tue, 05 Dec 2017 17:42:01
+0100</local_time_rfc822>
<local_epoch>1512492121</local_epoch>
<local_tz_short>CET</local_tz_short>
<local_tz_long>Europe/Berlin</local_tz_long>
<local_tz_offset>+0100</local_tz_offset>
<weather>Mostly Cloudy</weather>
<temperature_string>36.3 F (2.4 C)</temperature_string>
<temp_f>36.3</temp_f>
<temp_c>2.4</temp_c>
<relative_humidity>86%</relative_humidity>
<wind_string>From the SE at 6.2 MPH Gusting to 11.2
MPH</wind_string>
<wind_dir>SE</wind_dir>
<wind_degrees>145</wind_degrees>
<wind_mph>6.2</wind_mph>
<wind_gust_mph>11.2</wind_gust_mph>
<wind_kph>10.0</wind_kph>
<wind_gust_kph>18.0</wind_gust_kph>
<pressure_mb>1035</pressure_mb>
<pressure_in>30.57</pressure_in>
<pressure_trend>0</pressure_trend>
<dewpoint_string>32 F (0 C)</dewpoint_string>
<dewpoint_f>32</dewpoint_f>
<dewpoint_c>0</dewpoint_c>
<heat_index_string>NA</heat_index_string>
<heat_index_f>NA</heat_index_f>
<heat_index_c>NA</heat_index_c>
<windchill_string>31 F (0 C)</windchill_string>
<windchill_f>31</windchill_f>
<windchill_c>0</windchill_c>
<feelslike_string>31 F (0 C)</feelslike_string>
<feelslike_f>31</feelslike_f>
<feelslike_c>0</feelslike_c>
<visibility_mi>6.2</visibility_mi>
<visibility_km>10.0</visibility_km>
<solarradiation></solarradiation>
<UV>0</UV>
<precip_1hr_string>-999.00 in ( 0 mm)</precip_1hr_string>
<precip_1hr_in>-999.00</precip_1hr_in>
<precip_1hr_metric> 0</precip_1hr_metric>
<precip_today_string>0.01 in (0 mm)</precip_today_string>
<precip_today_in>0.01</precip_today_in>
<precip_today_metric>0</precip_today_metric>
<icon>mostlycloudy</icon>

<icon_url>http://icons.wxug.com/i/c/k/nt_mostlycloudy.gif</icon_url>

<forecast_url>http://www.wunderground.com/global/stations/10857.html</foreca
st_url>

<history_url>http://www.wunderground.com/weatherstation/WXDailyHistory.asp?I
D=IKAUFERI2</history_url>

<ob_url>http://www.wunderground.com/cgi-bin/findweather/getForecast?query=48
.085468,10.861280</ob_url>
</current_observation>
</response>

Now I embedded it in an SELECT-Statement and shedded the xml-document with
XMLTABLE (and only returned the following information: City, latitude,
longitude, weather, temperature in Fahrenheit, feels like in Fahrenheit and
relative humidity)

Here is the SELECT-STATEMEMT:
Select *
from XMLTABLE('$D/response/current_observation'
passing xmlparse(Document
httpgetClob('http://api.wunderground.com/api/MYKEY/conditions/q/CA/Kaufering
.xml', '')) as d
Columns city VarChar(50) Path
'display_location/city',
latidude Dec(15, 5) Path
'display_location/latitude',
longitude Dec(15, 5) Path
'display_location/longitude',
weather VarChar(50) Path 'weather',
temp_fahrenheit Dec(7, 2) Path 'temp_f',
feelslike_f Dec(7, 2) Path 'feelslike_f',
rel_humidity VarChar(20) path
'relative_humidity') x


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Smith,
Mike
Sent: Dienstag, 5. Dezember 2017 14:05
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxx>
Subject: RE: weather and xml

First, thanks to everyone for the imput. I did shrink the size to a more
manageable size and have the program compiling. I can read part of the xml
now, but I think I left out one field that I need to break down, so I'm
working on that this morning.

As far as the XMLTABLE, I actually started with that, but I had only come
across one example and couldn't quite figure out what I needed to do.

This is what I'm using to retrieve the xml file
http://api.wunderground.com/api/my_account_info/forecast/q/VA/Roanoke.xml



-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon
Hamberg
Sent: Monday, December 04, 2017 7:51 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: weather and xml

Hi Jon

I'd like to see an example of the XML that is coming from the web service -
I found that I can pull whatever I need for writing to a given table quite
easily with XMLTABLE, using, among other things, a kind of JOIN to get
detail from some header rows.

Maybe the OP could post an example on the MIDRANGE code repository.

Cheers
Vern

On 12/4/2017 6:43 PM, Jon Paris wrote:

Now code an example that shows the SQL processing for the OP's XML - the

SQL is OK for simple(fish) stuff but I found it becomes a nightmare very
quickly.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Dec 4, 2017, at 3:56 PM, Bryan Dietz <bdietz400@xxxxxxxxx> wrote:

Not to dissuade you from using RPG to decompose XML, why not just use

SQL.

I use this example:

SELECT *
FROM Xmltable('$result/rss/channel/item' Passing
XMLPARSE(Document
Systools.Httpgetblob('http://www.redbooks.ibm.com/rss/power.xml',''))
AS "result"
Columns
Title VARCHAR(128) PATH 'title'
,Description VARCHAR(1024) PATH 'description'
,LINK VARCHAR(255) PATH 'link'
,Pubdate VARCHAR(20) PATH 'substring(pubDate, 1, 16)')
AS RESULT ;


Bryan




Smith, Mike wrote on 12/4/2017 3:06 PM:

I'm working on a program that retrieve weather 'FORECAST' from
WUNDERGROUND.COM I think I have finally worked out the layout of the
datastructures, but now I'm getting
RNF0376
Total length 46856810 of data item SFORECA... exceeds
16,773,104 bytes.
The XML file is really not all that big. I have a significally larger

XML that I am using for another process with no issue. My weather xml file
is 10343 bytes my other xml file is 10210510 bytes

I'm not really sure how to go about resolving this issue.
Below is the declarations.
dcl-ds response Qualified;
version char(5);
termsofservice char(55);
features LikeDS(features_T);
forecast LikeDS(forecast_T);
end-ds response ;
dcl-ds features_T Template Qualified;
feature char(50);
end-ds;
dcl-ds forecast_T Template Qualified ;
txt_forecast LikeDS(txt_forecast_T);
simpleforecast LikeDS(simpleforecast_T);
end-ds;
dcl-ds txt_forecast_T Template Qualified;
date char(50);
forecastdays LikeDS(forecastdays_T);
end-ds;
dcl-ds forecastdays_T Template Qualified;
countforecast int(10);
forecastday Dim(32767) LikeDS(forecastday_T);
end-ds ;
dcl-ds forecastday_T Template Qualified;
period char(5);
icon char(5);
icon_url char(50);
title char(50);
fcttext char(50);
fcttext_metric LikeDS(fcttext_metric_T);
pop char(5);
end-ds ;
dcl-ds fcttext_metric_T Template Qualified;
#cdata_section char(50);
end-ds ;
dcl-ds simpleforecast_T Template Qualified;
forecastdays LikeDS(Sforecastdays_T);
end-ds;
dcl-ds Sforecastdays_T Template Qualified;
countSforecast int(10);
Sforecastday Dim(32767) LikeDS(Sforecastday_T);
end-ds ;
dcl-ds Sforecastday_T Template Qualified;
date char(50);
period char(5);
shigh LikeDS(high_T) ;
slow LikeDS(low_T) ;
conditions char(50);
icon char(5);
icon_url char(50);
skyicon char(50);
pop char(5);
gpf_allday LikeDS(gpf_allday_T);
gpf_day LikeDS(gpf_day_T) ;
gpf_night LikeDS(gpf_night_T) ;
snow_allday LikeDS(snow_allday_T) ;
snow_day LikeDS(snow_day_T) ;
snow_night LikeDS(snow_night_T) ;
maxwind LikeDS(maxwind_T) ;
avewind LikeDS(avewind_T) ;
avehumidity char(5);
maxhumidity char(5);
minhumidity char(5);
end-ds ;
dcl-ds high_T Template Qualified;
fahrenheit char(50);
celsius char(50);
end-ds ;
dcl-ds low_T Template Qualified;
fahrenheit char(50);
celsius char(50);
end-ds ;
dcl-ds gpf_allday_T Template Qualified;
ins char(50);
outs char(50);
end-ds ;
dcl-ds gpf_day_T Template Qualified;
ins char(50);
outs char(50);
end-ds ;
dcl-ds gpf_night_T Template Qualified;
ins char(50);
outs char(50);
end-ds ;
dcl-ds snow_allday_T Template Qualified;
ins char(50);
outs char(50);
end-ds ;
dcl-ds snow_day_T Template Qualified;
ins char(50);
outs char(50);
end-ds ;
dcl-ds snow_night_T Template Qualified;
ins char(50);
outs char(50);
end-ds ;
dcl-ds maxwind_T Template Qualified;
mph char(50);
kph char(50);
dir char(50);
degrees char(50);
end-ds ;
dcl-ds avewind_T Template Qualified;
mph char(50);
kph char(50);
dir char(50);
degrees char(50);
end-ds ;
NOTICE: This message, including any attachment, is intended as a

confidential and privileged communication. If you have received this message
in error, or are not the named recipient(s), please immediately notify the
sender and delete this message.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our
affiliate link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD

NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this message
in error, or are not the named recipient(s), please immediately notify the
sender and delete this message.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD