How to get xml documnet columns data types in c#

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().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().ToList().Select(x => x.InnerText).ToList();
Dt.Rows.Add(Valores.ToArray());
// Progress();
}
}
catch (Exception ex)
{
}
return Dt;
}
// Show Progress Bar
}
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Mario Scheeren5y ago
Same question you already asked once: And still you need the XSD,
and your coding style ist still crappy ...

If you have no XSD, you may Bruteforce findout the type by TryParsing the values of each column to different types ... but this would be a hack ...

If you do not understand what I am talking about, maybe ask ...
0