Aug 19, 2008

Best way to generate XML using LINQ

Hi all,

As we know C# 3.0 with LINQ gives us lots of power and smart coding, here is one more example which uses features of LINQ to generating XML of the abstract type.

I will give you example where you need to display the person details along with the address, here address can be anything like home address, email address, IM address ...

Here are the classes which require to achive our goal.

class Person
public string FirstName { get; set; }
public string LastName { get; set; }
public List<Address> Address = new List<Address>();

class Address
public string AddressValue { get; set; }
public string Type { get; set; }

Person class used to store person data, and Address class which holds the type of address.
Now lets add few details.

Person Person1 = new Person() { FirstName = "Imran", LastName = "Bhadelia" };
Person1.Address.Add(new Address() { Value = "imran.bhadelia[at]", Type = "Email" });
Person1.Address.Add(new Address() { Value = "bhadelia.imran[at]", Type = "AlternativeEmail" });
Person1.Address.Add(new Address() { Value = "bhadelia.imran[at]", Type = "MSNInstanceMessanger" });
Person1.Address.Add(new Address() { Value = "bhadelia.imran", Type = "YahooInstanceMessanger" });

Person Person2 = new Person() { FirstName = "Armaan", LastName = "Bhadelia" };
Person2.Address.Add(new Address() { Value = "armaan.bhadelia[at]", Type = "Email" });
Person2.Address.Add(new Address() { Value = "Sweet Home, 406 Paradise Palace...", Type = "Residence" });
Person2.Address.Add(new Address() { Value = "bhadelia.armaan[at]", Type = "MSNInstanceMessanger" });
Person2.Address.Add(new Address() { Value = "bhadelia.armaan", Type = "YahooInstanceMessanger" });

List<Person> PersonList = new List<Person>();

Here I used one feature of C#3.0, which is Object Initialization Expressions. See the constructor of both class, Its default and while creating object I am setting the value to the fields of class [Smart coding]. I added this two class into List variable, from where LINQ will generate XML which looks like...

<Person FirstName="Imran" LastName="Bhadelia">
<Address Email="imran.bhadelia[at]" />
<Address AlternativeEmail="bhadelia.imran[at]" />
<Address MSNInstanceMessanger="bhadelia.imran[at]" />
<Address YahooInstanceMessanger="bhadelia.imran" />
<Person FirstName="Armaan" LastName="Bhadelia">
<Address Email="armaan.bhadelia[at]" />
<Address Residence="Sweet Home, 406 Paradise Palace..." />
<Address MSNInstanceMessanger="bhadelia.armaan[at]" />
<Address YahooInstanceMessanger="bhadelia.armaan" />

And now LINQ to generate above XML.

var PersonXml =
new XElement("Persons",
from person in PersonList
select (new XElement("Person",
new XAttribute("FirstName", person.FirstName),
new XAttribute("LastName", person.LastName),

from addr in person.Address
select new XElement("Address", new XAttribute(addr.Type, addr.Value)))));


How it works??
Line#2: Create the main element which is Persons
Line#3: Getting single Persons object from PersonList
Line#4: Creating sub element to Persons which is Person
Line#5&6: Creating attributes to show person details.
Line#8: Getting Address of the person from Address collection
Line#9: Creating Address element along with the attribute

Easy? I found its very easy :)

Now if you want to add this information into SQL Server 2005 and higher, you can pass it as string and in your procedure grab the value. There are lots of way to grab value from XML in SQL, you can check this url, it has almost all the operations to XML datatype in SQL.

I created SQL script for specific to this xml.

Person.value('@FirstName[1]', 'VARCHAR(100)') as FirstName,
Person.value('@LastName[1]', 'VARCHAR(100)') as LastName,
Person.value('(Address/@Email)[1]', 'VARCHAR(100)') as Email,
Person.value('(Address/@AlternativeEmail)[1]', 'VARCHAR(100)') as AlternativeEmail,
Person.value('(Address/@Residence)[1]', 'VARCHAR(100)') as Residence,
Person.value('(Address/@MSNInstanceMessanger)[1]', 'VARCHAR(100)') as MSNInstanceMessanger,
Person.value('(Address/@YahooInstanceMessanger)[1]', 'VARCHAR(100)') as YahooInstanceMessanger
FROM @Persons.nodes('/Persons/Person') p(Person)

1 comment:

maulikmbd said...

Good one that was really helpfull!