[ASP.NET]Use LINQ to XML to construct XML document from database records

  • Article

LINQ to XML provides us powerful component to manipulate XML data in .NET application. One common issue in ASP.NET application is retriving some data records from database and expose them to client user in XML format. Before LINQ is available, we need to loop through each data records via ADO.NET api and then use System.Xml component to construct the XML document and flush document to ASP.NET page response.

 

Now, with the help of LINQ to XML, we can achieve this via very simple code. e.g.

 C# code

protected void btnGetXml_Click(object sender, EventArgs e)

        {

             using(SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True"))

             {

                 conn.Open();

                 SqlCommand comm = new SqlCommand("select CategoryID, CategoryName, Description from Categories", conn);

                 IDataReader reader = comm.ExecuteReader();

                 DataTable dt = new DataTable();

                 dt.Load(reader);

                 var xmlDoc = new XDocument(

                     new XElement("rootElement",

                         new XElement("categories",

        from c in dt.AsEnumerable()

                             select new XElement("category",

                                        new XAttribute("CategoryID", c["CategoryID"]),

                                        new XAttribute("CategoryName",c["CategoryName"]),

                                        new XAttribute("Description",c["Description"])

                                 )

                        )

                     )

                 );

                 Response.Clear();

                 Response.ContentType = "text/xml";

                 xmlDoc.Save(Response.Output, SaveOptions.None);

                 Response.End();

                 reader.Close();

                 comm.Dispose();

             }

      }

 

VB.NET code(with xml literal support):

Protected Sub btnGetXml_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnGetXml.Click

        Dim conn As New SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")

        Try

            conn.Open()

            Dim comm As New SqlCommand("select CategoryID, CategoryName, Description from Categories", conn)

            Dim reader As IDataReader = comm.ExecuteReader()

   Dim dt As New DataTable()

            dt.Load(reader)

            Dim xmldoc = _

                <rootElement>

                    <categories>

                        <%= From r In dt.AsEnumerable() _

                            Select <category CategoryID=<%= r("CategoryID") %> CategoryName=<%= r("CategoryName") %> Description=<%= r("Description") %>/> _

                        %>

                    </categories>

                </rootElement>

            Response.Clear()

            Response.ContentType = "text/xml"

            xmldoc.Save(Response.Output)

            Response.End()

            comm.Dispose()

            reader.Close()

        Catch ex As Exception

        Finally

            conn.Close()

        End Try

     

  End Sub

For more info about LINQ to XML, here are the MSDN links online:

 

#.NET Language-Integrated Query for XML Data
https://msdn.microsoft.com/en-us/library/bb308960.aspx

 

#Programming Guide (LINQ to XML)
https://msdn.microsoft.com/en-us/library/bb387087.aspx