Jump to content

How To: Import Xml Feed From Another Domain


kwilliams
 Share

Recommended Posts

I have a stored procedure (SP) that imports 2 internal XML feeds into a SQL Server 2005 table using Bulk Load/OPENROWSET without a problem. But I'd also like to import one more external XML feed that's not located on my domain into that same SP. When I enter the URL to that XML feed, I get this error message when I run the job:Msg 4861, Level 16, State 1, Line 2Cannot bulk load because the file "http://codeamber.org/a1xl04act/amberalert.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).(1 row(s) affected)From research, I'm *thinking* this is an issue regarding accessing data from another site/domain, but I can't say for sure. I'm able to do this in ASP.NET/VB.NET code without a problem, so I'm not sure why it wouldn't work within SQL Server 2005 somehow. I'm including the entire SP code below. If anyone can let me know what I'm doing wrong, it would be greatly appreciated. Thanks.spXMLImport:

USE [DATABASENAME]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spXMLImport]ASBEGINSET NOCOUNT ON;DROP TABLE tblXMLFeedsCREATE TABLE tblXMLFeeds(	record_id VARCHAR(50),	xmlTitle VARCHAR(100),		xmlFileName VARCHAR(300),		xml_data xml,	datestamp VARCHAR(23))DECLARE @record_id_aa VARCHAR(50)DECLARE @record_id_bb VARCHAR(50)DECLARE @record_id_cc VARCHAR(50)DECLARE @xmlTitle_aa VARCHAR(100)DECLARE @xmlTitle_bb VARCHAR(100)DECLARE @xmlTitle_cc VARCHAR(100)DECLARE @xmlFileName_aa VARCHAR(300)DECLARE @xmlFileName_bb VARCHAR(300)DECLARE @xmlFileName_cc VARCHAR(300)DECLARE @datestamp VARCHAR(23)SELECT  @record_id_aa = 'aaID'SELECT  @record_id_bb = 'bbID'SELECT  @record_id_cc = 'ccID'SELECT  @xmlTitle_aa = 'Internal Feed 1'SELECT  @xmlTitle_bb = 'Internal Feed 2'SELECT  @xmlTitle_cc = 'External Feed'SELECT  @xmlFileName_aa = '\\SERVERNAME\DIRECTORY\docs\xml\internalfeed1.xml'SELECT  @xmlFileName_bb = '\\SERVERNAME\DIRECTORY\docs\xml\internalfeed2.xml'SELECT  @xmlFileName_cc = 'http://codeamber.org/a1xl04act/amberalert.xml' --<<<----EXTERNAL FEED--******************I'VE ALSO TRIED THE FOLLOWING:******************--SELECT  @xmlFileName_cc = '//codeamber.org/a1xl04act/amberalert.xml'--SELECT  @xmlFileName_cc = '\\codeamber.org\a1xl04act\amberalert.xml'--SET datestamp as ISO-8601 datetime formatSELECT @datestamp = CONVERT(VARCHAR(23), GETDATE(), 126)EXEC('INSERT INTO tblXMLFeeds(record_id, xmlTitle, xmlFileName, xml_data, datestamp)SELECT ''' + @record_id_aa + ''', ''' + @xmlTitle_aa + ''', ''' + @xmlFileName_aa + ''', xmlData, ''' + @datestamp + ''' FROM(	SELECT  * 	FROM	OPENROWSET (BULK N''' + @xmlFileName_aa + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)INSERT INTO tblXMLFeeds(record_id, xmlTitle, xmlFileName, xml_data, datestamp)SELECT ''' + @record_id_bb + ''', ''' + @xmlTitle_bb + ''', ''' + @xmlFileName_bb + ''', xmlData, ''' + @datestamp + '''FROM(	SELECT  * 	FROM	OPENROWSET (BULK N''' + @xmlFileName_bb + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)INSERT INTO tblXMLFeeds(record_id, xmlTitle, xmlFileName, xml_data, datestamp)SELECT ''' + @record_id_cc + ''', ''' + @xmlTitle_cc + ''', ''' + @xmlFileName_cc + ''', xmlData, ''' + @datestamp + '''FROM(	SELECT  * 	FROM	OPENROWSET (BULK N''' + @xmlFileName_cc + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)')END

Link to comment
Share on other sites

  • 2 weeks later...
SQL Server just needs a file that's either local or on a network share, it can't load a remote URL.
I received this response from someone else:
Should use SSIS package to do this...1st step, import the file to your domain. Then do a bulk insert.
Are you saying that what's mentioned is not possible? Thanks for your help.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...