OpenXML - more than 8000 characters

26 March,2007 by Jack Vamvas

An excellent script to deal with XML documents that are greater than 8000 characters. This deals with the SQL Server 2000 limitation. SQL Server 2005 and beyond deasl with more than 8000 characters

Use it like :

DECLARE @hDoc int
EXEC usp_OpenXML_From_File 'c:\myXMLfile.xml', @hDoc OUTPUT

SELECT *
FROM OPENXML(@hDOC, '/rss/channel/item',2)
WITH 
(
  title varchar(200),
  link varchar(400),
  description nvarchar(2000)
  
)
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc usp_OpenXML_From_File(@strPathToFile varchar(500), @hdoc int output) as
declare @objStream int, @retval int, @filesize int, @chunknum int
declare @chunkstr nvarchar(4000), @strSQL nvarchar(4000)
declare @str1 nvarchar(4000), @str2 nvarchar(4000), @str3 nvarchar(4000), @str4 nvarchar(4000), @str5 nvarchar(4000),
     @str6 nvarchar(4000), @str7 nvarchar(4000), @str8 nvarchar(4000), @str9 nvarchar(4000), @str10 nvarchar(4000),
     @str11 nvarchar(4000), @str12 nvarchar(4000), @str13 nvarchar(4000), @str14 nvarchar(4000), @str15 nvarchar(4000),
     @str16 nvarchar(4000), @str17 nvarchar(4000), @str18 nvarchar(4000), @str19 nvarchar(4000), @str20 nvarchar(4000)
declare @ErrorSource varchar(255),  @ErrorDesc varchar(255)

-- create Stream Object from file
exec @retval = sp_OACreate 'ADODB.Stream', @objStream OUT
--open the stream object.  default stream type is text and should fit our needs
exec sp_OASetProperty @objStream, 'Type', 2 -- text(default)
exec sp_OASetProperty @objStream, 'CharSet', 'utf-8'
exec sp_OAMethod @objStream, 'Open'
--load file content into stream
exec @retval = sp_OAMethod @objStream, 'LoadFromFile', Null, @strPathToFile
IF @retval = 0 -- succesfully loaded file
begin
     -- get file size
     exec sp_OAGetProperty @objStream, 'Size', @filesize OUT
     -- you need to check file size and decide if it's too big or not
     -- depending on the number of @strN you declared : 
     -- eg with @str1 to @str20, you can handle files up to 20*3700 = 74000 characters
     if @filesize <= 74000
     begin
          set @chunknum = 1
          while @filesize > 0
          begin
               exec sp_OAMethod @objStream, 'ReadText', @chunkstr out, 3700
               set @chunkstr = replace(@chunkstr, char(39), char(39) + char(39))
               select      @str1 = isnull(case when @chunknum = 1 then @chunkstr else @str1 end,''),
                    @str2 = isnull(case when @chunknum = 2 then @chunkstr else @str2 end,''),
                    @str3 = isnull(case when @chunknum = 3 then @chunkstr else @str3 end,''),
                    @str4 = isnull(case when @chunknum = 4 then @chunkstr else @str4 end,''),
                    @str5 = isnull(case when @chunknum = 5 then @chunkstr else @str5 end,''),
                    @str6 = isnull(case when @chunknum = 6 then @chunkstr else @str6 end,''),
                    @str7 = isnull(case when @chunknum = 7 then @chunkstr else @str7 end,''),
                    @str8 = isnull(case when @chunknum = 8 then @chunkstr else @str8 end,''),
                    @str9 = isnull(case when @chunknum = 9 then @chunkstr else @str9 end,''),
                    @str10 = isnull(case when @chunknum = 10 then @chunkstr else @str10 end,''),
                    @str11 = isnull(case when @chunknum = 11 then @chunkstr else @str11 end,''),
                    @str12 = isnull(case when @chunknum = 12 then @chunkstr else @str12 end,''),
                    @str13 = isnull(case when @chunknum = 13 then @chunkstr else @str13 end,''),
                    @str14 = isnull(case when @chunknum = 14 then @chunkstr else @str14 end,''),
                    @str15 = isnull(case when @chunknum = 15 then @chunkstr else @str15 end,''),
                    @str16 = isnull(case when @chunknum = 16 then @chunkstr else @str16 end,''),
                    @str17 = isnull(case when @chunknum = 17 then @chunkstr else @str17 end,''),
                    @str18 = isnull(case when @chunknum = 18 then @chunkstr else @str18 end,''),
                    @str19 = isnull(case when @chunknum = 19 then @chunkstr else @str19 end,''),
                    @str20 = isnull(case when @chunknum = 20 then @chunkstr else @str20 end,''),
                    @chunknum = @chunknum + 1, @filesize = @filesize - 3700
          end
     end
end

exec('declare @handle int;exec sp_xml_preparedocument @handle out, ''' + @str1 + @str2 + @str3 + @str4 + @str5
 + @str6 + @str7 + @str8 + @str9 + @str10 + @str11 + @str12 + @str13 + @str14 + @str15
 + @str16 + @str17 + @str18 + @str19 + @str20+ ''';declare c1 cursor global for select @handle')
open global c1
fetch c1 into @hdoc
close global c1
deallocate global c1
exec sp_OAMethod @objStream, 'Close'
exec sp_OADestroy @objStream

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 Related Posts

SET ARITHABORT ON


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on OpenXML - more than 8000 characters


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer