Importing HTML files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Importing HTML files

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-10-15 : 08:35:52

Guys,

I have a utility on each server that runs every quarter to generate HTML report of the server (like what is OS patached to, CPU utilization, drives etc).

Is there any way to import html files into SQL database and query it. In the sense that view source of each HTML can it be loaded into the database
as XML datatype and query the same. if so please provide any inputs to load such files and query them.

Thanks

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-15 : 09:46:56

I used a function created here:
http://www.simple-talk.com/code/WorkingWithFiles/uftReadFileAsTable.txt

To use you need to enable ole automation:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Once you have function created this will import as XML:
DECLARE @itemDoc xml
SET @itemDoc = (SELECT line FROM [dbo].[uftReadfileAsTable]
('C:\temp','test.html') FOR XML Path, type)

--SELECT @itemDoc
INSERT INTO dbo.A (text) (SELECT @itemDoc as Test)

The only issue you may have is each line in file becomes a node in XML. If this is a problem try using REPLACE to remove nodes and insert into a varchar(max) column instead.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-15 : 10:15:00

I have modified Phil Factor's function so that the entire HTML file is inserted into a single row so the XML INSERT puts entire file into a node.

Here is the modified function:

alter FUNCTION [dbo].[uftReadfileAsTable]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS
@File TABLE
(
[LineNo] int identity(1,1),
line varchar(8000))

AS
BEGIN

DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@String VARCHAR(8000),
@YesOrNo INT,
@Counter INT
set @Counter =0

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'OpenTextFile'
, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII

WHILE @hr=0
BEGIN
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

IF @YesOrNo<>0 break
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='reading from the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT
set @Counter = @Counter + 1
IF @Counter = 1
BEGIN
INSERT INTO @file(line) SELECT @String
END
IF @Counter > 1
BEGIN
UPDATE @file SET line= line + ' ' + @String
END
END

if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
insert into @File(line) select @strErrorMessage
end
EXECUTE sp_OADestroy @objTextStream
-- Fill the table variable with the rows for your result set

RETURN
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources