Retrieving xml from sharepoint using SQL statement

Question

Retrieving xml from sharepoint using SQL statement

Kiran Kumar

SSC-Addicted

Points: 463
More actions
January 5, 2009 at 6:57 am

#133469

Can anyone plz help on finding what is the issue in the following script:
I'm getting the following error when running the script:
Msg 4861, Level 16, State 1, Line 4
Cannot bulk load because the file "http://servername/Request/123.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).
CREATE table #xmlTable (data XML)
--To get the xml from the given Location
BULK INSERT #xmlTable FROM 'http://servername/Request/123.xml' WITH(ROWTERMINATOR='')
select * from #xmlTable
drop table #xmlTable
Regards,
-Kiran

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

gyessql Ten Centuries Points: 1097 More actions · Answer 1

I replaced the http path with a local file path and it worked fine for me. Are u sure the file exists in the path specified?

Kiran Kumar SSC-Addicted Points: 463 More actions · Answer 2

Thanks for the reply. I have already tried it locally it works for me too, but when I'm trying to access the same as a URL it is giving me the above error. Is this a correct way of doing or is there any other better way for doing it?

Regards,

-Kiran

noeld SSC Guru Points: 96590 More actions · Answer 3

Kiran (1/5/2009)
Thanks for the reply. I have already tried it locally it works for me too, but when I'm trying to access the same as a URL it is giving me the above error. Is this a correct way of doing or is there any other better way for doing it?

BULK INSERT uses "file" protocol therefore http protocol is not supposed to be supported by BULK INSERT. You will need to download the File First somehow.

* Noel