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

[yingwen]

I have a xml similar like this:

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


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.

[iburyak]

Try this:


1. Create procedure:

[PHP]CREATE PROCEDURE sp_insert_RefundCorrectionAcceptance
@xmlFile VARCHAR(2000)
AS

DECLARE @iTree int

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

)
EXEC sp_xml_removedocument @iTree
GO[/PHP]

2. Execute procedure:

[PHP]sp_insert_RefundCorrectionAcceptance
'
<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>
'[/PHP]

Don't forget that XML is case sensitive.

Hope it helps.

Good Luck.

Irina.