How to read second record within XML file by using openxml?

[yingwen]

I have a xml similar like this:

[html]<Member ID="123">
<DateBorrowed>11-01-2006</DateBorrowed>
<Book ID="222"
Title="ABC">
<Category> Fiction</Category>
</Book>
<Book ID="333"
Title="ABu">
<Category>Children</Category>
</Book>
......
</Member>[/html]


I used OPENXML to read into MS SQL server database table, but it only reads first book. What should I do to read all three books, and insert 2 lines into the table:

MemberID | DateBorrowed | BookID | BookTitle | BookCategory
123 11-01-2006 222 ABC Fiction
123 11-01-2006 333 ABu Children
......

My code following only read first book:

CREATE PROCEDURE sp_insert_RefundCorrectionAcceptance  AS
 
DECLARE @iTree int
DECLARE @xmlFile VARCHAR(2000)
 
EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlFile,
SELECT    *
FROM  OPENXML (@iTree,'root', 3)
WITH (
MemberID decimal '/Member/@ID',
DateBorrowed datetime 'DateBorrowed/text()',
BookID decimal '/book/@id',
bookTitle varchar(200) '/book/@title',
bookCat varchar(100) '/book/category/text()'
 
)
EXEC sp_xml_removedocument @iTree
GO

Thank you.

[dorinbogdan]

For your XML, I tested with this Select query:

SELECT    *
FROM  OPENXML (@iTree,'Member/Book', 3)
WITH (
MemberID decimal '/Member/@ID',
DateBorrowed datetime '../DateBorrowed/text()',
BookID decimal '@ID',
bookTitle varchar(200) '@Title',
bookCat varchar(100) 'Category'
)
 

and it works.