XML to database

[jvanamali]

Here is my problem,

 

my client has several customers who send him xml files. I need to dump all those xml files data into an existing table (insert and update depending on key)

 

The problem the all xml do not have the same structure, i.e there are a few common that every xml has , but they are others that the customer may or may not fill depending on his choice.

 

For Example

 

Customer 1 XML might have the following

<root>
<product>
<PrimaryField1>111</PrimaryField1>
<MandatoryField2>aaa<MandatoryField2>
<MandatoryField3>bbb</MandatoryField3>
<ExtraField1>extrainfo</ExtraField1>
<ExtraField3>extrainfo</ExtraField3>
</product>
<product>
<PrimaryField1>222</PrimaryField1>
<MandatoryField2>aaa<MandatoryField2>
<MandatoryField3>bbb</MandatoryField3>
<ExtraField1>extrainfo</ExtraField1>
<ExtraField3>extrainfo</ExtraField3>
</product>
</root>

 

Customer 2 XML might have the following

<root>
<product>
<PrimaryField1>555</PrimaryField1>
<MandatoryField2>aaa<MandatoryField2>
<MandatoryField3>bbb</MandatoryField3>
<ExtraField1>extrainfo</ExtraField1>
<ExtraField3>extrainfo</ExtraField3>
</product>
<product>
<PrimaryField1>666</PrimaryField1>
<MandatoryField2>aaa<MandatoryField2>
<MandatoryField3>bbb</MandatoryField3>
<ExtraField2>sf</ExtraField2>
<ExtraField4>extrainfo</ExtraField4>
</product>
</root>

 

The table has all the fields the primary fields,mandatory fields and all the other fields for which customer(s) may or may not give data.

 

What is the best possible to do this.

 

I tried generating an insert statement depending on the xml structure but that made the system slow, it was almost impossible to work with large xml files. some of the xml files have more than million records.

 

I use dotnet 2.0, in case you want to know.

[Diesel]

Which database server?

 

With Sql Server 2005+, you can bulk load xml files with OPENROWSET.

 

http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

[jvanamali]

It is Mysql database.