在 SQL 中使用 XML 时的 Where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16484549/
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
Where clause when using XML in SQL
提问by Flood Gravemind
I am currently using a XML data type in a SQL table
我目前在 SQL 表中使用 XML 数据类型
My datatable looks like this
我的数据表看起来像这样
Id | Name | Surname | Title | Location | Artist |
-------------------------------------------------------
1 | xxx | abc | def | London | XML |
2 | xxx | abc | def | Oslo | XML |
3 | xxx | abc | def | New York | XML |
My XML file looks like this
我的 XML 文件看起来像这样
<song category="gaming">
<title>Valentine's Day</title>
<artist-main>Fatfinger</artist-main>
<artist-featured>Slimthumb</artist-featured>
<year>2013</year>
<price>29.99</price>
<album>Gamestain</album>
<albumimg>http://download.gamezone.com/uploads/image/data/875338/halo-4.jpg</albumimg>
<songurl>http://www.youtube.com/watch?v=-J0ABq9TnCw</songurl>
Now to get the record depending upon the artist I am using a query which is
现在要根据艺术家获取记录，我正在使用查询
SELECT
Id, Name, Surname, Title
FROM
DATA
WHERE
Artist Like '%Fatfinger%' -- (this is user input)
Is this is the right approach in querying XML data in SQL or are there any built in functions in SQL that can handle XML. I am new to SQL.
这是在 SQL 中查询 XML 数据的正确方法还是 SQL 中是否有任何可以处理 XML 的内置函数。我是 SQL 新手。
回答by Jasmina Shevchenko
Try this:
尝试这个：
declare @table table (
Id int, Name varchar(50), Surname varchar(50),
Title varchar(50), Location varchar(50), Artist xml)
insert into @table (Id, Name, Surname, Title, Location , Artist )
values(1, 'xxx', 'abc', 'def', 'London', '<song category="gaming"></song>
<title>Valentines Day</title>
<artist-main>Fatfinger</artist-main>
<artist-featured>Slimthumb</artist-featured>
<year>2013</year>
<price>29.99</price>
<album>Gamestain</album>
<albumimg>http://download.gamezone.com/uploads/image/data/875338/halo-4.jpg</albumimg>
<songurl>http://www.youtube.com/watch?v=-J0ABq9TnCw</songurl>')
SELECT Id, Name, Surname, Title
FROM @table
WHERE Artist.value('(/artist-main)[1]','varchar(max)') LIKE '%FatFinger%'
回答by Jeff B
You need to use the .value function.
您需要使用 .value 函数。
SELECT Id, Name, Surname, Title
FROM DATA
WHERE Artist.value('(/song/artist-main)[1]','varchar(max)') LIKE '%FatFinger%'
