By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

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

P: 17
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:

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE sp_insert_RefundCorrectionAcceptance  AS
  2.  
  3. DECLARE @iTree int
  4. DECLARE @xmlFile VARCHAR(2000)
  5.  
  6. EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlFile,
  7. SELECT    *
  8. FROM  OPENXML (@iTree,'root', 3)
  9. WITH (
  10. MemberID decimal '/Member/@ID',
  11. DateBorrowed datetime 'DateBorrowed/text()',
  12. BookID decimal '/book/@id',
  13. bookTitle varchar(200) '/book/@title',
  14. bookCat varchar(100) '/book/category/text()'
  15.  
  16. )
  17. EXEC sp_xml_removedocument @iTree
  18. GO

Thank you.
Apr 5 '07 #1
Share this Question
Share on Google+
1 Reply


dorinbogdan
Expert 100+
P: 839
For your XML, I tested with this Select query:
Expand|Select|Wrap|Line Numbers
  1. SELECT    *
  2. FROM  OPENXML (@iTree,'Member/Book', 3)
  3. WITH (
  4. MemberID decimal '/Member/@ID',
  5. DateBorrowed datetime '../DateBorrowed/text()',
  6. BookID decimal '@ID',
  7. bookTitle varchar(200) '@Title',
  8. bookCat varchar(100) 'Category'
  9. )
  10.  
and it works.
Apr 5 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.