MySQL Forums
Forum List  »  Connector/Node.js

Using XML as a list in a MySQL Column
Posted by: chris joy
Date: January 18, 2012 10:43AM

So I have a MySQL database for a music library.

As I have several songs by the same artist, I have a table called Artists.

The Songs table links to the Artist table via an Artist_id.

Since a Song can have several different artists (or let's say 2 for a duet), I'm using XML to put the list of artist id's in. Here's what the data in Songs.Artist_ID's column looks like:

<artist_ids>
<artist_id>239780</artist_id>
<artist_id>784534</artist_id>
</artist_ids>

My question is simple. I've done queries by linking to the artist table via ExtractValue, but that only gets me the first one (or a specific one if I get specific with the XPath).

How can I search for or link with any/all of the artist_id's? I do not want to loop through SQL queries if I don't have to. Is there an easy way to say, here's a list of artists, go fetch the data on all of them?

This is the query I use now, and it gets it for the first artist only.

SELECT `tanoshimi`.`filename`, `linkArtists`.`name`, `linkSongs`.`title`
FROM `public_music`.`tanoshimi`, `public_music`.`linkArtists`, `public_music`.`linkSongs`
WHERE linkSongs.song_id = tanoshimi.song_id AND linkArtists.artist_id = ExtractValue(artist_ids, '/artist_ids/artist_id')

Any help on how to have multiple Artists listed in a single Column, but still searchable would be appreciated!
-Chris Joy

Options: ReplyQuote


Subject
Written By
Posted
Using XML as a list in a MySQL Column
January 18, 2012 10:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.