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 2
Cannot 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:
Code:
USE [DATABASENAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spXMLImport]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tblXMLFeeds
CREATE 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 format
SELECT @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
HOW TO: Import XML Feed from Another Domain
