Click here to Skip to main content
14,979,837 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how can I Fastest read data and insert from xml file sql server 2008
xml file size is over 50 Mb

can help me any one ?????
Posted
Comments
Deviprasad Das 16-Jan-14 2:14am
   
Reading from XML, does it include displaying the data in a web page?

Sending the XML to SQL storedprocedure as a XML parameter and iterating in sql server is a best option for your problem(this avoids multiple database connection open & close)

The following will help you,

http://stackoverflow.com/questions/10384745/upsert-on-sql-server-table-from-xml[^]
   
v3
Hello, in addition to solution 1: you can also create a CLR stored procedure or user defined function which accepts your xml as parameter, implements XmlReader class (or other classes) and inserts data to your database tables desired. For more information about xml performance please see Chapter 9 — Improving XML Performance[^]
If it is impossible by CLR disabled directive due to your server policy you should create stand-alone app which does the same as CLR stored procedure or user defined function.
   
Use MS SQL Server Integration Services [SSIS]. It is very easy and does the work efficiently.

Regards,
Dinesh Kumar.V.
   
Guies I solved this quetion ..........
and i think this is the fastest way

//create dataset which will have all data
 DataSet ds1 = new DataSet("XML data1");
 
 //load schema first, this is updates schema will attch with email ds1.ReadXmlSchema(@"D:\userdocs\Ken_XML\FAC_SSIS_Identities\output.xsd");
 
 ds1.EnforceConstraints = false;
 
 //load data in dataset
 ds1.ReadXml(@"<XML file>");
 
 //connect to sql server and appropriate database see if table already present, if not present create new table after that load data from dataset to sql using bulkcopy
 try
 {
 SqlConnection conn = new SqlConnection("Server=localhost;Initial Catalog= XML_data;Trusted_Connection=True;");
 conn.Open();
 foreach (DataTable dt in ds1.Tables)
 {
 //check if table is present or not
 string exists = null;
 try
 {
 SqlCommand cmd = new SqlCommand("SELECT * FROM sysobjects where name = '" + dt.TableName + "'", conn);
 exists = cmd.ExecuteScalar().ToString();
 }catch (Exception exce)
 {
 exists = null;
 }
 // selecting each column of the datatable to create a table in the database
 Console.WriteLine("Bulk Insert Started table:" + dt.TableName);
 SqlBulkCopy bulk = new SqlBulkCopy(conn);
 bulk.DestinationTableName = "[" + dt.TableName + "]";
 foreach (DataColumn dc in dt.Columns)
 {
 bulk.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
 string type = "";
 //Getting right data type for column is very importatnt as it can create problem later if wrong data type is chosen
 //for mapping we are using below key value pair
 //Dictionary<string, string> typemappings = new Dictionary<string, string>();
 //typemappings.Add("Decimal", "Numeric");
 //typemappings.Add("String", "varchar(255)");
 //typemappings.Add("Int32", "Int");
 typemappings.TryGetValue(dc.DataType.FullName.Split('.')[1], out type);
 if (type == null)
 type = "varchar(255)";
 if (exists == null)
 {
 SqlCommand createtable = new SqlCommand("CREATE TABLE [" + dt.TableName + "] ([" + dc.ColumnName + "] " + type + ")", conn);
 createtable.ExecuteNonQuery();
 exists = dt.TableName;
 }
 else
 {
 try
 {
 SqlCommand addcolumn = new SqlCommand("ALTER TABLE [" + dt.TableName + "] ADD [" + dc.ColumnName + "] " + type, conn);
 addcolumn.ExecuteNonQuery();
 }catch (Exception ex2) { }
 }
 }
 //load data in sql
 bulk.WriteToServer(dt);
 Console.WriteLine("Bulk Insert completed table:" + dt.TableName);
 }
 conn.Close()
 }catch (Exception ex)
 {
 Console.WriteLine(ex.Message.ToString() + "\n" + ex.StackTrace.ToString());
 }
 finally
 {
 ds1.Clear();
 ds1.Dispose();
 }
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900