title: Grabbing values from multiple xml rows in sql (ms server 2005)
body: <p>I will attempt to explain this situation as clearly as I can. I have this query below which tries to get a list of author_ids from an xml column in sql server 2005 associated with a book, but assigning the query to the @authorIds returns an error because it returns multiple rows, any ideas on how to run around this problem? Many thanks guys.</p>
  
  <pre><code>DECLARE @BookAuthor TABLE (ID int) 
  DECLARE @authorIds xml
  SET @authorIds = (select ListOfAuthors.query('/Authors/value') from BookRelated where ListOfAuthors is not null)
  
  INSERT INTO @BookAuthor (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
  FROM @authorIds.nodes('/Authors/value') as ParamValues(ID) 
  
  SELECT  a.ID,
      FullName AS Author
  
  FROM    Author auth
  INNER JOIN @BookAuthor a
  ON      a.ID = auth.Id
  </code></pre>