-- Looping through all records in Xml parameter using temporary table variable
-- using the WHILE loop construct
SET NOCOUNT ON;
DECLARE @xml XML;
SET @xml = '<table>
<col>
<field>Id</field>
<value>1</value>
</col>
<col>
<field>Name</field>
<value>Kaustubh</value>
</col>
</table>'
DECLARE @temp TABLE
(
ROW NVARCHAR(10),
FIELD NVARCHAR(MAX),
VALUE NVARCHAR(MAX)
)
INSERT INTO @temp
SELECT [Row] = ROW_NUMBER() OVER (ORDER BY map.value('field[1]','NVARCHAR(MAX)')), map.value('field[1]','NVARCHAR(MAX)') AS FIELD,
map.value('value[1]','NVARCHAR(MAX)') AS VALUE
FROM @xml.nodes('//table/col') R(map)
DECLARE @NumberRecords BIGINT = 0, @RowCount BIGINT = 1;
SELECT @NumberRecords = COUNT(Row) FROM @temp
DECLARE @field AS NVARCHAR(MAX) = NULL;
DECLARE @value AS NVARCHAR(MAX) = NULL;
WHILE @RowCount <= @NumberRecords
BEGIN
SELECT @field = ISNULL(FIELD,''), @value = ISNULL(VALUE,'')
FROM @temp
WHERE ROW = @RowCount
print '@field = ' + @field + ' & @value = ' +@value;
SET @RowCount += 1;
ENDThe code snippet specifies how to loop through records specified in xml parameter in sql.