Extract weather information from google's API in SQL

xml_query.sql

SELECT 
  city_name, 
  created_at,
  CAST(array_to_string(xpath('/xml_api_reply/weather/current_conditions/temp_c/@data', xml_response), '') AS integer) AS temperature,
  substring(array_to_string(xpath('/xml_api_reply/weather/current_conditions/wind_condition/@data', xml_response), '') FROM '% #"[NSOE]+#" %' FOR '#') AS wind_direction,
  CAST(substring(array_to_string(xpath('/xml_api_reply/weather/current_conditions/wind_condition/@data', xml_response), '') FROM '% #"[0-9]+#"%' FOR '#') AS integer) AS wind_speed,
  CAST(substring(array_to_string(xpath('/xml_api_reply/weather/current_conditions/humidity/@data', xml_response), '') FROM '% #"[0-9]+#"%' FOR '#') AS integer) AS humidity,
  array_to_string(xpath('/xml_api_reply/weather/current_conditions/icon/@data', xml_response), '') AS condition
FROM 
  probe_xml 
  ;