I have a program to import data from an XML file into a SQL Server table. When i debug the program i get an "Incorrect syntax near 'TRANSACTION'" error as the flow starts creating the table . My XML is structured as :
="1.0"="utf-16"
<EXPORT_HEADER xmlns="http://www.sir.com/SFI/Export/GL_Export/20051005"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.sir.com/SFI/Export/GL_Export/20051005
GL_EXPORT.xsd" total_transactions="4616" total_accnts="2000">
<TRANSACTION business_type_code="DIRECT" term="NB" />
<TRANSACTION business_type_code="DIRECT" term="NB" />
</EXPORT_HEADER>
However when i use the TRANSACTION tag section which contains the actual data i want to commit im getting the syntax error above
DataSet DS = new DataSet();
DS.ReadXml(XMlFile);
DataTable dt = DS.Tables[1];
if (dt.Columns.Count == 0)
dt.ReadXmlSchema(XMlFile);
dt.ReadXml(XMlFile);
This is my code for importing the XML file into Sql server table :
private void btnImport_Click(object sender, EventArgs e)
{
const string XMlFile = @"C:\Users\tshumae\Documents\MyProjects\MyProject\GL_Export_54_201907081053.xml";
if (File.Exists(XMlFile))
{
DataSet DS = new DataSet();
DS.ReadXml(XMlFile);
DataTable dt = DS.Tables[0];
if (dt.Columns.Count == 0)
dt.ReadXmlSchema(XMlFile);
dt.ReadXml(XMlFile);
string Query = CreateTableQuery(dt);
SqlConnection con = new SqlConnection(StrCon);
con.Open();
SqlCommand cmd = new SqlCommand("IF OBJECT_ID('dbo." + dt.TableName + "', 'U') IS NOT NULL DROP TABLE dbo." + dt.TableName + ";", con);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(Query, con);
int check = cmd.ExecuteNonQuery();
if (check != 0)
{
using (var bulkCopy = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
foreach (DataColumn col in dt.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = dt.TableName;
bulkCopy.WriteToServer(dt);
}
MessageBox.Show("Table Created Successfully");
}
con.Close();
}
}
What I have tried:
As a check i tried using EXPORT_HEADER section and the data in the header is successfully committed and table also created:
DataSet DS = new DataSet();
DS.ReadXml(XMlFile);
DataTable dt = DS.Tables[0];
if (dt.Columns.Count == 0)
dt.ReadXmlSchema(XMlFile);
dt.ReadXml(XMlFile);<pre lang="c#">