Populate (Bind) ASP.Net GridView with XML generated using SQL query using C# and VB.Net

Hi nauna,

Check this example. Now please take its reference and correct your code.

HTML

<asp:GridView runat="server" ID="gvCustomers" />

Namespaces

C#

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

VB.Net

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        DataSet ds = new DataSet();
        ds.ReadXml(new StringReader("<root>" + GetXmlString() + "</root>"));
        gvCustomers.DataSource = ds;
        gvCustomers.DataBind();
    }
}

private string GetXmlString()
{
    string xml = "";
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT * FROM Customers FOR XML AUTO,ELEMENTS";
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        con.Open();
        cmd.Connection = con;
        xml = cmd.ExecuteScalar().ToString();
        con.Close();
    }

    return xml;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Dim ds As DataSet = New DataSet()
        ds.ReadXml(New StringReader("<root>" & GetXmlString() & "</root>"))
        gvCustomers.DataSource = ds
        gvCustomers.DataBind()
    End If
End Sub

Private Function GetXmlString() As String
    Dim xml As String = ""
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT * FROM Customers FOR XML AUTO,ELEMENTS"
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        con.Open()
        cmd.Connection = con
        xml = cmd.ExecuteScalar().ToString()
        con.Close()
    End Using

    Return xml
End Function

Screenshot