CODEWITHSUNDEEP
sql-serverxmlt-sql
Stefto
Stefto

Reputation: 149

parsing xml with T-SQL.... what am i doing wrong

I'm trying to parse xml infomation i get from a webservice to later put in a table. i get no errors only a empty field, so i'm probably overlooking something small, can anyone push me in the right direction?

Declare @myXml as xml;

set @myXml = '<?xml version="1.0" encoding="UTF-8"?>
<string xmlns="http://www.webserviceX.NET">
   <CurrentWeather>
      <Location>Eindhoven, Netherlands (EHEH) 51-27N 005-25E 28M</Location>
      <Time>Jun 15, 2016 - 06:55 AM EDT / 2016.06.15 1055 UTC</Time>
      <Wind>from the WSW (240 degrees) at 2 MPH (2 KT):0</Wind>
      <Visibility>greater than 7 mile(s):0</Visibility>
      <SkyConditions>mostly cloudy</SkyConditions>
      <Temperature>62 F (17 C)</Temperature>
      <DewPoint>57 F (14 C)</DewPoint>
      <RelativeHumidity>82%</RelativeHumidity>
      <Pressure>29.50 in. Hg (0999 hPa)</Pressure>
      <Status>Success</Status>
   </CurrentWeather>
</string>';

 SELECT 
   b.value('(./CurrentWeather/Location/text())[1]','Varchar(250)') as [Location] 
FROM @myXml.nodes('/string') as a(b);

Upvotes: 1

Views: 53

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

T-SQL requires you to specify namespaces when any present:

with xmlnamespaces(default 'http://www.webserviceX.NET')
select b.value('(./CurrentWeather/Location/text())[1]','Varchar(250)') as [Location]
FROM @myXml.nodes('/string') as a(b);

Upvotes: 3

Related Questions