Load XML containing 300 elements for each record into db

Question

So I have this large XML file containing 300+ elements for each record and I need to insert these records into a database table. The name of the nodes in the XML file are the same as the column names in the db. And they're all strings. Is there an automatic way to map them and insert the data or will I have to write out lines of code mapping each column to each element in XML file? Using SQL Server 2005.

Answer 1

What is the database engine are you using?

SQL server could import the data using bulk load, or import / export wizard, etc.

Microsoft KB, + Many Other Techniques

Answer 2

I'd agree that with SQL server, using bulk load or SQL/XML is a good option.

If the database isn't SQL server and doesn't support xml natively, another option would be to use a basic xslt to transform the xml into csv or tsv; most databases can import delimited files.

Of course, another question would be: why have you got 300 columns?

Answer 3

300 columns? Not the way I read the question, but then again my english isn't that good. :) As far as I know there's no auto mapping between Xml and database table/columns. But it shouldn't be that hard to write a dynamic update? Get all the column names (nodenames) and add them to a string (column1, column2 etc.) and then iterate over the xml to produce the values (node1.Value node2.Value etc.). Or if you're using Sql Server 2005/2008 use the bulk insert.

Answer 4

Well I've decided to use an SSIS package to do it. Thanks.