April 23, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Converting Data to XML with LINQ to XML

  • October 5, 2009
  • By Paul Kimmel
  • Send Email »
  • More Articles »
If the other person injures you, you may forget the injury; but if you injure him you will always remember.
--Kahlil Gibran


In Malcolm Gladwell's Outliers: The Story of Success, Gladwell talks about the magic number of 10,000 hours. Gladwell's book is worth reading, and I don't want to oversimplify, but the basic idea is that success, represented by expertise takes 10,000 hours of effort to attain. For the most part if one puts in the effort then success will follow. A fun story in the book is about the Beatles. The Beatles played in the Red Light district in Hamburg, Germany for six or seven hours every night, sometimes for weeks on end. Having spent the better part of two years working the Red Light district in Germany as a Military Policeman, I wonder if the various Beatles didn't find a mixture of lascivious glee and mild loathing working around beautiful, available, naked women. That's not the point. The point is that long before the Beatles hit the main stream they had paid their dues. They had perfected their stage craft by playing all of those long nights and months in Hamburg. The Beatles had probably surpassed the magic 10,000 hours before anyone except Hamburgers really knew who they were.

By reading this article and trying the example you can learn about LINQ to XML, Functional Construction, and put two more hours into your expert bin.

Reviewing Functional Construction

The first time I heard the term functional construction was when I was writing the LINQ to XML section of my book LINQ Unleashed: for C# from Sams. From the MSDN site on Functional Construction, Microsoft defines functional construction as the ability to create an XML tree in a single statement. I think of functional construction as the ability to construct one thing from another with method calls. Or to borrow from "Crazy" Joe Clark, functional construction is transmogrification. (I wonder if someone at Microsoft thought of using the term transmogrification.)

The way functional construction is used in .NET, is that you invoke a constructor call on a class that represents an element and it yields the desired output. The CodeDOM namespaces uses this approach. You invoke method calls and the CodeDOM will generate an element of code. For example, if you create a CodeTypeDeclaration object then the CodeDOM can generate a class, struct, interface, or enumeration based on the target language. In this way through function calls you can construct code.

The System.Xml.Linq namespace contains classes that represents elements of an XML document, including XDocument, XElement, XAttribute, XDeclaration, and some other basic kinds of classes that represent XML content. By invoking these constructors with element names and values you can construct an XML document (in a single statement).

Constructing XML with LINQ to XML

The objective is to take some data that you have and construct an XML document from it. You might want to use the XML as a data island, bind it to a component like Developer Express' ASPxMenu, or use the XML document as a means of persisting data externally.

To use functional construction with LINQ your source data needs to be an enumerable form. This means rows in a DataTable will work, LINQ to SQL data will work, and generic collections and lists will work. The System.Xml.Linq namespace contains the classes that will emit the various XML elements, and LINQ to XML will iterate over the source data. For each object in the source data you can construct the various XML elements in the pattern you desire. This literally means you can simply convert table rows into XML elements in a flat structure or you can create a hierarchical tree of data combining fields or using some elements as attributes and some elements as child nodes.

The example in Listing 1 uses plain vanilla ADO.NET and LINQ to DataTables to read the Northwind Customers table. Functional construction and LINQ to XML is used to construct the XML document representing the customers. The select statement is arbitrarily defined to return 5 rows to keep the XML output-in Listing 2-short. An explanation of the LINQ to XML code and the Functional Construction is provided after Listing 2.

