Hi Guys,
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.
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