Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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.

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

    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •