SELECT XML FROM TABLE IN SQL SERVER

Question

I have XML stored in my table. This table has columns id type int, and value type XML. I'm using SQL Server 2012.

XML looks like this:

 <?xml version="1.0" encoding="utf-16"?>
 <Weather>
 <Forecast>
 <Description>sky is clear</Description>
 <Date>6.9.2013 23:50:36</Date>
 <MinTemp>13</MinTemp>
 <MaxTemp>13</MaxTemp>
 <Pressure>995</Pressure>
 <Humidity>68</Humidity>
 </Forecast>
 </Weather>

This XML can have up to then Forecast parts. How can I with simple SELECT statement get for instance Humidity value?

I have been trying several thins I found here, but I keep getting NULL so that's the reason I'm asking this question. Please help...

Check this similar question Select values from XML field in SQL Server 2008. It might be helpful. — Adam Porad
– Adam Porad, Commented Sep 9, 2013 at 19:04
If you share some of the queries tried, someone might be able to identify a mistake, etc. that causes your issue. — Adam Porad
– Adam Porad, Commented Sep 9, 2013 at 19:04

roman · Accepted Answer · 2013-09-09 19:29:16Z

2

If you have more than one Forecast in your xml, use nodes function:

select
    id, d.c.value('Humidity[1]', 'bigint') as Humidity
from test as t
    outer apply t.Data.nodes('Weather/Forecast') as d(c)

sql fiddle demo

answered Sep 9, 2013 at 19:29

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Edna Over a year ago

Thanks a lot! Wish I could accept two answers, but can't. So Vote up!

Community · Accepted Answer · 2017-05-23 10:31:54Z

1

Try like this:-

select
  columnname
from
  MyTable
where
  columnname.value('(/Weather/Forecast)[1]', 'varchar(max)') like 'StringToSearchFor'

or as suggested in this link like this:-

SELECT 
[xmlField].value('(/Weather//Forecast/Description/node())[1]', 'nvarchar(max)') as columname
FROM [myTable]

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Sep 9, 2013 at 19:02

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

3 Comments

Edna Over a year ago

Thanks, really helped a lot! I was so close but couldn't find mistake. THANKS!

Edna Over a year ago

Can you please tell me what to do if I have XML that has 10 Forecast parts? I started using SQL few days ago so everything is new for me...

Rahul Tripathi Over a year ago

You have to specify nodes for all the 10 forecast parts!!

Collectives™ on Stack Overflow

SELECT XML FROM TABLE IN SQL SERVER

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related