Insert XML data in SQL Table using C# and VB.Net in ASP.Net

Last Reply 4 months ago By pandeyism

Posted 4 months ago

Hello, I am trying to load an XML file to a sql table from C #, what I do is create a stored procedure in the database and send an XML type parameter, but I always receive this error

C#

public void executeLoadXML()
{
    string StrCon = "Connection String";
    bool success = false;
    string xml = @"<?xml version=""1.0"" encoding=""utf-8""?>  
            <soap:Envelope xmlns:soap=""http://www.w3.org/2003/05/soap-envelope"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">
                <soap:Body>
                    <RetrieveUserByLoginResponse xmlns=""http://tempuri.org/"">
                        <RetrieveUserByLoginResult>
                        <Error_Number>0</Error_Number>
                        <User>
                            <Id_SegUSU>00000001</Id_SegUSU>
                            <Id_CLIE>0000002</Id_CLIE>
                            <Id_CLIESede>0000000003</Id_CLIESede>
                            <Id_SegPerfil>0000004</Id_SegPerfil>
                            <Login>******</Login>    
                            <Nombres>*****</Nombres>
                            <Apellidos>******</Apellidos>
                            <DirectorioActivo>false</DirectorioActivo>
                            <Id_SegUSU_Crea>0000005</Id_SegUSU_Crea>
                            <FechaHoraCrea>2019-04-24T12:14:10</FechaHoraCrea>
                            <Id_SegUSU_Modifica>0000006</Id_SegUSU_Modifica>
                            <FechaHoraModifica>2019-05-07T12:03:13</FechaHoraModifica>
                            <Activo>true</Activo>
                            <Mail/>
                        </User>
                        </RetrieveUserByLoginResult>
                    </RetrieveUserByLoginResponse>
                </soap:Body>
            </soap:Envelope>";

    try
    {

        using (SqlConnection con = new SqlConnection(StrCon))
        {
            con.Open();
            using (SqlCommand command = new SqlCommand("pruebaImport_XMLtoSql", con))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 3600;
                SqlParameter parameter1 = command.Parameters.Add("@xml", SqlDbType.Xml);
                parameter1.Value = xml.ToString();
                command.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

 

The problem seems to be in this line "<? Xml version =" "1.0" "encoding =" "utf-8" "?> But, please, do not ask me to delete it because I can not do it because I am consuming a web services and the response always bring this "<? xml version = "" 1.0 "" encoding = "" UTF-8 ""?>, I only made this example so that they understand me.

Thank you

This question does not have replies that have been liked.