Open Xml

Hi All,

I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.

Definitely doing something wrong here, please advise!

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>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'

DECLARE @.docHandle int

EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc

SELECT *
FROM
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) 'http://http://DVName',
DvcodeNo int 'http://http://DvcodeNo',
CustSurveyNo int 'http://../CustSurveyNo',
ClaimGroupType varchar (20) 'http://../ClaimGroupType',
QuestionID int 'http://QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')

EXEC sp_xml_removedocument @.docHandle

The reason you are only getting two rows in the resultset is because there are only two nodes that match your xpath expression /DivisionName/ClaimGroup/Questions/Answer/Ansoption.

What is the result set that you desire? I'm not certain what the results are you implying when you say you are expecting 4 rows.

Regards,

Galex

|||

Thanks for the reply first of all, luckily I have sorted it out now.

Thanks again

|||

Hi

I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml

<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber /><level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>

Srini

|||Can you be more specific in your question? E.g. what element you want to access? After you have access, what do you want to do about that element?|||

This is the column in a table that contains the skill set of an individual candidate. on Passing the candidate id , i would access the access and the output i would give is like

coach=false, cdt=false,dit=false,fsa=false, lmr=false,iam=false,qefmemer=false,qefcertificatenumber=null,btec=false.

i would like to query for basic qualification and additional qualifications.

Thanks

Srini

|||

Anonymous wrote:

Hi

I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml

<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber /><level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>

Srini

<xsl:for-each select="skill/coach/{@.type}>

</xsl:for-each>