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
Home 1 4 34 Q1.? 13 Unhelpful
Any Ideas ?, I need to finish this procedure as quick as possible,
Please adviseSELECT *
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
Showing posts with label sub. Show all posts
Showing posts with label sub. Show all posts
Monday, February 20, 2012
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
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
Subscribe to:
Posts (Atom)