Google Sheets IMPORTXML - Dealing with null values

user_4231902 0
Viewed 67

I am trying to pull data from https://forecast.weather.gov/MapClick.php?lat=33.4456&lon=-112.0674&FcstType=digitalDWML

enter image description here

I need the coverage attribute under weather-conditions, but also need to know if the weather-condition is null. Each "weather-conditions" listed is an hourly value, so a null value is "no chance."

The following xpath gets me the coverage attribute:

//weather-conditions/value[contains(@weather-type,'rain')]/@coverage

I had thought the following xpath would get me the null values as "true" but I'm not having luck:

//weather-conditions/@xsi:nil

Final code I have (not working):

=IMPORTXML("https://forecast.weather.gov/MapClick.php?lat=33.4456&lon=-112.0674&FcstType=digitalDWML", "//weather-conditions/value[contains(@weather-type,'rain')]/@coverage | //weather-conditions/@xsi:nil")
google-sheets-formula xsi google-sheets xpath xml
3 Answers

In your situation, how about the following XPath?

Sample xpath:

//*[local-name()='weather-conditions']/*[local-name()='value'][@weather-type='rain']/@coverage | //*[local-name()='weather-conditions']/@*[name()='xsi:nil']

Sample formula:

=IMPORTXML(A1,"//*[local-name()='weather-conditions']/*[local-name()='value'][@weather-type='rain']/@coverage | //*[local-name()='weather-conditions']/@*[name()='xsi:nil']")
  • In this case, the cell "A1" has the URL of https://forecast.weather.gov/MapClick.php?lat=33.4456&lon=-112.0674&FcstType=digitalDWML.

Testing:

When this sample formula is used, the following result is obtained.

enter image description here

Note:

  • For example, if you want to convert TRUE to empty, I thought that a formula of =ARRAYFORMULA(IF(A2:A=TRUE,,A2:A)) might be able to be used.

  • This path is for your provided URL. So, when you change the URL this XPath might not be able to be used. Please be careful about this.

user_71086530

This is the best result I could get:

enter image description here

I used this formula:

=IMPORTXML("https://forecast.weather.gov/MapClick.php?lat=33.4456&lon=-112.0674&FcstType=digitalDWML", "//weather-conditions/value[contains(@weather-type,'rain')]/@coverage  | //data/parameters/weather/weather-conditions")

However, I can't certainly say if this is pulling exactly what you need since I do not know the exact amount of "nulls" we should expect in the results.

Maybe it can be a starting point to achieve your final goal.

user_192011680

Try

={IMPORTXML(A1,"//*[local-name()='weather-conditions']/*[local-name()='value']/@weather-type"),
 IMPORTXML(A1,"//*[local-name()='weather-conditions']/*[local-name()='value']/@coverage")}

this site uses namespace, so you have to use local-name() to retrieve the informations

enter image description here

enter image description here

user_150193800
Related