SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Xml Looping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divyaram
Posting Yak Master

India
177 Posts

Posted - 10/26/2010 :  05:06:27  Show Profile  Reply with Quote
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
29910 Posts

Posted - 10/26/2010 :  06:19:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 10/26/2010 :  06:33:38  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 10/26/2010 :  06:34:13  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 10/27/2010 :  02:28:28  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 10/27/2010 :  04:57:10  Show Profile  Visit webfred's Homepage  Reply with Quote
welcome


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

shimeixiaoxiao
Starting Member

1 Posts

Posted - 10/27/2010 :  05:00:49  Show Profile  Reply with Quote
spam removed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000