How to manage special characters in XML element using C#

Question

How to manage special characters in XML element using C#

BeUnique 2,332

This is very simple question and something reasonable.

I am handling XML file through C# code. each XML file contains millions of records.

Inside the XML tag, some value contains special characters like "–". But, this doesn't allow to process in XML for inserting to SQL.

It's showing error like "illegal xml character"

How to handle such type of special characters....?

Example XML Tag below

<Employee EmpId="528496">
			<NAME>JAMES–SS</NAME>
</Employee>

"DRUMS–SS" ==> "–"(Special character) and doesn't allow to process in SQL.

How to handle such type of special characters....?

Anonymous

2023-06-20T09:40:09.3766667+00:00

Hi @BeUnique , Welcome to Microsoft Q&A. Isn't using CDATA useful? What is the sample code you are currently using?

BeUnique 2,332

Hi @Anonymous ,

Thanks for your reply.

pls. find below sample code (C#.NET)

string filePath = @"C:\Temp\Test1.xml";
            string xml = File.ReadAllText(filePath);
            string constr = ConfigurationManager.ConnectionStrings["Constr"].ConnectionString;
            using (SqlConnection con1 = new SqlConnection(constr))
            {
                using (SqlCommand cmd1 = new SqlCommand("InsertXML"))
                {
                    cmd1.Connection = con1;
                    cmd1.CommandType = CommandType.StoredProcedure;
                    cmd1.Parameters.AddWithValue("@xml_string", xml);
                    con1.Open();
                    cmd1.ExecuteNonQuery();
                    con1.Close();
                }
            }

Anonymous

Please show your InsertXML.

Nothing went wrong in my attempts.

User's image

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;

namespace _6_21_X
{
    internal class Program
    {

        static void Main(string[] args)
        {
            string filePath = @"XXXX.xml";
            string xml = File.ReadAllText(filePath);
            Console.WriteLine(xml);
            string insertXmlProcedure = @"
DECLARE @xmlString NVARCHAR(MAX);
SET @xmlString = N'" + xml + @"';

INSERT INTO DBO.TTTable (id, XML)
VALUES (1, @xmlString);
";


            string constr = ConfigurationManager.ConnectionStrings["Constr"].ConnectionString;
            using (SqlConnection con1 = new SqlConnection(constr))
            {
                con1.Open();

                using (SqlCommand cmd1 = new SqlCommand(insertXmlProcedure, con1))
                {
                    cmd1.ExecuteNonQuery();
                }

                con1.Close();
            }

            Console.WriteLine("XML data inserted into the database.");
            Console.ReadLine();
        }
    }
}

enter image description here

BeUnique 2,332 Reputation points

2023-08-04T13:13:29.1633333+00:00

It's working. Thanks...

1 answer

Your answer

Anonymous

2023-06-20T09:40:09.3766667+00:00

Hi @BeUnique , Welcome to Microsoft Q&A. Isn't using CDATA useful? What is the sample code you are currently using?
BeUnique 2,332 Reputation points

2023-06-20T09:45:06.06+00:00

Hi @Anonymous ,

Thanks for your reply.

pls. find below sample code (C#.NET)

string filePath = @"C:\Temp\Test1.xml"; string xml = File.ReadAllText(filePath); string constr = ConfigurationManager.ConnectionStrings["Constr"].ConnectionString; using (SqlConnection con1 = new SqlConnection(constr)) { using (SqlCommand cmd1 = new SqlCommand("InsertXML")) { cmd1.Connection = con1; cmd1.CommandType = CommandType.StoredProcedure; cmd1.Parameters.AddWithValue("@xml_string", xml); con1.Open(); cmd1.ExecuteNonQuery(); con1.Close(); } }
Anonymous

2023-06-21T09:09:48.72+00:00

Please show your InsertXML.

Nothing went wrong in my attempts.

using System; using System.Configuration; using System.Data.SqlClient; using System.IO; namespace _6_21_X { internal class Program { static void Main(string[] args) { string filePath = @"XXXX.xml"; string xml = File.ReadAllText(filePath); Console.WriteLine(xml); string insertXmlProcedure = @" DECLARE @xmlString NVARCHAR(MAX); SET @xmlString = N'" + xml + @"'; INSERT INTO DBO.TTTable (id, XML) VALUES (1, @xmlString); "; string constr = ConfigurationManager.ConnectionStrings["Constr"].ConnectionString; using (SqlConnection con1 = new SqlConnection(constr)) { con1.Open(); using (SqlCommand cmd1 = new SqlCommand(insertXmlProcedure, con1)) { cmd1.ExecuteNonQuery(); } con1.Close(); } Console.WriteLine("XML data inserted into the database."); Console.ReadLine(); } } }
BeUnique 2,332 Reputation points

2023-08-04T13:13:29.1633333+00:00

It's working. Thanks...

Answer 1

BeUnique 2,332

Hi @Anonymous ,

Thanks for your reply.

I used below code for handling illegal characters.

 public static string JustAscii(string source)
        {
            return new string(source.Where(c => c <= sbyte.MaxValue).ToArray());
        }

It's working now....Thanks for your support and help..

Share via

How to manage special characters in XML element using C#

1 answer

Your answer