asked on

Bulk Insert XML

I have a folder with about 200 or so xml files, all of the same node structure.
I would like to have a proc to loop through my single directory and bulk insert all of these xml documents into a single field/table, but I only want a single node ("mainbdy") to be imported into my table from each of these documents
How can this be done?

Many thanks in advance!

edemcs

I would bulk insert into a temp table each xml file into an xml column. Then, do a query on the xml column to get just that node to be inserted into the actual table. Here is an example:

insert into @temptable
values
('<root><mainbdy>data3</mainbdy><node2/></root>')

insert into @temptable
values
('<root><mainbdy>data4</mainbdy><node2/></root>')

select col1.query('//mainbdy') from @temptable

ASKER CERTIFIED SOLUTION

edemcs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

H-SC

ASKER

edemcs,
Thanks for your reply..
I have 200 plus xml files that will need importing
The code will need to fully loop through a directory of xml files and grab that single node from each file and place it into a table.

edemcs

I understand. Load the contents of each xml file into the temp table first, then proceed. You could create a stored procedure to do so.

H-SC

ASKER

ok I will try that, one question; using your code where do I put the path to the xml file?

insert into @temptable
values
('<root><mainbdy>data2</mainbdy><node2/></root>')

edemcs

That is simply for an example so you can see how your XML can get parsed out of the temporary table. Do you need to know how to do the Bulk Insert?

H-SC

ASKER

yes please

edemcs

You can loop through the files in an ssis package or stored proc for the bulk insert of each file. here is a great reference for bulk inserting an xml file into a table in a database. http://msdn.microsoft.com/en-us/library/ms191184.aspx