Microsoft & .NET.NETConverting Data to XML with LINQ to XML

Converting Data to XML with LINQ to XML

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

If the other person injures you, you may forget the injury; but if you injure him you will always remember.
–Kahlil Gibran

Introduction

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=WYOMINGSQLEXPRESS;” + _
“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( _
connectionString)
connection.Open()
Dim command As SqlCommand = New SqlCommand(sql, connection)
Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
adapter.Fill(table)
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”)))))

Console.WriteLine(doc)
Console.ReadLine()
End Sub
End Module

Listing 2: The output from Listing 1.

  <Customers>
    <Customer CustomerID="ALFKI">
      <CompanyName>Alfreds Futterkiste</CompanyName>
      <ContactName>Maria Anders</ContactName>
      <ContactTitle>Sales Representative</ContactTitle>
      <Address>Obere Str. 57</Address>
      <City>Berlin</City>
      <Region></Region>
      <PostalCode>12209</PostalCode>
      <Country>Germany</Country>
      <Phone>030-0074321</Phone>
      <Fax>030-0076545</Fax>
    </Customer>
    <Customer CustomerID="ANATR">
      <CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
      <ContactName>Ana Trujillo</ContactName>
      <ContactTitle>Owner</ContactTitle>
      <Address>Avda. de la Constitución 2222</Address>
      <City>México D.F.</City>
      <Region></Region>
      <PostalCode>05021</PostalCode>
      <Country>Mexico</Country>
      <Phone>(5) 555-4729</Phone>
      <Fax>(5) 555-3745</Fax>
    </Customer>
    <Customer CustomerID="ANTON">
      <CompanyName>Antonio Moreno Taquería</CompanyName>
      <ContactName>Antonio Moreno</ContactName>
      <ContactTitle>Owner</ContactTitle>
      <Address>Mataderos  2312</Address>
      <City>México D.F.</City>
      <Region></Region>
      <PostalCode>05023</PostalCode>
      <Country>Mexico</Country>
      <Phone>(5) 555-3932</Phone>
      <Fax></Fax>
    </Customer>
    <Customer CustomerID="AROUT">
      <CompanyName>Around the Horn</CompanyName>
      <ContactName>Thomas Hardy</ContactName>
      <ContactTitle>Sales Representative</ContactTitle>
      <Address>120 Hanover Sq.</Address>
      <City>London</City>
      <Region></Region>
      <PostalCode>WA1 1DP</PostalCode>
      <Country>UK</Country>
      <Phone>(171) 555-7788</Phone>
      <Fax>(171) 555-6750</Fax>
    </Customer>
    <Customer CustomerID="BERGS">
      <CompanyName>Berglunds snabbköp</CompanyName>
      <ContactName>Christina Berglund</ContactName>
      <ContactTitle>Order Administrator</ContactTitle>
      <Address>Berguvsvägen  8</Address>
      <City>Luleå</City>
      <Region></Region>
      <PostalCode>S-958 22</PostalCode>
      <Country>Sweden</Country>
      <Phone>0921-12 34 65</Phone>
      <Fax>0921-12 34 67</Fax>
    </Customer>
    <Customer CustomerID="BLAUS">
      <CompanyName>Blauer See Delikatessen</CompanyName>
      <ContactName>Hanna Moos</ContactName>
      <ContactTitle>Sales Representative</ContactTitle>
      <Address>Forsterstr. 57</Address>
      <City>Mannheim</City>
      <Region></Region>
      <PostalCode>68306</PostalCode>
      <Country>Germany</Country>
      <Phone>0621-08460</Phone>
      <Fax>0621-08924</Fax>
    </Customer>
    <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>
      <City>Strasbourg</City>
      <Region></Region>
      <PostalCode>67000</PostalCode>
      <Country>France</Country>
      <Phone>88.60.15.31</Phone>
      <Fax>88.60.15.32</Fax>
    </Customer>
    <Customer CustomerID="BOLID">
      <CompanyName>Bólido Comidas preparadas</CompanyName>
      <ContactName>Martín Sommer</ContactName>
      <ContactTitle>Owner</ContactTitle>
      <Address>C/ Araquil, 67</Address>
      <City>Madrid</City>
      <Region></Region>
      <PostalCode>28023</PostalCode>
      <Country>Spain</Country>
      <Phone>(91) 555 22 82</Phone>
      <Fax>(91) 555 91 99</Fax>
    </Customer>
    <Customer CustomerID="BONAP">
      <CompanyName>Bon app'</CompanyName>
      <ContactName>Laurence Lebihan</ContactName>
      <ContactTitle>Owner</ContactTitle>
      <Address>12, rue des Bouchers</Address>
      <City>Marseille</City>
      <Region></Region>
      <PostalCode>13008</PostalCode>
      <Country>France</Country>
      <Phone>91.24.45.40</Phone>
      <Fax>91.24.45.41</Fax>
    </Customer>
    <Customer CustomerID="BOTTM">
      <CompanyName>Bottom-Dollar Markets</CompanyName>
      <ContactName>Elizabeth Lincoln</ContactName>
      <ContactTitle>Accounting Manager</ContactTitle>
      <Address>23 Tsawassen Blvd.</Address>
      <City>Tsawassen</City>
      <Region>BC</Region>
      <PostalCode>T2F 8M4</PostalCode>
      <Country>Canada</Country>
      <Phone>(604) 555-4729</Phone>
      <Fax>(604) 555-3745</Fax>
    </Customer>
  </Customers>

The XML document is constructed on the line that begins with Dim doc as XDocument. The return type of the single statement is an XDocument (an XML document). The XDocument constructor is overloaded. One of its definitions is to accept a ParamArray of objects. The idea here is that it is unknown how many actual arguments any particular document might require. In the statement in Listing 1 the XDocument is constructed with two arguments, an XDeclaration and an XElement. The XDeclaration contains the XML document’s metadata, and the XElement defines the root node, “Customers”. Each of the table rows represents a Customer as defined, and each Customer is a child node of the root, Customers. Only data that is part of the collection needs to be in the LINQ query.

The individual customer nodes are constructed using a LINQ query and represent ParamArray arguments to the XElement that represents the Customers node. The From clause of the LINQ query defines the range variable cust. The range variable is like the iterator variable in a for loop. The In clause contains the data source, in this case a collection of table rows. The Order By clause sorts the results by CompanyName.

Finally, the projection–the Select clause–creates an XML element for each column in the Customer table. The XAttribute class will create an attribute from the CustomerID of the Customer row, and all of the other elements are child nodes of the Customer node. It’s a bit of a complicated statement, but the statement that creates the XDocument is a single statement. If you look at the output in Listing 2 you will see that the organization of the construction statement mirrors the output.

Summary

One of capabilities of LINQ to XML is functional construction of an XML document. By using class constructors in the System.Xml.Linq namespace and LINQ queries you can iterate over data, like a database table, and construct an XML document in a single statement.

Biography

Paul Kimmel is the VB Today columnist for CodeGuru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book Professional DevExpress ASP.NET Controls (from Wiley) now available on Amazon.com and fine bookstores everywhere. Look for his upcoming book Teach Yourself the ADO.NET Entity Framework in 24 Hours (from Sams). You may contact him for technology questions at pkimmel@softconcepts.com. Paul Kimmel is a Technical Evangelist for Developer Express, Inc, and you can ask him about Developer Express at paulk@devexpress.com and read his DX blog at http://community.devexpress.com/blogs/paulk.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories