ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Insert XML data in SQL Table using C# and VB.Net in ASP.Net<p>Hi <a class="username" rel="fredo1094"> fredo1094</a>,</p> <p>Refer below sample.</p> <p><span style="text-decoration: underline;"><strong>HTML</strong></span></p> <pre class="brush: html">&lt;asp:Button ID=&#34;Button1&#34; Text=&#34;Upload XML&#34; runat=&#34;server&#34; OnClick=&#34;UploadXML&#34; /&gt; &lt;br /&gt; &lt;asp:GridView ID=&#34;gvCustomers&#34; runat=&#34;server&#34; AutoGenerateColumns=&#34;false&#34;&gt; &lt;Columns&gt; &lt;asp:BoundField DataField=&#34;CustomerId&#34; HeaderText=&#34;CustomerId&#34; /&gt; &lt;asp:BoundField DataField=&#34;Name&#34; HeaderText=&#34;Name&#34; /&gt; &lt;asp:BoundField DataField=&#34;Country&#34; HeaderText=&#34;Country&#34; /&gt; &lt;/Columns&gt; &lt;/asp:GridView&gt;</pre> <p><span style="text-decoration: underline;"><strong>Namespaces</strong></span></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.Data; using System.Configuration; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Xml;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.Data Imports System.Configuration Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports System.Xml</pre> <p><span style="text-decoration: underline;"><strong>Code</strong></span></p> <p><strong>C#</strong></p> <pre class="brush: csharp">protected void UploadXML(object sender, EventArgs e) { string xml = @&#34;&lt;?xml version=&#39;1.0&#39; encoding=&#39;utf-8&#39;?&gt; &lt;Customers&gt; &lt;Customer Id =&#39;1&#39;&gt; &lt;Name&gt;John Hammond&lt;/Name&gt; &lt;Country&gt;United States&lt;/Country&gt; &lt;/Customer&gt; &lt;Customer Id = &#39;2&#39;&gt; &lt;Name&gt;Mudassar Khan&lt;/Name&gt; &lt;Country&gt;India&lt;/Country&gt; &lt;/Customer&gt; &lt;Customer Id =&#39;3&#39;&gt; &lt;Name&gt;Suzanne Mathews&lt;/Name&gt; &lt;Country&gt;France&lt;/Country&gt; &lt;/Customer&gt; &lt;Customer Id =&#39;4&#39;&gt; &lt;Name&gt;Robert Schidner&lt;/Name&gt; &lt;Country&gt;Russia&lt;/Country&gt; &lt;/Customer&gt; &lt;/Customers&gt;&#34;; string constr = ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { SqlCommand cmd = new SqlCommand(&#34;InsertXML&#34;); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; SqlParameter parameter1 = cmd.Parameters.Add(&#34;@xml&#34;, SqlDbType.Xml); parameter1.Value = new SqlXml(new XmlTextReader(xml, XmlNodeType.Document, null)); con.Open(); cmd.ExecuteNonQuery(); con.Close(); cmd = new SqlCommand(&#34;SELECT CustomerId, Name,Country FROM CustomerDetails&#34;, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); gvCustomers.DataSource = dt; gvCustomers.DataBind(); } }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Protected Sub UploadXML(ByVal sender As Object, ByVal e As EventArgs) Dim xml As String = &#34;&lt;?xml version=&#39;1.0&#39; encoding=&#39;utf-8&#39;?&gt; &#34; xml += &#34;&lt;Customers&gt;&#34; xml += &#34;&lt;Customer Id =&#39;1&#39;&gt;&#34; xml += &#34;&lt;Name&gt;John Hammond&lt;/Name&gt;&#34; xml += &#34;&lt;Country&gt;United States&lt;/Country&gt;&#34; xml += &#34;&lt;/Customer&gt;&#34; xml += &#34;&lt;Customer Id = &#39;2&#39;&gt;&#34; xml += &#34;&lt;Name&gt;Mudassar Khan&lt;/Name&gt;&#34; xml += &#34;&lt;Country&gt;India&lt;/Country&gt;&#34; xml += &#34;&lt;/Customer&gt;&#34; xml += &#34;&lt;Customer Id =&#39;3&#39;&gt;&#34; xml += &#34;&lt;Name&gt;Suzanne Mathews&lt;/Name&gt;&#34; xml += &#34;&lt;Country&gt;France&lt;/Country&gt;&#34; xml += &#34;&lt;/Customer&gt;&#34; xml += &#34;&lt;Customer Id =&#39;4&#39;&gt;&#34; xml += &#34;&lt;Name&gt;Robert Schidner&lt;/Name&gt;&#34; xml += &#34;&lt;Country&gt;Russia&lt;/Country&gt;&#34; xml += &#34;&lt;/Customer&gt;&#34; xml += &#34;&lt;/Customers&gt;&#34; Dim constr As String = ConfigurationManager.ConnectionStrings(&#34;constr&#34;).ConnectionString Using con As SqlConnection = New SqlConnection(constr) Dim cmd As SqlCommand = New SqlCommand(&#34;InsertXML&#34;) cmd.Connection = con cmd.CommandType = CommandType.StoredProcedure Dim parameter1 As SqlParameter = cmd.Parameters.Add(&#34;@xml&#34;, SqlDbType.Xml) parameter1.Value = New SqlXml(New XmlTextReader(xml, XmlNodeType.Document, Nothing)) con.Open() cmd.ExecuteNonQuery() con.Close() cmd = New SqlCommand(&#34;SELECT CustomerId, Name,Country FROM CustomerDetails&#34;, 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</pre> <p><span style="text-decoration: underline;"><strong>Screenshot</strong></span></p> <p><img src="https://imgur.com/wiNbZ9y.gif" alt="" width="336" height="233" /></p>https://www.aspforums.net:443/Threads/184246/Insert-XML-data-in-SQL-Table-using-C-and-VBNet-in-ASPNet/https://www.aspforums.net:443/Threads/184246/Insert-XML-data-in-SQL-Table-using-C-and-VBNet-in-ASPNet/Fri, 24 May 2019 07:57:52 GMT