The solution, I've adopted is below. I'm not real happy with it because of the looping and the fact that it took a while to get it working when you would think this sort of functionality could be included and I've missed it. In any case the SQL below will leave you with a table variable containing the contents of your XML directory with one row for each file, a column containing the filename and another containing it's XML content.:
declare @directory varchar(256) set @directory = 'C:\Temp'
declare @filecount int, @fileindex int, @linecount int, @lineindex int
declare @filename varchar(255), @arg varchar(512), @contents varchar(8000)
set @arg = 'dir ' + @directory + '\ /A-D /B'
declare @dir table ([filename] varchar(512))
insert @dir exec master.dbo.xp_cmdshell @arg
declare @files table (id int not null identity(1,1), [filename] varchar(512), [content] xml null)
insert into @files ([filename]) select [filename] from @dir where [filename] like '%.xml'
select @filecount = count(*) from @files
set @fileindex = 0
while @fileindex < @filecount begin
set @fileindex = @fileindex + 1
select @filename = @directory + '\' + [filename] from @files where id = @fileindex
set @contents = ''
set @arg = 'type ' + @filename
create table #lines(id int not null identity(1,1), line varchar(255))
insert into #lines exec master.dbo.xp_cmdshell @arg
select @linecount = count(*) from #lines
set @lineindex = 0
while @lineindex < @linecount begin
set @lineindex = @lineindex + 1
select @contents = @contents + line from #lines where Id = @lineindex
end
drop table #lines
update @files set [content] = @contents where id = @fileindex
end
select * from @files
go