[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:
Expand|Select|Wrap|Line Numbers
- 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.