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

quote:

---

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

---

DECLARE	@url VARCHAR(300),
	@win INT,
	@hr INT,
	@Text NVARCHAR(MAX)


SET	@url = 'http://codeamber.org/a1xl04act/amberalert.xml'

EXEC @hr = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Open', NULL, 'GET', @url, 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAGetProperty @win, 'ResponseText', @Text OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OADestroy @win 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

SELECT	CAST(@Text AS XML)

quote:

---

Public Sub Main()

   Dim httpConnection As Microsoft.SqlServer.Dts.Runtime.HttpClientConnection

   Dim temp As Object

   temp = Dts.Connections("JPGToDownload").AcquireConnection(Nothing)

   httpConnection = New HttpClientConnection(temp)

   httpConnection.DownloadFile(Dts.Variables("DownloadFile").Value.ToString(), True)

   Dts.TaskResult = Dts.Results.Success

End Sub

---