vba to generate XML file with different values

[snb]

or

Sub M_snb()
   sn = Filter([transpose(if(A1:A100="","","<?xml version=""1.0""?><Name><Firstname>"&a1:A100&"</Firstname><Surname>"&B1:B100&"</Surname><Middlename>"&C1:C100&"</Middlename></Name>"))], "<")
   
   With CreateObject("scripting.filesystemobject")
    For j = 0 To UBound(sn)
      .createtextfile("G:\OF\xml_" & Format(j, "000") & ".xml").write Replace(sn(j), "><", ">" & vbLf & "<")
    Next
   End With
End Sub