How to read XML file from URL in SQL Server 2008

Question

I would like to read an xml and get a value from it but the xml file is returned via the following url:

http://webserver/XMLResult

I try To read the xml file, I used the folowing code :

    INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(BULK ‘http://webserver/XMLResult’,SINGLE_BLOB) as c

but I am getting the following error:

Cannot bulk load because the file… could not be opened. Operating system error code 123(error not found).

Do you know how to fix this issue?

If it is not possible, can someone recommend the best method which is help me reed XML from URL in sql server thanks

possible duplicate of How can I to read a XML from a URL using T-SQL? — Mikael Eriksson
– Mikael Eriksson, Commented Oct 24, 2013 at 6:02

Efran Cobisi · Accepted Answer · 2013-10-24 06:09:20Z

0

Apparently, the data-file parameter of the OPENROWSET function can't point to an URL. I would suggest to read the XML data outside of the TSQL block, possibly passing the data as a parameter to a stored procedure. If this is not possible and you must read the XML data from within TSQL, then two options come to my mind, although I am not a big fan of neither of them:

use a CLR function and read your XML data from there;
try to exploit the COM objects available to SQL Server machine by way of the sp_OACreate system stored procedure, as suggested here.

answered Oct 24, 2013 at 6:09

Efran Cobisi

6,5141 gold badge24 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to read XML file from URL in SQL Server 2008

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related