This imports an XML document file parses it and extracts information.
It uses the function f_GetXMLString below and only deals with fairly simple xml structures.

This deals with the xml stucture

...
<Data>
<Entry1>value1a</Entry1>
<Entry2>value2a</Entry2>
</Data>
<Data>
<Entry1>value1a</Entry1>
<Entry2>value2a</Entry2>
</Data>
<Data>
<Entry1>value1a</Entry1>
<Entry2>value2a</Entry2>
</Data>

and will extract and process the Entry1 and Entry2 values.



if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ImportXML]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ImportXML]
GO

Create procedure s_ImportXML
@FilePath	varchar(1000) ,
@FileName	varchar(100)
as

declare	@Result			varchar(200) ,
	@i			int ,
	@j			int ,
	@k			int ,
	@Len			int ,
	@s			varchar(8000) ,
	@sql			varchar(1000) ,
	@filemod4		int
	
	-- import the file
	select @filemod4 = 0
	create table #dir (s varchar(1000))
	select @sql = 'dir ' + @FilePath + @FileName
	insert	#dir
	exec master..xp_cmdshell @sql
	delete #dir where s not like '%' + @FileName + '%' or s is null
	select @i = convert(int,replace(substring(s, 20, (charindex(@FileName, s) - 20)), ',', '')) from #dir
	if @i % 4 = 0
	begin
		select @filemod4 = 1
		select @sql = 'copy ' + @FilePath + @FileName + ' ' + @FilePath + 'work.txt'
		exec master..xp_cmdshell @sql
		select @sql = 'echo xx >> ' +  @FilePath + 'work.txt'
		exec master..xp_cmdshell @sql
	end
	drop table #dir
	
	create table #data (s text)
	if @filemod4 = 1
		select @sql = 'bulk insert #data from ''' + @FilePath + 'work.txt' + ''' with (rowterminator=''\0'')'
	else
		select @sql = 'bulk insert #data from ''' + @FilePath + @FileName + ''' with (rowterminator=''\0'')'
	exec (@sql)
	
	if @filemod4 = 1
	begin
		select @sql = 'del ' + @FilePath + 'work.txt'
		exec master..xp_cmdshell @sql
	end
	
	-- now deal with the failures
declare	@Data			varchar(8000) ,
	@Entry1			varchar(1000) ,
	@Entry2			varchar(1000)
	
	select 	@i = 1
	select	@Len = datalength(s) from #data
	while @i < @Len
	begin
		select 	@s = substring(s, @i, 8000) from #data
		select	@Data = admin.dbo.f_GetXMLString(@s, 'Data')
		select	@Entry1 = admin.dbo.f_GetXMLString(@TransactionErrorDetails, 'Entry1')
		select	@Entry2 = admin.dbo.f_GetXMLString(@TransactionErrorDetails, 'Entry2')
		
		-- deal with the @entry1, @entry2 values here

		select 	@i = @i + charindex('<Data>', @s) + (2 * len('<Data>'))
		select @i = @i + len(@TransactionErrorDetails)
	end
go


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetXMLString]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetXMLString]
GO

Create function f_GetXMLString
(
@line varchar(8000) ,
@Tag varchar(100)
)
returns varchar(8000)
as
begin
/*
select dbo.f_GetXMLString('<a>qwer</a>', 'a')
*/

declare	@i int ,
	@j int ,
	@s varchar(8000)

	select	@i = charindex('<' + @Tag + '>', @line)
	select @j = charindex('</' + @Tag + '>', @line)
	if @i <> 0 and @j > @i
	begin
		select @i = @i + len('<' + @Tag + '>')
		select @s = substring(@line, @i, @j - @i)
	end
	
	return @s
end

go