Query all XML files in a directory from SQL Server

Given a folder full of simple XML documents that all have the same structure, is there a quick way to either load all the documents into a temporary table or treat the folder as a table, in order to query the documents with the SQL 2005 XML query syntax?

The bulk load examples that I have seen, all try to parse the XML document while loading. For me this is not necessary as I am happy for the whole file to be treated as a single column of type 'xml'. The parsing will be handled 开发者_JAVA百科by the query syntax.

It is indeed a good thought, because all the xml files has same structure. I'm not sure about loading xml files into temporary table, but you can create an maintain a table in SQL Server 2005 with datatype xml and store each xml file as record. This way you can use XML.Query or XML.Value to query.

This article covers almost everything you want to know about XML query in SQL Server...

http://www.15seconds.com/issue/050803.htm

There's plentiful tutorials available in BeyondRelational.com
& Jacob Sebastian's blog.

Cheers!

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