• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 962
  • Last Modified:

Loop through data in SQL Server store proc

Below is a proc that I have. after executing it I get 2 rows of XML. I want to loop through each row and perform some tasks on it. How can I loop through different rows in SQL server store proc
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, 
'<ClauseGroup>
		<GroupID>1</GroupID>
		<GroupName>Test Group</GroupName>
		<EffectiveDate>1/1/1900 12:00:00 AM</EffectiveDate>
		<ExpirationDate>1/1/1900 12:00:00 AM</ExpirationDate>
		<DeActivateDate>1/1/1900 12:00:00 AM</DeActivateDate>
	 <Clauses>
	  <Clause>
	   <ClauseID>0</ClauseID>
	   <ClauseGroupID>2</ClauseGroupID>
	   <EffectiveDate>1/1/1900 12:00:00 AM</EffectiveDate>
	   <ExpirationDate>1/1/1900 12:00:00 AM</ExpirationDate>
	   <DeActivateDate>1/1/1900 12:00:00 AM</DeActivateDate>
	   <Description></Description>
	  <Operation>
	   <Code>BET</Code>
	  </Operation>
	  <Attribute>
	   <AttributeID>2</AttributeID>
	  </Attribute>
	  <Values>
	   <Value>
	     <value>Test4</value>
	   </Value>
	   <Value>
	     <value>Test5</value>
	   </Value>
	  </Values>
	  </Clause>
	  <Clause>
	   <ClauseID>1</ClauseID>
	   <ClauseGroupID>2</ClauseGroupID>
	   <EffectiveDate>1/1/1900 12:00:00 AM</EffectiveDate>
	   <ExpirationDate>1/1/1900 12:00:00 AM</ExpirationDate>
	   <DeActivateDate>1/1/1900 12:00:00 AM</DeActivateDate>
	   <Description>test</Description>
	  <Operation>
	   <Code>IN</Code>
	  </Operation>
	  <Attribute>
	   <AttributeID>3</AttributeID>
	  </Attribute>
	  <Values>
	   <Value>
	     <value>Test6</value>
	   </Value>
	   <Value>
	     <value>Test7</value>
	   </Value>
	  </Values>
	  </Clause>	
	 </Clauses>
	</ClauseGroup>'
 
Select * 
FROM OPENXML(@docHandle, 'ClauseGroup/Clauses/Clause', 1) 
WITH (Data xml '.')

Open in new window

0
pratikshahse
Asked:
pratikshahse
  • 2
1 Solution
 
chapmandewCommented:
Use a cursor to do the looping:

DECLARE  CursorTemplate CURSOR
FAST_FORWARD FOR       
      SELECT Val1, Val2, Val3 FROM Table1

OPEN CursorTemplate

FETCH NEXT FROM CursorTemplate
INTO      @Var1, @Var2, @Var3

WHILE (@@FETCH_STATUS = 0)
BEGIN
      --do something here w/ your data

      FETCH NEXT FROM CursorTemplate
      INTO      @Var1, @Var2, @Var3

END

CLOSE CursorTemplate
DEALLOCATE CursorTemplate
0
 
pratikshahseAuthor Commented:
i am kind of confused with this  statement. Can you please explain.

FAST_FORWARD FOR      
      SELECT Val1, Val2, Val3 FROM Table1  (I dont have to select anything from the table over here. The data is coming XML )





0
 
chapmandewCommented:
Right...you'd just swap in your SQL statement that pulls the XML data out...

ECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT,
'<ClauseGroup>
                <GroupID>1</GroupID>
                <GroupName>Test Group</GroupName>
                <EffectiveDate>1/1/1900 12:00:00 AM</EffectiveDate>
                <ExpirationDate>1/1/1900 12:00:00 AM</ExpirationDate>
                <DeActivateDate>1/1/1900 12:00:00 AM</DeActivateDate>
         <Clauses>
          <Clause>
           <ClauseID>0</ClauseID>
           <ClauseGroupID>2</ClauseGroupID>
           <EffectiveDate>1/1/1900 12:00:00 AM</EffectiveDate>
           <ExpirationDate>1/1/1900 12:00:00 AM</ExpirationDate>
           <DeActivateDate>1/1/1900 12:00:00 AM</DeActivateDate>
           <Description></Description>
          <Operation>
           <Code>BET</Code>
          </Operation>
          <Attribute>
           <AttributeID>2</AttributeID>
          </Attribute>
          <Values>
           <Value>
             <value>Test4</value>
           </Value>
           <Value>
             <value>Test5</value>
           </Value>
          </Values>
          </Clause>
          <Clause>
           <ClauseID>1</ClauseID>
           <ClauseGroupID>2</ClauseGroupID>
           <EffectiveDate>1/1/1900 12:00:00 AM</EffectiveDate>
           <ExpirationDate>1/1/1900 12:00:00 AM</ExpirationDate>
           <DeActivateDate>1/1/1900 12:00:00 AM</DeActivateDate>
           <Description>test</Description>
          <Operation>
           <Code>IN</Code>
          </Operation>
          <Attribute>
           <AttributeID>3</AttributeID>
          </Attribute>
          <Values>
           <Value>
             <value>Test6</value>
           </Value>
           <Value>
             <value>Test7</value>
           </Value>
          </Values>
          </Clause>    
         </Clauses>
        </ClauseGroup>'
 


DECLARE  CursorTemplate CURSOR
FAST_FORWARD FOR      
Select *
FROM OPENXML(@docHandle, 'ClauseGroup/Clauses/Clause', 1)
WITH (Data xml '.')

OPEN CursorTemplate

FETCH NEXT FROM CursorTemplate
INTO      @Var1, @Var2, @Var3

WHILE (@@FETCH_STATUS = 0)
BEGIN
      --do something here w/ your data

      FETCH NEXT FROM CursorTemplate
      INTO      @Var1, @Var2, @Var3

END

CLOSE CursorTemplate
DEALLOCATE CursorTemplate
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now