Logparser SQL and HTTP/SOAP: redux

I originally posted this but after nobody even bothered to read it, it was deleted. The assumption was: (1) there were subscribers to this forum who processed data from the web and therefore would be interested in a wsdl interface (2) there were an equal amount who might be interested in the free logparser tool for data transformation via VBA. Aaron:friends: suggested a second try so here goes.

Caveat: do not read any further if you ae not interested in processing XML data from webservices.

1. navigate to this site: http://ws.cdyne.com/WeatherWS/Weather.asmx?wsdl

it should return an XML specification (weather forecasts) via HTTP/SOAP. I'll leave it up to y'all to Google WSDL and SOAP as this is not a tutorial.

2. One of the methods defined by the wsdl is GetCityForecastByZIP. This will return a 7 day weather forecast for a specific zipcode. For example (if I enter 27609, a zipcode in Raleigh NC I would get back:

[VBA]
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<GetCityForecastByZIPResponse xmlns="http://ws.cdyne.com/WeatherWS/">
<GetCityForecastByZIPResult>
<Success>
true</Success>
<ResponseText>
City Found</ResponseText>
<State>
NC</State>
<City>
Raleigh</City>
<WeatherStationCity>
Bunn</WeatherStationCity>
<ForecastResult>
<Forecast>
<Date>
2009-05-30T00:00:00</Date>
<WeatherID>
4</WeatherID>
<Desciption>
Sunny</Desciption>
<Temperatures>
<MorningLow />
<DaytimeHigh>
71</DaytimeHigh>
</Temperatures>
<ProbabilityOfPrecipiation>
<Nighttime />
<Daytime>
10</Daytime>
</ProbabilityOfPrecipiation>
</Forecast>
<Forecast>
<Date>
2009-05-31T00:00:00</Date>
<WeatherID>
2</WeatherID>
<Desciption>
Partly Cloudy</Desciption>
<Temperatures>
<MorningLow>
57</MorningLow>
<DaytimeHigh>
74</DaytimeHigh>
</Temperatures>
<ProbabilityOfPrecipiation>
<Nighttime>
30</Nighttime>
<Daytime>
20</Daytime>
</ProbabilityOfPrecipiation>
</Forecast>
<Forecast>
<Date>
2009-06-01T00:00:00</Date>
<WeatherID>
4</WeatherID>
<Desciption>
Sunny</Desciption>
<Temperatures>
<MorningLow>
51</MorningLow>
<DaytimeHigh>
80</DaytimeHigh>
</Temperatures>
<ProbabilityOfPrecipiation>
<Nighttime>
10</Nighttime>
<Daytime>
10</Daytime>
</ProbabilityOfPrecipiation>
</Forecast>
<Forecast>
<Date>
2009-06-02T00:00:00</Date>
<WeatherID>
2</WeatherID>
<Desciption>
Partly Cloudy</Desciption>
<Temperatures>
<MorningLow>
57</MorningLow>
<DaytimeHigh>
83</DaytimeHigh>
</Temperatures>
<ProbabilityOfPrecipiation>
<Nighttime>
10</Nighttime>
<Daytime>
10</Daytime>
</ProbabilityOfPrecipiation>
</Forecast>
<Forecast>
<Date>
2009-06-03T00:00:00</Date>
<WeatherID>
2</WeatherID>
<Desciption>
Partly Cloudy</Desciption>
<Temperatures>
<MorningLow>
62</MorningLow>
<DaytimeHigh>
75</DaytimeHigh>
</Temperatures>
<ProbabilityOfPrecipiation>
<Nighttime>
10</Nighttime>
<Daytime>
40</Daytime>
</ProbabilityOfPrecipiation>
</Forecast>
<Forecast>
<Date>
2009-06-04T00:00:00</Date>
<WeatherID>
3</WeatherID>
<Desciption>
Mostly Cloudy</Desciption>
<Temperatures>
<MorningLow>
57</MorningLow>
<DaytimeHigh>
70</DaytimeHigh>
</Temperatures>
<ProbabilityOfPrecipiation>
<Nighttime>
40</Nighttime>
<Daytime>
40</Daytime>
</ProbabilityOfPrecipiation>
</Forecast>
<Forecast>
<Date>
2009-06-05T00:00:00</Date>
<WeatherID>
3</WeatherID>
<Desciption>
Mostly Cloudy</Desciption>
<Temperatures>
<MorningLow>
56</MorningLow>
<DaytimeHigh>
68</DaytimeHigh>
</Temperatures>
<ProbabilityOfPrecipiation>
<Nighttime>
40</Nighttime>
<Daytime>
40</Daytime>
</ProbabilityOfPrecipiation>
</Forecast>
</ForecastResult>
</GetCityForecastByZIPResult>
</GetCityForecastByZIPResponse>
</soap:Body>
</soap:Envelope>
[/VBA]

3. I use this as an example because the webservice is free. However, Paypal, IBM and others offer webservice access, so this thread is not about a trivial exercise.

4. The Initial Question: How do I get this response in the first place?

5. 2-ways: The first is to construct what is known as a SOAP 'envelope' and send it to the wsdl website (above) then process the ResponseText. The second is to use the SOAP ActiveX which will return the results as an XML nodelist.

6. In either case, processing the results will involve an XML DOM Parser:dunno .

7. So... I want the results into my database... and I do what??:banghead:

8. Glad I asked:clap: , this is where Logparser comes in... as it has a native ability to process XML input to either csv or COM output w.out having to write lines of code for recursion or XPATH queries... why not let it.

Curious? To be honest, my job now depends on knowing how to deal with webservices, so I had to hustle and figure a lot of this out for myself. The interesting thing about the wsdl I referenced above... it is a 1 to many relationship, so for each of the 7 day forecast one has to reference the city,state... especially if the webservice calls are to multiple zipcodes... and this makes parsing a little more sensitive. So when I discovered Logparser had a neat way of handling this I was estatic.

If there is any interest I can break down steps 1-8. Stan