How to read a remote xml file from SQL Server 2005

Question

Can I read a XML file from remote server such as

http://dealer.somedomain.com/data/inventory.xml

I need read and get data from this remote XML file and update some local tables.

I use SQL Server 2005.

Thanks in advance.

Answer 1

Your task can be split into two sub-tasks.

1. Downloading the file over HTTP to your local PC (to a temporary folder).
2. Importing the XML file into SQL Server.

You can use SSIS for both. Once you look for "SSIS downloading over HTTP" or "SSIS downloading from website" you will find many tutorials for file download.

For the second step you will need data import, there are plenty tutorials as well, here is just an interesting pick.

You can find ready solutions including data download and import all-in-one, like here.