OpenXml sub elements

Hi All,
I,m shredding this xml to view the data and ultimate goal is to insert
the data at a later stage but when I shred it with the syntax
mentioned below then I recieve only one record instead of 3 records
with different AnsOptions (last sub element).
Help!
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo />
<DVName>Home</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1.?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Answer>No</Answer>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
<Ansoption>
<Options>Time Waiting</Options>
</Ansoption>
<Ansoption>
<Options>Rude/Abrupt</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
--split --
SELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer', 2)
WITH
(DVName varchar (20) '../../../DVName',
DvcodeNo int '../../../DvcodeNo',
CustSurveyNo int '../../CustSurveyNo',
QuestionID int '../QuestionID',
QuestionDesc varchar(50) '../QuestionDesc',
AnswerID int,
Ansoption varchar (30))
EXEC sp_xml_removedocument @.docHandle
Results
Home1434Q1.?13Unhelpful
Any Ideas ?, I need to finish this procedure as quick as possible,
Please advise
SELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30) 'Options')
|||Thanks all sorted!
markc...@.hotmail.com wrote:
> SELECT *
> FROM
> -- Add OPENXML statement for ResponseDetail table
> OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
> Ansoption', 2)
> WITH
> (DVName varchar (20) '../../../../DVName',
> DvcodeNo int '../../../../DvcodeNo',
> CustSurveyNo int '../../../CustSurveyNo',
> QuestionID int '../../QuestionID',
> QuestionDesc varchar(50) '../../QuestionDesc',
> AnswerID int '../AnswerID',
> Ansoption varchar (30) 'Options')
|||It Doesn't work again if I add more data to the XML :
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1. Was your call answered prompt and
courteously?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
SELECT *
FROM
-- Add OPENXML statement for SalesOrderDetail table INSERT
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @.docHandle