在SQL SERVER中选择XML FROM FROM TABLE

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存储在我的表中。该表的列id类型为int,值类型为XML。我正在使用SQL Server 2012。

XML looks like this:

XML看起来像这样:

 <?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?

此XML最多可包含预测部分。如何使用简单的SELECT语句获取湿度值?

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

我一直在尝试我在这里找到的几个,但我一直都是NULL,所以这就是我问这个问题的原因。请帮忙...

2 个解决方案

#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]

#2

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

如果您的xml中有多个预测,请使用节点功能:

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

sql小提琴演示

#1