Importing Nested XML Document into SQL Server 2000

[jfjavier]

How do I import nested XML document to sql server 2000?

<Registrants>
<Member>
<ID>01</ID>
<LName>Dou</LName>
<FName>John</FName>
<Questions>
<Question>
<Title>What is your favorite fruits?</Title>
<Answers>
<Answer>Apple </Answer>
<Answer>Grape s</Answer>
</Answers>
</Question>
</Questions>
<Questions>
<Question>
<Title>What is your favorite colors?</Title>
<Answers>
<Answer>Blue</Answer>
</Answers>
</Question>
</Questions>
<Questions>
<Question>
<Title>What is your hobbies?</Title>
<Answers>
<Answer>Tenni s</Answer>
<Answer>Compute r Games</Answer>
</Answers>
</Question>
</Questions>
</Member>
<Member>
<ID>02</ID>
<LName>Lee</LName>
<FName>Jimmy</FName>
<Questions>
<Question>
<Title>What is your favorite fruits?</Title>
<Answers>
<Answer>Banan a</Answer>
<Answer>Apricot </Answer>
<Answer>Pears </Answer>
</Answers>
</Question>
</Questions>
<Questions>
<Question>
<Title>What is your favorite colors?</Title>
<Answers>
<Answer>Green </Answer>
<Answer>White </Answer>
</Answers>
</Question>
</Questions>
<Questions>
<Question>
<Title>What is your hobbies?</Title>
<Answers>
<Answer>Socce r</Answer>
</Answers>
</Question>
</Questions>
</Member>
</Registrants>


I appreciate if you could provide me a better solution with sample code snippets to accomplish this task.

I've tried "OpenXML" through SQL Server Store Procedure but it doesn't work. I can get only the "ID", "LName", and "FName".


Thanks a lot.
JFJavier

[Dököll]

I've tried "OpenXML" through SQL Server Store Procedure but it doesn't work. I can get only the "ID", "LName", and "FName".


Thanks a lot.
JFJavier

Greetings, JFJavier!

It looks like you are saying you are getting data returned.

Do you have data in the fields mentioned above?
Do you know if you are pulling in all fields?