How to insert an XML file into a column

Question

I'm trying to upload a user profile settings xml(as a whole file into a column of xml data type) into my postgres database using c# , here is my code,

var connstring = System.Configuration.ConfigurationManager.ConnectionStrings["pgcon"].ConnectionString;

using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
{
    conn.Open();

    XmlDocument xmlDoc = new XmlDocument();
    xmlDoc.Load("G:\\new repo\\setting xmls\\settings.xml");
    //workinq query insert
    //  string sql2 = string.Format(@"INSERT INTO Public.""UserDetails"" (id, usercode, address) VALUES ('dd', 'code', '1001')");

    NpgsqlCommand dbcmd = conn.CreateCommand();
    try
    {                       
        string sql = string.Format("SET SEARCH_PATH to Public;");                          
        string sql0 = string.Format(@"INSERT INTO Public.""UserProfile"" (setting, userstatus, userstatusdescription, id) VALUES ('{0}', true, 'active', 'ddd');", xmlDoc);

        dbcmd.CommandText = sql + sql0;
        dbcmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw;
    }
}

But , it is uploading as System.Xml.XmlDocument in the table , I need save it as an xml .

Any help would be appreciated.

Answer 1

As mentioned in the comments you can do the following

string myXml = XDocument.Load("G:\\new repo\\setting xmls\\settings.xml").ToString()

Then

string sql0 = string.Format(@"INSERT INTO Public.""UserProfile"" (setting, userstatus, userstatusdescription, id) VALUES ('{0}', true, 'active', 'ddd');", myXml);

Please use @ahammadalipk answer as this was originally posted to solve the XML upload issue the OP was having. @ahammadalipk answer covers the issue which the OP had with Sql Injection

Answer 2

To avoid the issue LITTLE BOBBY TABLES!(making error when xml data contains single quotes and injection error), instead of adding parameters directly in the query , you can use NpgsqlParameter class.

instead of this ,

string sql0 = string.Format(@"INSERT INTO Public.""UserProfile"" (setting, userstatus, userstatusdescription, id) VALUES ('{0}', true, 'active', 'ddd');", myXml);

do this ,

NpgsqlParameter p = new NpgsqlParameter("@myXml", NpgsqlTypes.NpgsqlDbType.Xml);
                    p.Value = myXml;  

complete code ,

 using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
            {
                conn.Open();    
                string myXml = XDocument.Load("G:\\new repo\\setting xmls\\settings.xml").ToString();                     
                NpgsqlCommand dbcmd = conn.CreateCommand();
                try
                {

                    string sql = string.Format("SET SEARCH_PATH to Public;");                     
                    string sql0 = string.Format(@"INSERT INTO Public.""UserProfile"" (setting, userstatus, userstatusdescription, id) VALUES (@myXml, true, 'active', 'ddd');");                    
                    dbcmd.CommandText = sql + sql0;
                    NpgsqlParameter p = new NpgsqlParameter("@myXml", NpgsqlTypes.NpgsqlDbType.Xml);
                    p.Value = myXml;                   
                    dbcmd.Parameters.Add(p);
                    dbcmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {

                    throw;
                }



            } 

Happy coding! for the late comers .