Loading External XML Files on Microsoft SQL Server

USE [PFMP]
GO

/****** Object:  UserDefinedFunction [dbo].[af_LoadRemoteFile]    Script Date: 03/10/2011 13:13:15 ******/
ALTER FUNCTION [dbo].[af_LoadRemoteFile](@url [nvarchar](255))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [LoadFile].[LoadFile].[LoadRemoteFile]
GO


DECLARE @xml xml
SELECT @xml=REPLACE(dbo.af_LoadRemoteFile('http://rss.cnn.com/rss/edition.rss'), 'utf-8' ,'utf-16')
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlServer;
using Microsoft.SqlServer.Server;

public class StoredProcedures
{

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString LoadRemoteFile(string remoteFile)
    {
		System.Net.WebClient remote = new System.Net.WebClient();
		byte[] myDataBuffer = remote.DownloadData (remoteFile);
		string download = System.Text.Encoding.ASCII.GetString(myDataBuffer);

        return download;
    }


};

Error handling and check for unauthorized remote files are completely up to the user.


blog comments powered by Disqus