November 1, 2008 at 10:33 am
Hello,
Is it possible to access a URL from SQL Server (within a SP or function) and retrieve data?. I need to import data daily into a SQL Server table from a URL that provides data in XML format.
I know that I can make SQL Server read XML data but can I make SQL Server to read directly from the URL instead of me downloading the URL data manually and then provide it to SQL Server?
Thanks,
Ganesh
November 1, 2008 at 1:26 pm
Hi Ganesh,
I am in deep water now but the URL isn't really the data provider is it? It's just a locator such as http://www.myspace.com or mailto:johndoe@myspace.com. When you download manually, how do you go about doing that?
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 1, 2008 at 2:23 pm
The URL itself is the data provider. When you open the URL in Internet Explorer, you get an XML data.
If I have to do it manually, I will opening the URL in IE and then save it as an XML file and then process the data.
So, I was wondering whether I can make the SQL Server directly read from the URL response.
I hope I made it clear.
Thanks,
Ganesh
November 1, 2008 at 3:28 pm
Hi again,
No, the URL isn't the provider; some component of sorts somewhere is providing the code that runs within the browser with the XML data.
For all I know it is still so that SQL Server, as a client or consumer, can only connect via OLEDB (except for the specific case of Merge Web Synchronization but that requires an IIS server with a bunch of custom dll's in between).
CLR integration is a part of SQL Server starting with 2005, so if you can find a way of getting to that XML data in a CLR component, you can then easily incorporate that in a stored procedure on the SQL Server.
It's not much, but it's all I've got on the subject. Maybe someone else has more creative or up-to-date ideas!
🙂
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 2, 2008 at 7:43 am
SSIS has an XML provider that should read from a URL. Or you can create a CLR component to do this.
It's not built into the server, nor should it be. This is a huge potential security hole, look at all the broswer issues we have already. You should have something to verify this data before SQL Server starts to read it.
November 3, 2008 at 8:08 am
Give this a read (using xp_cmdshell and CURL)
Getting Stuff Into SQL Server (his sample retrieved Bank of Canada exchange rate online as .csv and import into SQL)
http://www.simple-talk.com/community/blogs/philfactor/archive/2006/01/20/158.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply