MSSQL GET URL TEXT

이번에는  URL 로부터 리턴 텍스트를 구한다.  (HTML)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		mulder
-- Create date: 
-- Description:	URL Tester

-- OLE Automation in SQLServer
-- http://forums.asp.net/t/1425747.aspx

-- pre-work-start
-- EXEC sp_configure 'Ole Automation Procedures';
-- sp_configure 'show advanced options', 1;
-- RECONFIGURE;
-- sp_configure 'Ole Automation Procedures', 1;
-- RECONFIGURE;
-- pre-work-end

-- select dbo.fncValidateURL ('http://127.0.0.1:8080/xxx');
-- select dbo.fncGetURLText ('http://127.0.0.1:8080/xxx');
-- =============================================
ALTER FUNCTION dbo.fncGetURLText(@URL VARCHAR(300))
RETURNS VARCHAR(5000)
AS
BEGIN
	DECLARE @Object INT
	DECLARE @Return TINYINT
	DECLARE @Valid BIT SET @Valid = 0 --default to false
	DECLARE @resText VARCHAR(5000) SET @resText = ''
	
	--create the XMLHTTP object
	EXEC @Return = sp_oacreate 'MSXML2.ServerXMLHTTP.3.0', @Object OUTPUT
	IF @Return = 0
	BEGIN
		DECLARE @Method VARCHAR(350)

		--define setTimeouts method
		--Resolve, Connect, Send, Receive
		SET @Method = 'setTimeouts(4500, 4500, 4500, 4500)'

		--set the timeouts
		EXEC @Return = sp_oamethod @Object, @Method

		IF @Return = 0
		BEGIN
			--define open method
			SET @Method = 'open("GET", "' + @URL + '", false)'
	
			--Open the connection
			EXEC @Return = sp_oamethod @Object, @Method
		END
	
		IF @Return = 0
		BEGIN
			--SEND the request
			EXEC @Return = sp_oamethod @Object, 'send()'
		END
	
		IF @Return = 0
		BEGIN
			DECLARE @Output INT
			EXEC @Return = sp_oamethod @Object, 'status', @Output OUTPUT
	
			IF @Output < 400
			BEGIN
				SET @Valid = 1
				
				EXEC @Return = sp_oamethod @Object, 'ResponseText', @resText OUTPUT
				
			END
		END
	END
	
	--destroy the object
	EXEC sp_oadestroy @Object

	RETURN (@resText)
END