>Here is an example of processing an XML in a stored procedure:
>
>

>declare @myxml as XML = N'
><TABLENAME> 
>   <Fld Name="Field1">ValueInField1</Fld> 
>   <Fld Name="Field2">ValueInField2</Fld> 
>   <Fld Name="Field3">ValueInField3</Fld> 
></TABLENAME> '
>
>-- SQL Select to get the values
>SELECT [Field].value('.','varchar(100)') as FldValue, [Field].value('@Name','varchar(20)') as FldName 
>  FROM @myxml.nodes('/TABLENAME/Fld') Fld25 ([Field])
>
>-- The Results window will the following:
>FldValue           FldName
>ValueInField1   Field1
>ValueinFiedl2   Field2
>ValueInField3   Field3
>

>
>My question is, how can I SCAN through the results. I know that SQL Server does not have a SCAN command so I use it as a pseudo term. But basically I need to create (dynamically) a SQL INSERT based on the values in the XML and the only way I see it possible is by scanning the results (keep in mind that the number of elements in the email is a variable).
>Any suggestions?

There was a recent thread on this exact topic in MSDN forum (either in T-SQL or in XML forum). I'll search later if I saved the code.