Does the file need to have the exact name "Sitemap.xml"?

No. The file could be a ASPX, PHP, XML etc. As long as the file is valid XML and following the sitemap schema it is ok. If you need to check your sitemap you can use one of the following tools.

Exmaple: If you have a ASP.NET site and want to have a dynamic generated Sitemap.xml file it could have the name "MySitemapXml.aspx".

You can register the URL to this sitemap.xml in the serach engine webmaster tools (example Google Webmaster Tools).