HOW TO: Import XML Feed from Another Domain, SQL Server 2005

[kwilliams]

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:
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

[russell]

Should use SSIS package to do this...1st step, import the file to your domain. Then do a bulk insert.

[kwilliams]

Thanks for the info russell,

I'm in the Wizard right now, and I see a "Flat File Source" option. Is that the one to use? When I input the URL, it won't take it. It says "A valid filename must be selected", so I'm not sure how to proceed. I'll continue researching this option, but I'd appreciate any help. Thanks so much.

[kwilliams]

Hi again russell,

We've just installed SSIS onto the server, so I should be able to do this now. But I can't find any literature on how to import an XML file from another domain. Could you (or anyone else) please point me in the right direction? I'm stumped at this point. Thanks.