Grabbing values from multiple xml rows in sql (ms server 2005)

Question

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

Matthew Whited · Accepted Answer · 2009-10-02 20:38:34Z

1

you have a bug on the line "FROM @authorIds.nodes('/Authors/value') as ParamValues(ID)" it should be "FROM @authorIds.nodes('value') as ParamValues(ID)"

--- Test Data
DECLARE @BookRelated TABLE (ListOfAuthors XML)
DECLARE @Author TABLE (ID int, FullName VARCHAR(100))

INSERT INTO @BookRelated (ListOfAuthors) VALUES ('<Authors><value>1</value></Authors>')
INSERT INTO @Author (ID, FullName) VALUES (1, 'Matt')


-- test script
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('value') as ParamValues(ID) 

SELECT  a.ID, FullName AS Author
FROM @Author auth
INNER JOIN @BookAuthor a ON a.ID = auth.Id

answered Oct 2, 2009 at 20:38

Matthew Whited

22.5k4 gold badges54 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

simplyme Over a year ago

Thanks for the feedback Matt. I am using a simpler solution now.

Collectives™ on Stack Overflow

Grabbing values from multiple xml rows in sql (ms server 2005)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related