Solved

Import XML files into SQL

Posted on 2011-09-15
3
351 Views
Last Modified: 2012-05-12
Experts,

I need to import multiple XML files into a single SQL table. I need two things:

1)  I need SQL query to create a table in SQL from the XML file so I dont have to manually create the table.
2) I need SQL import query to import bulk XML files into the table created in (1). All the XML files will be saved in the same folder. I will schedule the import to run once a day so the SQL import query would import in all XML files received that day.

See attached two example XML files that we get from third parties. The XML file is suppose to be a standard format.  Any ideas?  

Thanks.

 70DAVIES-CA-EMP.XML 80BOTKIN-LD.XML
0
Comment
Question by:asmyatt
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
HI.

Here are a few techniques / resources.

1. Use OPENROWSET(BULK 'your_xml_file_path', SINGLE_BLOB) as shown by SQL Server MVP, Plamen Ratchev -- http://pratchev.blogspot.com/2008/11/import-xml-file-to-sql-table.html

Note: Instead of INSERT INTO...SELECT, you could use SELECT...INTO to create table on the fly.

SQLTeam blog example -- http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx -- showing similar approach but then bringing XML into XML data type versus parsing directly from OPENROWSET. From the XML column, you can parse XML later into table.

MSDN: http://msdn.microsoft.com/en-us/library/ms191184.aspx

2. Use BULK INSERT (Example D) from above MSDN -- http://msdn.microsoft.com/en-us/library/ms188365.aspx. You can use a staging table that just holds the XML, then have a procedure that automates storing this to a physical table after shredding the XML.

3. A bit dated, but use XML Bulk Load component (VBScript) -- http://support.microsoft.com/kb/316005. Along these lines, you could use .NET code in some fashion.

4. SSIS -- http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx
MSDN: http://msdn.microsoft.com/en-us/library/ms140277.aspx
http://blogs.msdn.com/b/rdoherty/archive/2005/03/16/396956.aspx

Hope that helps!
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
I am not as SSIS savvy as I should be, so that should probably be #1 choice; however, I would probably end up one of the first two at the moment (that may change if I dive into the SSIS pool more -- again, as I think it is a good idea). Specifically, I would move data to a table containing an XML column and then I would do parsing in a routine as needed from there.
0
 
LVL 2

Author Comment

by:asmyatt
Comment Utility
I'll have to dig into this - apparently not as easy as I was hoping...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now