I found that code - too bad I didn't also save a reference to the thread. Anyway, this code loops through XML file:

DECLARE @x xml;

SET @x = N'<Employees>
  <Employee ID="101">
    <Name>Jacob</Name>
    <Department>IT</Department>
  </Employee>
  <Employee ID="354">
    <Name>Steve</Name>
    <Department>IT</Department>
  </Employee>
  <Employee ID="456">
    <Name>Bob</Name>
    <Department>IT</Department>
  </Employee>
  <Employee ID="478">
    <Name>Joe</Name>
    <Department>IT</Department>
  </Employee>
  <Employee ID="981">
    <Name>Louis</Name>
    <Department>IT</Department>
  </Employee>
</Employees>';
 

-- Total count of <Employee> Nodes
DECLARE @max int, @i int;

SELECT @max = @x.value('count(/Employees/Employee)', 'int');

-- Set counter variable to 1
SET @i = 1;

-- variable to store employee name
DECLARE @EmpNode xml;


-- loop starts
WHILE @i <= @max BEGIN

    -- select "Name" to the variable
    SELECT
        @EmpNode = @x.query('/Employees/Employee[sql:variable("@i")]');

    -- print the name
    SELECT @EmpNode;

    -- increment counter
    SET @i = @i + 1        ;
END
GO

>>>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.
>
>Thank you. No rush; only if you have time.