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