SQL:Looping and Reading from XML type



i have set of records from XML type and i need to loop through the Nodes and extract data from them



<Data xmlns:xsi="http://ift.tt/ra1lAU" xmlns:xsd="http://ift.tt/tphNwY">
<Opxmlns="http://ift.tt/1F4yoN3">
<ID>
<FDSerial>00000008</FDSerial>
<Type>1</Type>
</ID>
<Param>ParmString</Param>
<OperationName>ReadData</OperationName>
</Op>
</Data>


i already read from the record but i need to loop through the Nodes



;WITH XMLNAMESPACES('http://ift.tt/1F4yoN3' as ns)
SELECT Data.value('/Data[1]/ns:Op[1]/ns:ID[1]/ns:FDSerial[1]', 'varchar(50)')
as Serial
FROM [dbo].[DB]


what i need to replace the # 1 with i in a loop


No comments:

Post a Comment