HOW TO: Import XML Feed from Another Domain

kwilliams · #1 (**permalink**) June 26th, 2009, 04:41 PM

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

kwilliams · #2 (**permalink**) July 7th, 2009, 11:36 AM

I received this response from someone else:

Quote:

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

...but I can't figure out how to import an external XML file. If anyone can point me in the right direction, that would be great. Thanks for any help.

Need to download code?

Navigation