[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Bulk Insert XML

Posted on 2011-05-04
8
Medium Priority
?
592 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:H-SC
  • 5
  • 3
8 Comments
 
LVL 8

Expert Comment

by:edemcs
ID: 35690156
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
 
LVL 8

Accepted Solution

by:
edemcs earned 2000 total points
ID: 35690165
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
 
LVL 1

Author Comment

by:H-SC
ID: 35690339
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:edemcs
ID: 35690378
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
 
LVL 1

Author Comment

by:H-SC
ID: 35690976
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
 
LVL 8

Expert Comment

by:edemcs
ID: 35690985
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
 
LVL 1

Author Comment

by:H-SC
ID: 35691008
yes please
0
 
LVL 8

Expert Comment

by:edemcs
ID: 35691113
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question