pratikshahse
asked on
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 '.')
ASKER
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 )
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 )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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