11

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.

Improve this question
5
  • 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 Commented May 10, 2013 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 </song> tag at the end. Commented May 10, 2013 at 14:45
  • @Yaroslav I am using MS SQL 2012 Commented May 10, 2013 at 15:38
  • @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 :) Commented May 10, 2013 at 15:45
  • 1
    @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. Commented May 10, 2013 at 16:15

2 Answers 2

Reset to default
13

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%'
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Are you going around answering all the xml sql questions :)
No :). I recently some learned xml, so I just knew the answer.
@Mike Can I use the "=" operator instead of "Like" in this statement? And one more thing what does the "[1]" signify? Thanks
Getting - Cannot call methods on nvarchar(max).
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%'
Improve this answer

2 Comments

Can I use the "=" operator instead of "Like" in this statement? And one more thing what does the "[1]" signify? Thanks
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.