Listing 1: The LINQ to XML code that constructs the XML document in Listing 2.

  Imports System.Linq
  Imports System.Xml.Linq
  Imports System.Data.SqlClient
  Imports System.Data
  Module Module1
    <STAThread()> _
    Sub Main()
      Dim connectionString As String = _
        "Data Source=WYOMING\SQLEXPRESS;" + _
        "Initial Catalog=Northwind;Integrated Security=True"
      Dim sql As String = _
        "SELECT TOP 10 * FROM Customers"
      Dim table As DataTable = New DataTable()
      Using connection As SqlConnection = New SqlConnection( _
        Dim command As SqlCommand = New SqlCommand(sql, connection)
        Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
      End Using
      Dim doc As XDocument = New XDocument( _
        New XDeclaration("1.0", "utf-8", "true"), _
          New XElement("Customers", _
            From cust As DataRow In table.Rows _
            Order By cust("CompanyName") _
            Select New XElement("Customer", _
              New XAttribute("CustomerID", cust("CustomerID")), _
              New XElement("CompanyName", cust("CompanyName")), _
              New XElement("ContactName", cust("ContactName")), _
              New XElement("ContactTitle", cust("ContactTitle")), _
              New XElement("Address", cust("Address")), _
              New XElement("City", cust("City")), _
              New XElement("Region", cust("Region")), _
              New XElement("PostalCode", cust("PostalCode")), _
              New XElement("Country", cust("Country")), _
              New XElement("Phone", cust("Phone")), _
              New XElement("Fax", cust("Fax")))))
    End Sub
  End Module

Listing 2: The output from Listing 1.

    <Customer CustomerID="ALFKI">
      <CompanyName>Alfreds Futterkiste</CompanyName>
      <ContactName>Maria Anders</ContactName>
      <ContactTitle>Sales Representative</ContactTitle>
      <Address>Obere Str. 57</Address>
    <Customer CustomerID="ANATR">
      <CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
      <ContactName>Ana Trujillo</ContactName>
      <Address>Avda. de la Constitución 2222</Address>
      <City>México D.F.</City>
      <Phone>(5) 555-4729</Phone>
      <Fax>(5) 555-3745</Fax>
    <Customer CustomerID="ANTON">
      <CompanyName>Antonio Moreno Taquería</CompanyName>
      <ContactName>Antonio Moreno</ContactName>
      <Address>Mataderos  2312</Address>
      <City>México D.F.</City>
      <Phone>(5) 555-3932</Phone>
    <Customer CustomerID="AROUT">
      <CompanyName>Around the Horn</CompanyName>
      <ContactName>Thomas Hardy</ContactName>
      <ContactTitle>Sales Representative</ContactTitle>
      <Address>120 Hanover Sq.</Address>
      <PostalCode>WA1 1DP</PostalCode>
      <Phone>(171) 555-7788</Phone>
      <Fax>(171) 555-6750</Fax>
    <Customer CustomerID="BERGS">
      <CompanyName>Berglunds snabbköp</CompanyName>
      <ContactName>Christina Berglund</ContactName>
      <ContactTitle>Order Administrator</ContactTitle>
      <Address>Berguvsvägen  8</Address>
      <PostalCode>S-958 22</PostalCode>
      <Phone>0921-12 34 65</Phone>
      <Fax>0921-12 34 67</Fax>
    <Customer CustomerID="BLAUS">
      <CompanyName>Blauer See Delikatessen</CompanyName>
      <ContactName>Hanna Moos</ContactName>
      <ContactTitle>Sales Representative</ContactTitle>
      <Address>Forsterstr. 57</Address>
    <Customer CustomerID="BLONP">
      <CompanyName>Blondesddsl père et fils</CompanyName>
      <ContactName>Frédérique Citeaux</ContactName>
      <ContactTitle>Marketing Manager</ContactTitle>
      <Address>24, place Kléber</Address>
    <Customer CustomerID="BOLID">
      <CompanyName>Bólido Comidas preparadas</CompanyName>
      <ContactName>Martín Sommer</ContactName>
      <Address>C/ Araquil, 67</Address>
      <Phone>(91) 555 22 82</Phone>
      <Fax>(91) 555 91 99</Fax>
    <Customer CustomerID="BONAP">
      <CompanyName>Bon app'</CompanyName>
      <ContactName>Laurence Lebihan</ContactName>
      <Address>12, rue des Bouchers</Address>
    <Customer CustomerID="BOTTM">
      <CompanyName>Bottom-Dollar Markets</CompanyName>
      <ContactName>Elizabeth Lincoln</ContactName>
      <ContactTitle>Accounting Manager</ContactTitle>
      <Address>23 Tsawassen Blvd.</Address>
      <PostalCode>T2F 8M4</PostalCode>
      <Phone>(604) 555-4729</Phone>
      <Fax>(604) 555-3745</Fax>

Page 1 of 2

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Sitemap | Contact Us

Rocket Fuel