Click here to Skip to main content
15,881,709 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 :
XML
<?xml version="1.0" encoding="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
C#
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 :
C#
private void btnImport_Click(object sender, EventArgs e)
    {
        const string XMlFile = @"C:\Users\tshumae\Documents\MyProjects\MyProject\GL_Export_54_201907081053.xml";
       // string XMlFile = txtFilePath.Text;
        if (File.Exists(XMlFile))
        {
            // Conversion Xml file to DataTable
            DataSet DS = new DataSet();
            DS.ReadXml(XMlFile);

            DataTable dt = DS.Tables[0];

            if (dt.Columns.Count == 0)
                dt.ReadXmlSchema(XMlFile);
                dt.ReadXml(XMlFile);


            // Creating Query for Table Creation
            string Query = CreateTableQuery(dt);
            SqlConnection con = new SqlConnection(StrCon);
            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)
            {
            // Copy Data from DataTable to Sql Table
            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:
C#
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#">
Posted
Updated 29-Aug-19 3:27am
Comments
Afzaal Ahmad Zeeshan 29-Aug-19 5:11am    
By correcting the syntax, I assume. :-)

And boy, you are exposing your system to a heavy SQL Injection, not from one but two regions! :D
Maciej Los 29-Aug-19 5:17am    
Wow! It looks very suspicious:
SqlCommand cmd = new SqlCommand("IF OBJECT_ID('dbo." + dt.TableName + "', 'U') IS NOT NULL DROP TABLE dbo." + dt.TableName + ";", con);

and might be very dangerous.

1 solution

Deserialize to a class. You'll have better luck.

XmlSerializer.Deserialize Method (System.Xml.Serialization) | Microsoft Docs[^]

// NOTE: Generated code may require at least .NET Framework 4.5 or .NET Core/Standard 2.0.
      /// <remarks/>
      [System.SerializableAttribute()]
      [System.ComponentModel.DesignerCategoryAttribute( "code" )]
      [System.Xml.Serialization.XmlTypeAttribute( AnonymousType = true, Namespace = "http://www.sir.com/SFI/Export/GL_Export/20051005" )]
      [System.Xml.Serialization.XmlRootAttribute( Namespace = "http://www.sir.com/SFI/Export/GL_Export/20051005", IsNullable = false )]
      public partial class EXPORT_HEADER {

         private EXPORT_HEADERTRANSACTION[] tRANSACTIONField;

         private ushort total_transactionsField;

         private ushort total_accntsField;

         /// <remarks/>
         [System.Xml.Serialization.XmlElementAttribute( "TRANSACTION" )]
         public EXPORT_HEADERTRANSACTION[] TRANSACTION {
            get {
               return this.tRANSACTIONField;
            }
            set {
               this.tRANSACTIONField = value;
            }
         }

         /// <remarks/>
         [System.Xml.Serialization.XmlAttributeAttribute()]
         public ushort total_transactions {
            get {
               return this.total_transactionsField;
            }
            set {
               this.total_transactionsField = value;
            }
         }

         /// <remarks/>
         [System.Xml.Serialization.XmlAttributeAttribute()]
         public ushort total_accnts {
            get {
               return this.total_accntsField;
            }
            set {
               this.total_accntsField = value;
            }
         }
      }

      /// <remarks/>
      [System.SerializableAttribute()]
      [System.ComponentModel.DesignerCategoryAttribute( "code" )]
      [System.Xml.Serialization.XmlTypeAttribute( AnonymousType = true, Namespace = "http://www.sir.com/SFI/Export/GL_Export/20051005" )]
      public partial class EXPORT_HEADERTRANSACTION {

         private string business_type_codeField;

         private string termField;

         /// <remarks/>
         [System.Xml.Serialization.XmlAttributeAttribute()]
         public string business_type_code {
            get {
               return this.business_type_codeField;
            }
            set {
               this.business_type_codeField = value;
            }
         }

         /// <remarks/>
         [System.Xml.Serialization.XmlAttributeAttribute()]
         public string term {
            get {
               return this.termField;
            }
            set {
               this.termField = value;
            }
         }
      }
 
Share this answer
 
Comments
Tshumore 29-Aug-19 11:48am    
Will it be a case of reading the XML data via the class then , if so how. im not entirely sure how to incorporate the deserialized object in my implementation.

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