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


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
http://msdn.microsoft.com/en-us/library/bb308960.aspx


 


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


 


 


 

Comments (0)