Assuming your data is in A like so:

Mark Richads Jeff
Peter Simon Blue
Carol Jane Mary
Jack Peters Paul 


Sub GenerateXMLFiles()
    Dim ocell As Range
    Dim sXML As String
    Dim lFile As Long
    Dim lCount As Long
    For Each ocell In ActiveSheet.UsedRange.Columns(1).Cells
        lCount = lCount + 1
        sXML = "<?xml version=""1.0""?>"
        sXML = sXML & vbNewLine & "<Name>" & vbNewLine & "<Firstname>" & ocell.Value & "</Firstname>"
        sXML = sXML & vbNewLine & "<Surname>" & ocell.Offset(, 1).Value & "</Surname>"
        sXML = sXML & vbNewLine & "<Middlename>" & ocell.Offset(, 2).Value & "</Middlename>"
        sXML = sXML & vbNewLine & "</Name>"
        lFile = FreeFile
        Open "xml file " & lCount & ".xml" For Output As lFile
        Print #lFile, sXML
        Close lFile
    Next
End Sub