The latest version of Microsoft Access has the ability to import XML files. Or even Microsoft Excel. You might try linking the XML file into Access so you don't have to re-import each time. From there, you can push the data into MS SQL Server.

If the data is in the same format all of the time, you might just write a schema to do it, and create a stored procedure or an executable to keep it updated.

I was faced with a similar issue; I created something to download the tables, truncate the current tables in the database, and then--via a schema--execute and refresh the sql data.

If you need some coding direction, I can provide some sheets, upon request.

You can use XSLT to transform the data into a CSV file, a series of SQL insert statements, or most anything else your SQL database software prefers for import. Free XSLT processors are widely available for just about any platform (e.g., Google "xsltproc").