Where clause when using XML in SQL

Question

I am currently using a XML data type in a SQL table

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

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

Wich RDBM's are you using, MS SQL Server, Oracle, etc. Retag your question so it has a better visibility and therefore get some attention — Yaroslav, May 10 '13 at 14:39
Also, check your XML sintax, line 3 and 4 does not have a correct closing tag. Should be `artist-main` and `artist-feature`. And I guess there is a closing `` tag at the end. — Yaroslav, May 10 '13 at 14:45
@John I just had a look at the link. Thanks but found it a little bit advanced. I have just begin to use sql less than 10 hrs :) — Flood Gravemind, May 10 '13 at 15:45
@FloodGravemind, no problem. The point really is that you're not using SQL at all when it comes to XML fields. You're actually using a form of XPath. That question has links that explain how it works. You *can* treat the XML as text and use string manipulation techniques like you're doing in your example, but it's usually better to use XML-specific tools on XML. — John M Gant, May 10 '13 at 16:15

score 14 · Accepted Answer · answered May 10 '13 at 17:51

14

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%'

answered May 10 '13 at 17:51

Jasmina Shevchenko

797
6
17

1

Are you going around answering all the xml sql questions :) – Jay Jay Jay May 10 '13 at 18:53
2

No :). I recently some learned xml, so I just knew the answer. – Jasmina Shevchenko May 10 '13 at 18:58
@Mike Can I use the "=" operator instead of "Like" in this statement? And one more thing what does the "[1]" signify? Thanks – Flood Gravemind May 10 '13 at 19:44
Getting - Cannot call methods on nvarchar(max). – Abhilash V Jul 05 '18 at 09:41

score 9 · Answer 2 · answered May 10 '13 at 14:43

9

You need to use the .value function.

SELECT Id, Name, Surname, Title 
FROM DATA 
WHERE Artist.value('(/song/artist-main)[1]','varchar(max)') LIKE '%FatFinger%'

answered May 10 '13 at 14:43

Jeff B

155
7

Can I use the "=" operator instead of "Like" in this statement? And one more thing what does the "[1]" signify? Thanks – Flood Gravemind May 10 '13 at 19:43
Yes, you can. Just remove the wild-card characters. Also, [1] indicates that you're going to return the first value at that song/artist-main node. That's what you want. John M Gant's link above mentions that too. – Jeff B May 10 '13 at 19:50

Where clause when using XML in SQL

2 Answers2