How do I link to a table from an XML list of id's?

I know it's generally poor DB design to have more than one piece of data in a single field, but I have the following setup:

Database called Library
Table called Authors
Table called Books

Books links to Author via it's primary key. Since a book can have more than one author, I have a column in Books called Authors, that contains XML list of all the authors id's.


<author_id>782395</author_id>
<author_id>234523</author_id>


I can link the two table with an ExtractValue command, and it works to get one of the authors (whichever one I select via the XPath statement), but I need to get all of them.

Is there a way to do this? I want to be able to search for all books by a particular Author, even if they're 5th or 6th id in the list.

Ummm...why would you store XML data in a DB field in the first place?

But ignoring that...

Yes, it is generally poor DB design to have any kind of delimited list in a single field.

And yes, it leads you to doing things such as using XPath that you should never do.

Isn't there any way you can fix this DB design? Surely it wouldn't be that hard to run a one-time process that would go through an normalize the database and create a many-to-many table of BookAuthors. You would do that one time and then you'd have the performance--and query capabilities--that you are looking for.


CREATE TABLE BookAuthors (
bookid INT REFERENCES books(bookid),
authorid INT REFERENCES authors(authorid),
PRIMARY KEY (bookid, authorid)
);


***********

Having said all the above, you *COULD* do this with a really ugly query.


SELECT b.bookname, a.authorname
FROM books AS b, authors AS a
WHERE b.Authors LIKE CONCAT( '%>', a.authorid, '<%' )

Using LIKE will give you pretty ugly performance, so I sure hope your database is relatively tiny.