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

Last Reply 5 months ago By pandeyism

Posted 6 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

You are viewing reply posted by: pandeyism 5 months ago.
Posted 5 months ago

Hi fredo1094,

Refer below sample.

HTML

<asp:Button ID="Button1" Text="Upload XML" runat="server" OnClick="UploadXML" />
<br />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Xml

Code

C#

protected void UploadXML(object sender, EventArgs e)
{
    string xml = @"<?xml version='1.0' encoding='utf-8'?>  
                    <Customers>
                        <Customer Id ='1'>
                        <Name>John Hammond</Name>
                        <Country>United States</Country>
                        </Customer>
                        <Customer Id = '2'>
                        <Name>Mudassar Khan</Name>
                        <Country>India</Country>
                        </Customer>
                        <Customer Id ='3'>
                        <Name>Suzanne Mathews</Name>
                        <Country>France</Country>
                        </Customer>
                        <Customer Id ='4'>
                        <Name>Robert Schidner</Name>
                        <Country>Russia</Country>
                        </Customer>
                    </Customers>";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        SqlCommand cmd = new SqlCommand("InsertXML");
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter parameter1 = cmd.Parameters.Add("@xml", SqlDbType.Xml);
        parameter1.Value = new SqlXml(new XmlTextReader(xml, XmlNodeType.Document, null));
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        cmd = new SqlCommand("SELECT CustomerId, Name,Country FROM CustomerDetails", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        gvCustomers.DataSource = dt;
        gvCustomers.DataBind();
    }
}

VB.Net

Protected Sub UploadXML(ByVal sender As Object, ByVal e As EventArgs)
    Dim xml As String = "<?xml version='1.0' encoding='utf-8'?>  "
    xml += "<Customers>"
    xml += "<Customer Id ='1'>"
    xml += "<Name>John Hammond</Name>"
    xml += "<Country>United States</Country>"
    xml += "</Customer>"
    xml += "<Customer Id = '2'>"
    xml += "<Name>Mudassar Khan</Name>"
    xml += "<Country>India</Country>"
    xml += "</Customer>"
    xml += "<Customer Id ='3'>"
    xml += "<Name>Suzanne Mathews</Name>"
    xml += "<Country>France</Country>"
    xml += "</Customer>"
    xml += "<Customer Id ='4'>"
    xml += "<Name>Robert Schidner</Name>"
    xml += "<Country>Russia</Country>"
    xml += "</Customer>"
    xml += "</Customers>"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim cmd As SqlCommand = New SqlCommand("InsertXML")
        cmd.Connection = con
        cmd.CommandType = CommandType.StoredProcedure
        Dim parameter1 As SqlParameter = cmd.Parameters.Add("@xml", SqlDbType.Xml)
        parameter1.Value = New SqlXml(New XmlTextReader(xml, XmlNodeType.Document, Nothing))
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        cmd = New SqlCommand("SELECT CustomerId, Name,Country FROM CustomerDetails", con)
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        gvCustomers.DataSource = dt
        gvCustomers.DataBind()
    End Using
End Sub

Screenshot