| Author |
 Topic  |
|
|
divyaram
Posting Yak Master
India
177 Posts |
 Posted - 10/26/2010 : 05:06:27
|
HI All,
I need one help... currently i got struck with an xml,
i am pasting the code over here
DECLARE @docHandle INT
DECLARE @XML VARCHAR(MAX) = '<Questions>
<FIS_Question_Header Ques_Head_ID="2" Header_name="SPECIFIC EVALUATION" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="19" SubHeader_name="Material and Methods" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
<FIS_Question_Header Ques_Head_ID="1" Header_name="GENERAL COMMENTS" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="9" SubHeader_name="Significance: " SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="13" SubHeader_name="Expert Authority" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="1" SubHeader_name="Main Finding" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="2" SubHeader_name="Novelty" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
</Questions>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML
SELECT * FROM
OPENXML(@docHandle, '//Questions/FIS_Question_Header')
with (Ques_Head_ID int)
,
OPENXML(@docHandle, '//Questions/FIS_Question_Header/FIS_Question_SubHeader')
with ( SubHead_ID int)
EXEC sp_xml_removedocument @docHandle
for this i am getting the output like this
Ques_head_iD SUbHead_ID
2 19
2 9
2 13
2 1
2 2
1 19
1 9
1 13
1 1
1 2
But in this case for Quse_head_Id =2 only one subHeader_ID
is there, but the code which i have done is looping int
all nodes and taking every subHeadnodes and for Ques_head_Id =1
again it starts from SUbHead_ID=19 , it should not be like that
for Quse_head_Id =1 only 4 subhead_ID are there , so
i want the output like this
Ques_head_iD SUbHead_ID
2 19
1 9
1 13
1 1
1 2
How can i loop like that
Regards,
Divya |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/26/2010 : 06:19:48
|
Use XML when dealing with XML!DECLARE @Data XML = ' <Questions>
<FIS_Question_Header Ques_Head_ID="2" Header_name="SPECIFIC EVALUATION" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="19" SubHeader_name="Material and Methods" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
<FIS_Question_Header Ques_Head_ID="1" Header_name="GENERAL COMMENTS" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="9" SubHeader_name="Significance: " SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="13" SubHeader_name="Expert Authority" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="1" SubHeader_name="Main Finding" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="2" SubHeader_name="Novelty" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
</Questions>'
SELECT n.value('../@Ques_Head_ID', 'INT') AS QuesHeadID,
n.value('@SubHead_ID', 'INT') AS SubHeadID
FROM @Data.nodes('Questions/FIS_Question_Header/FIS_Question_SubHeader') AS s(n)
N 56°04'39.26"
E 12°55'05.63" |
 |
|
|
divyaram
Posting Yak Master
India
177 Posts |
Posted - 10/26/2010 : 06:33:38
|
quote:
Originally posted by Peso
Use XML when dealing with XML!DECLARE @Data XML = ' <Questions>
<FIS_Question_Header Ques_Head_ID="2" Header_name="SPECIFIC EVALUATION" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="19" SubHeader_name="Material and Methods" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
<FIS_Question_Header Ques_Head_ID="1" Header_name="GENERAL COMMENTS" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="9" SubHeader_name="Significance: " SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="13" SubHeader_name="Expert Authority" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="1" SubHeader_name="Main Finding" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="2" SubHeader_name="Novelty" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
</Questions>'
SELECT n.value('../@Ques_Head_ID', 'INT') AS QuesHeadID,
n.value('@SubHead_ID', 'INT') AS SubHeadID
FROM @Data.nodes('Questions/FIS_Question_Header/FIS_Question_SubHeader') AS s(n)
N 56°04'39.26"
E 12°55'05.63"
HI Peso.... Thanks a lots it really worked for me....
Regards,
Divya |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 10/26/2010 : 06:34:13
|
Or in your example start with SubHeader and work it's way back up the node tree.
DECLARE @docHandle INT
DECLARE @XML VARCHAR(MAX) = '<Questions>
<FIS_Question_Header Ques_Head_ID="2" Header_name="SPECIFIC EVALUATION" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="19" SubHeader_name="Material and Methods" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
<FIS_Question_Header Ques_Head_ID="1" Header_name="GENERAL COMMENTS" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="9" SubHeader_name="Significance: " SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="13" SubHeader_name="Expert Authority" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="1" SubHeader_name="Main Finding" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="2" SubHeader_name="Novelty" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
</Questions>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML
SELECT * FROM
OPENXML(@docHandle, '//Questions/FIS_Question_Header/FIS_Question_SubHeader')
with (Ques_Head_ID int '../@Ques_Head_ID',
SubHead_ID int '@SubHead_ID')
EXEC sp_xml_removedocument @docHandle
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
divyaram
Posting Yak Master
India
177 Posts |
Posted - 10/27/2010 : 02:28:28
|
quote:
Originally posted by webfred
Or in your example start with SubHeader and work it's way back up the node tree.
DECLARE @docHandle INT
DECLARE @XML VARCHAR(MAX) = '<Questions>
<FIS_Question_Header Ques_Head_ID="2" Header_name="SPECIFIC EVALUATION" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="19" SubHeader_name="Material and Methods" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
<FIS_Question_Header Ques_Head_ID="1" Header_name="GENERAL COMMENTS" TempID="0" HeaderSeq="1">
<FIS_Question_SubHeader SubHead_ID="9" SubHeader_name="Significance: " SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="13" SubHeader_name="Expert Authority" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="1" SubHeader_name="Main Finding" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
<FIS_Question_SubHeader SubHead_ID="2" SubHeader_name="Novelty" SubHeaderSeq="0" TempID="0">
</FIS_Question_SubHeader>
</FIS_Question_Header>
</Questions>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML
SELECT * FROM
OPENXML(@docHandle, '//Questions/FIS_Question_Header/FIS_Question_SubHeader')
with (Ques_Head_ID int '../@Ques_Head_ID',
SubHead_ID int '@SubHead_ID')
EXEC sp_xml_removedocument @docHandle
No, you're never too old to Yak'n'Roll if you're too young to die.
thanks webfred....
Regards,
Divya |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 10/27/2010 : 04:57:10
|
welcome
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
shimeixiaoxiao
Starting Member
1 Posts |
Posted - 10/27/2010 : 05:00:49
|
| spam removed |
|
|
| |
Topic |
|
Xml Looping
