Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
- sqlserver-dba.com switching from feedburner to follow.it
- Taking transaction log backup and full back at the same time
How to use OpenXML for more than 8000 characters
26 March,2007 by Tom Collins
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
XML has become a gained wide usage and it is also possible to use alternative methods for shredding , read more on - Shredding XML with Powershell and SQL Server
Related Posts
Author: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment
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.
Posted by: |