How do I resolve incorrect syntax error when parsing an XML document

Question

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 28-Aug-19 21:49pm

Tshumore

Updated 29-Aug-19 3:27am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gerry Schmitz · Answer 1 · 2019-08-29T03:28:00

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;
            }
         }
      }