Search code examples
xmlgoogle-sheetsxpathgoogle-sheets-formulaxsi

Google Sheets IMPORTXML - Dealing with null values

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")
Solution

  • 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.