CodingForums.com - View Single Post - How do I link to a table from an XML list of id's?

Tanoshimi · 01-18-2012, 08:04 PM

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.

Code:

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

01-18-2012, 08:04 PM	PM User \| #1
Tanoshimi New to the CF scene Join Date: Jan 2012 Posts: 1 Thanks: 0 Thanked 0 Times in 0 Posts	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. Code: <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.