I know I'm a little late in the conversation, but in case anyone stumbles on it and finds this helpful . . .

First of all, thanks for the article. It was very helpful. It very much simplifies the task I was preparing for pulling latitude/longitude data. I ended up reworking your code into a function (for ease of multiple record updates without cursors), and chose to use Yahoo's Placefinder API instead of Google's API, for the reasons already mentioned by others in this thread. I also was having an issue with several records coming back with NULL values from google, even though I KNEW the urls were valid, as was the XML. The problem I was encountering had to do with the number of bytes returned in the XML (>4000). In these cases, the sp_OAGetProperty proc is not able to handle results >4000 bytes. This appears to be a hard limit, and no workaround or fix is available, at least for SQL 2008. The Yahoo XML file that's returned contains much less data than the Google API, and therefore processes all of my addresses just fine.

The function code is attached. Note that you'll have to get a key from Yahoo (http://developer.yahoo.com/geo/placefinder/). Takes about 30 seconds to apply.

Sample query using the function applied to an address table:

select *

from addr as a

cross apply fnGeocode(a.addr,a.city, a.state, a.country,a.zip,null, null, null, null) as b --passes arguments to geocode function