xml 如何在tsql中查询xml列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13195922/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to query xml column in tsql
提问by fivelements
I have a table, T1, with a XML column, EventXML, on SQL Server 2008. I want to query all the rows where certain node contains a particular value. Better, I'd like to retrieve the value in a different node. The table T1:
我在 SQL Server 2008 上有一个表 T1，其中有一个 XML 列 EventXML。我想查询某个节点包含特定值的所有行。更好的是，我想检索不同节点中的值。表T1：
T1:
EventID, int
EventTime, datetime
EventXML, XML
Here is an example XML hierarchy:
这是一个示例 XML 层次结构：
<Event>
<Indicator>
<Name>GDP</Name>
</Indicator>
<Announcement>
<Value>2.0</Value>
<Date>2012-01-01</Date>
</Announcement>
</Event>
- How to find all the rows related to "GDP" Indicator;
- How to get all values for "GDP" Indicator;
- 如何找到所有与“GDP”指标相关的行；
- 如何获得“GDP”指标的所有值；
回答by marc_s
How about this?
这个怎么样？
SELECT
EventID, EventTime,
AnnouncementValue = t1.EventXML.value('(/Event/Announcement/Value)[1]', 'decimal(10,2)'),
AnnouncementDate = t1.EventXML.value('(/Event/Announcement/Date)[1]', 'date')
FROM
dbo.T1
WHERE
t1.EventXML.exist('/Event/Indicator/Name[text() = "GDP"]') = 1
It will find all rows where the /Event/Indicator/Nameequals GDPand then it will display the <Announcement>/<Value>and <Announcement>/<Date>for those rows.
它将找到/Event/Indicator/Name等于的所有行，GDP然后将显示这些行的<Announcement>/<Value>和<Announcement>/<Date>。
See SQLFiddle demo
回答by Quassnoi
DECLARE @t XML = '
<Event>
<Indicator>
<Name>GDP</Name>
</Indicator>
<Announcement>
<Value>2.0</Value>
<Date>2012-01-01</Date>
</Announcement>
</Event>
<Event>
<Indicator>
<Name>Other</Name>
</Indicator>
<Announcement>
<Value>3.0</Value>
<Date>2012-01-01</Date>
</Announcement>
</Event>
'
SELECT node.value('.', 'NUMERIC(20, 2)')
FROM @t.nodes('/Event[Indicator/Name/node()=''GDP'']/Announcement/Value') t(node)
