• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

Import XML files into SQL

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
asmyatt
Asked:
asmyatt
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
asmyattAuthor Commented:
I'll have to dig into this - apparently not as easy as I was hoping...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now