Best way to load XML files to SQL Server using C#

Question

I'd like to use SQLXMLBulkLoad, but I do not know nothing about XML files which have to be imported to temporary DB on SQL Server (without constraints, references, etc.). As far as I know SQLXMLBulkLoad requires schema for each XML document. Is there any way to create schema dynamically? Or, probably, there is better way to do what I need?

I'm not able to use SSIS.

 

Answer 1

Do you need to store the XML in a XML column of a table or do you need to shred the XML and store attributes/elements into separate columns?  Depending on what you need to do, you can use the following:

OpenXML - http://msdn.microsoft.com/en-us/library/ms186918.aspx

XQuery - http://msdn.microsoft.com/en-us/library/ms189075.aspx

SQLXMLBulkLoad - http://msdn.microsoft.com/en-us/library/ms171878.aspx

I have also blogged about OpenXML and XQuery and how to use these technologies via .NET

http://blog.wharton.com.au/2011/05/24/sending-multiple-rows-of-data-to-sql-server-2008r2/

---

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
MC ID: Microsoft Transcript

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

I want to store elements into separate columns.
I've described above why I cannot use SQLXMLBulkLoad.
Right now I'm thinking of using .NET SqlBulkCopy Class. It seems reliable and simple, but I'm not sure is this way will be good for performance of my app.

Answer 3

I want to store elements into separate columns.
I've described above why I cannot use SQLXMLBulkLoad.
Right now I'm thinking of using .NET SqlBulkCopy Class. It seems reliable and simple, but I'm not sure is this way will be good for performance of my app.

Ok.  Performance will be good because SqlBulkCopy is optimised for speed. Have a read of the following article, specifically the section of Column mappings http://www.akamarketing.com/blog/135-importing-xml-into-sql-server-table-aspnet.html

---

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
MC ID: Microsoft Transcript

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 4

I've already implemented that. Probably, that's the best way to do what I want. But I'm not sure this method will work fast with big XML files (~20-40 MB), because the app loads data to DataSet first...

Answer 5

Hi Andrey Gordeev,

Using SqlBulkCopy to load large XML files to SQL Server in a faster way, you can take use of a user defined class that implements IDataReader interface in .Net to read through data and save them to SQL Server. Please view this blog addressing this issue:

Importing Large Xml Files to SQL Server Using SqlBulkCopy
http://blogs.microsoft.co.il/blogs/kim/archive/2009/06/15/importing-large-xml-files-to-sql-server-using-sqlbulkcopy.aspx

---

Stephanie Lv

TechNet Community Support

Answer 6

Hi Andrey Gordeev,

Using SqlBulkCopy to load large XML files to SQL Server in a faster way, you can take use of a user defined class that implements IDataReader interface in .Net to read through data and save them to SQL Server. Please view this blog addressing this issue:

Importing Large Xml Files to SQL Server Using SqlBulkCopy
http://blogs.microsoft.co.il/blogs/kim/archive/2009/06/15/importing-large-xml-files-to-sql-server-using-sqlbulkcopy.aspx

---

Stephanie Lv

TechNet Community Support

But I dont know structure of XML file which I want to import, I dont think that method would be good for me