Xml Looping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Xml Looping

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-10-26 : 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

30421 Posts

Posted - 2010-10-26 : 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

180 Posts

Posted - 2010-10-26 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-26 : 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

180 Posts

Posted - 2010-10-27 : 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