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!
LVL 1
H-SCAsked:
Who is Participating?
 
edemcsConnect With a Mentor Commented:
Sorry, missed some code:

DECLARE @TempTable As Table(col1 xml)
insert into @temptable
values
('<root><mainbdy>data1</mainbdy><node2/></root>')

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

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
0
 
edemcsCommented:
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
0
 
H-SCAuthor Commented:
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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
edemcsCommented:
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.
0
 
H-SCAuthor Commented:
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>')

0
 
edemcsCommented:
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?
0
 
H-SCAuthor Commented:
yes please
0
 
edemcsCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.