How to get xml documnet columns data types in c#

Question

Hi

 

I have one xml file that xml file when i upload that xml data and columns need to create and store in sql server

hear i am using in string create table like that in for lopp columns i added but in this code what happend na that columns data type all are consider varchar(max) i want to get xml file data types and nedd to create table and store that data hear i am use my code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Xml;

public partial class Default2 : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constring"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void UploadXML(object sender, EventArgs e)
        {
          string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string filePath = Server.MapPath("~/Uploads/") + fileName;
        FileUpload1.SaveAs(filePath);
        string xml = File.ReadAllText(filePath);
        string XMlFile = filePath;
        //if (XMlFile=="")
        //    {
                // Conversion Xml file to DataTable
                DataTable dt = CreateDataTableXML(XMlFile);
                if (dt.Columns.Count == 0)
                    dt.ReadXml(XMlFile);
                // Creating Query for Table Creation
                string Query = CreateTableQuery(dt);
                con.Open();
                // Deletion of Table if already Exist
                SqlCommand cmd = new SqlCommand("IF OBJECT_ID('dbo." + dt.TableName + "', 'U') IS NOT NULL DROP TABLE dbo." + dt.TableName + ";", con);
                cmd.ExecuteNonQuery();
                // Table Creation
                cmd = new SqlCommand(Query, con);
                int check = cmd.ExecuteNonQuery();
                if (check != 0)
                {
                    using (SqlTransaction transaction =
                   con.BeginTransaction())
                    {
                        SqlBulkCopy sbc = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepIdentity, transaction);
                        foreach (DataColumn col in dt.Columns)
                        {
                            sbc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                        }
                        sbc.BulkCopyTimeout = 600;
                        sbc.DestinationTableName = dt.TableName;
                        sbc.WriteToServer(dt);
                    }
                   // MessageBox.Show("Table Created Successfully");
                }
                con.Close();
            //}+
        }
        // Getting Table Name as Per the Xml File Name
        public string GetTableName(string file)
        {
            FileInfo fi = new FileInfo(file);
            string TableName = fi.Name.Replace(fi.Extension, "");
            return TableName;
        }
        // Getting Query for Table Creation
        public string CreateTableQuery(DataTable table)
        {
            string sqlsc = "CREATE TABLE " + table.TableName + "(";
            //progressBar1.Maximum = table.Columns.Count;
           // progressBar1.Value = 0;
            for (int i = 0; i < table.Columns.Count; i++)
            {
                sqlsc += "[" + table.Columns[i].ColumnName + "]";
                string columnType = table.Columns[i].DataType.ToString();
                switch (columnType)
                {
                    case "System.Int32":
                        sqlsc += " int ";
                        break;
                    case "System.Int64":
                        sqlsc += " bigint ";
                        break;
                    case "System.Int16":
                        sqlsc += " smallint";
                        break;
                    case "System.Byte":
                        sqlsc += " tinyint";
                        break;
                    case "System.Decimal":
                        sqlsc += " decimal ";
                        break;
                    case "System.DateTime":
                        sqlsc += " datetime ";
                        break;
                    case "System.String":
                    default:
                        sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
                        break;
                }
                if (table.Columns[i].AutoIncrement)
                    sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
                if (!table.Columns[i].AllowDBNull)
                    sqlsc += " NOT NULL ";
                sqlsc += ",";
               // Progress();
            }
            return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
        }
        // Conversion Xml file to DataTable
        public DataTable CreateDataTableXML(string XmlFile)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(XmlFile);
            DataTable Dt = new DataTable();
            try
            {
                Dt.TableName = GetTableName(XmlFile);
                XmlNode NodoEstructura = doc.DocumentElement.ChildNodes.Cast<XmlNode>().ToList()[0];
               // progressBar1.Maximum = NodoEstructura.ChildNodes.Count;
               //progressBar1.Value = 0;
                foreach (XmlNode columna in NodoEstructura.ChildNodes)
                {
                    Dt.Columns.Add(columna.Name, typeof(String));
                    //Progress();
                }
                XmlNode Filas = doc.DocumentElement;
              //  progressBar1.Maximum = Filas.ChildNodes.Count;
               // progressBar1.Value = 0;
                foreach (XmlNode Fila in Filas.ChildNodes)
                {
                    List<string> Valores = Fila.ChildNodes.Cast<XmlNode>().ToList().Select(x => x.InnerText).ToList();
                    Dt.Rows.Add(Valores.ToArray());
                   // Progress();
                }
            }
            catch (Exception ex)
            {
            }
            return Dt;
        }
        // Show Progress Bar
    }
}