Opening an XML from the Internet and inserting in to a table

[webuser]

What is the best way to do the following:

I have a URL that contains an XML file. I need to easily take that XML file and put it in to a table in SQL Server. I will know in advance the fields in the XML file and in the table.
I have seen some code using OpenXML, OpenRowset etc. but I have not been able to figure out how to directly open the URL of the XML file (i.e.

http://mywebsite/myxmlfile.xml)

and insert the data in to a table - but not in to an XML field - I want the fields parsed out in the SQL table.

Thanks in advance!!!

 

[mrdenny]

If you can get FTP access to the web server you can use SSIS or DTS to download the file. Failing that you can download http_get.exe (find it via google) and use that to download the file, then import it into a temp table using the BULK INSERT command, then break apart the data into your relational tables as needed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[webuser]

I was looking for something that I can do directly from a Stored Procedure. For example, I have gotten this code to work:

INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(
BULK 'c:\myfile\MyXML.xml', SINGLE_BLOB) AS x

What I would like to improve here is the ability to get the file directly from a URL and then parse the data.

If anyone has specific code examples, that would be great.

 

[mrdenny]

If you are using SQL 2005, you might be able to setup a CLR procedure to go get the xml from the web site and save it to disk. However that would be an unsave procedure and would put your SQL Server in an unsupported state by Microsoft.

(See

http://support.microsoft.com/kb/922672/en-us)

SQL doesn't reconse the internet as a valid data source. Your best bet will be the options I posted above.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[webuser]

Ok, I guess I can live without the requirement of opening up the URL directly. I can just download the file first, then open it locally from SQL Server.

Which brings me to the next part of the question though - can you provide code that will open an xml file (locally), and then parse it and dump it's information in to a table? (As opposed to just putting it in to an xml variable)

 

[gmmastros]

Here is an example.

thread220-1249932

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom