XML file data to perminent table in database

Question

x
hyd
y
hyd1
i want to store the above xml data in backend(sqlserver2008) by automatically create new table
like table as folllows
name     address
x                hyd
y                hyd1
plz help me.

Naresh Babu Gopavaram · Answer

yes but we follow another way and your answer is also too good ...... thank u

Micke Blomqvist · Answer

Did you get your question answered? If so, please check my post as accepted answer so others can use the thread, as well.

Micke Blomqvist · Answer

I hope you can follow the thought in the uploaded code. It's based from a web application, but you could use it in a Windows forms solution with just a minor changes.
Please note the comments regarding dynamic datatypes, you need to finetune this your self. Let me explain the code a bit:
1. I copied your xml data and fixed the bad formatting. ( wasn't closed correctly)
2. Read data using DataSet ReadXml method.
3. I use a HashTable to loop thru the columns in the datatable to create a base for keeping track of the max length of the fields.
4. Set the MaxLenght using the stored values in the HashTable. (This is a must to create a varchar column in SQL Server)
5. Loop thru the column once again to create the "CREATE TABLE" statement.
5. SELECT CASE from the information_schema.tables table to check whether the table exists or not
6. If the table doesn't exists, use the CREATE TABLE statement to create the table
7. If there are rows in the table, use SqlBulkInsert to load the data from the DataTable to the SQL Server table.
8. All set!
Please let me know if you have any problems!