Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Reading XML documents using LINQ to XML

MSSQLTips author Edwin Sarmiento By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Language Integrated Query LINQ

Problem
In a recent tip on Language Integrated Query (LINQ), you have described how it can be used as a query language extension to both VB.NET and C#. As XML has become a popular means to store data for ease of interoperability, how do we use LINQ to query XML data?

Solution
Language-Integrated Query for XML (LINQ to XML) allows XML data to be queried by using the standard query operators as well as tree-specific operators that provide XPath-like navigation through descendants, ancestors, and siblings. It simplifies working with XML data without having to resort to using additional language syntax like XPath or XQuery. You can use LINQ to XML to perform LINQ queries over XML that you retrieve from the file system, from a remote web service, or from an in-memory XML content. This tip will only focus on querying XML using LINQ from an XML file - the Customers.xml file. You can download the XML file so you can follow along with the tip.

Create a simple LINQ project
We will start by following the steps outlined in the Introduction to LINQ tip. We will name our Visual Studio project ConsoleLINQtoXML and use the C# language. Add the following namespaces with the using directive. The System.Xml.Linq namespace contains the classes for LINQ to XML

//Include the LINQ to XML namespaces
using System.Xml; //namespace to deal with XML documents
using System.Xml.Linq; //namespace to deal with LINQ to XML classes

Next, we will add the Customers.xml file in our project. You can also opt to store the XML file in a directory in your file system but for simplicity's sake, we'll just include it in our project. Click on the Project menu and select Add Existing Item... from Visual Studio.

Select the Customers.xml file and click Add. The XML file should be added in your project.

Right-click on the Customers.xml file from the Solutions Explorer and select Properties. This should bring up the Properties window..

On the Properties window for the Customers.xml file, click the Copy to Output Directory option and select Copy always from the drop-down list

Now we are ready to write LINQ queries to read our XML file. Let's start writing some code inside the static void Main(string[] args).

// Create the query
var custs = from c in XElement.Load("Customers.xml").Elements("Customers")
          select c ;

// Execute the query
foreach (var customer in custs)
{
     Console.WriteLine(customer);
}

//Pause the application
Console.ReadLine();

You will notice that there isn't much difference in the query from the Introduction to LINQ tip except for the fact that it now uses the XElement class which represents an XML element. The Load method of the XElement class simply loads the Customers.xml file into the XElement class. The Elements method returns a filtered collection of the child elements of this element - the Customers element -in the XML document. This just demonstrates how powerful LINQ is as you are using the same language query constructs on collections and now on XML documents. You can even implement sorting, filtering and grouping as you normally would in LINQ. You can insert a where clause in the query above to filter your results.

where c.Element("Country").Value == "Italy"

Your output will look like this when you run your project in Visual Studio. You can press F5 or click on Debug - Start Debugging in Visual Studio

Doing the same thing without LINQ is more complex as you still need to traverse the nodes explicitly to access the elements and their values. You can check out the Microsoft KB article that demonstrates how to do this in C# and compare the differences.

Next Steps

You have seen how easy it is to read XML documents with LINQ. You can use LINQ to read XML documents from the file system, from a remote web service, or from an in-memory XML content. We will look at using LINQ to XML to manipulate XML data in future tips.



Last Update: 6/24/2008


About the author
MSSQLTips author Edwin Sarmiento
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, July 18, 2008 - 7:20:19 AM - mreynol5 Read The Tip

Gentlemen,

I have just found your forum and I have a Ling to XML question relating to the above post.  How can I return just the values associated with the nodes and not include the child node start and end tags?  I then need to load the values (there are two in each parent node) into temporary storage such as an array.

<testcenter>

       <testcentercode>12345</testcentercode>

      <zipcode>01945</zipcode>

There could be up to 200 "<testcenter> nodes".  I need to load just the values of the two children of each into an array.

 


Friday, July 18, 2008 - 10:10:40 AM - bass_player Read The Tip

It's just a matter of reading the value of the element name.  Here's a sample code based on the article. Notice that I have commented out the Fax element since there are a few records with no Fax element. Therefore, if you need to iterate thru your records in the XML document, you need to implement your own error handling mechanism.  Paste the code inside the static void Main(string[ ] args)

// Create the query

var custs = from c in XElement.Load("Customers.xml").Elements("Customers")

select new

{

CustomerID = c.Element("CustomerID").Value,

CompanyName = c.Element("CompanyName").Value,

ContactName = c.Element("ContactName").Value,

ContactTitle = c.Element("ContactTitle").Value,

Address = c.Element("Address").Value,

City = c.Element("City").Value,

PostalCode = c.Element("PostalCode").Value,

Country = c.Element("Country").Value,

Phone = c.Element("Phone").Value,

//Fax = c.Element("Fax").Value

};

// Execute the query

foreach (var customer in custs)

{

Console.WriteLine("CustomerID:" + customer.CustomerID);

Console.WriteLine("Company Name:" + customer.CompanyName);

Console.WriteLine("Contact Name:" + customer.ContactName);

Console.WriteLine("Contact Title:" + customer.ContactTitle);

Console.WriteLine("Address:" + customer.Address);

Console.WriteLine("City:" + customer.City);

Console.WriteLine("Postal Code:" + customer.PostalCode);

Console.WriteLine("Country:" + customer.Country);

Console.WriteLine("Phone:" + customer.Phone);

//Console.WriteLine("Fax:" + customer.Fax);

Console.WriteLine("========================");

}

 

//Pause the application

Console.ReadLine();


Saturday, June 19, 2010 - 2:20:54 AM - wicked Read The Tip
Hello Your sample are very useful and easy to understand

However I have a problem on dealing 2 same elment with 2 diff attribute value.

Ex. <phone type:"home"> 123-456-7 </phone>
     <phone type:"work">0934-4567-99 </phone>

How can I get the phone  number   for home using the Xelement.value ?

Thanks in advance

Monday, August 06, 2012 - 9:13:56 AM - Mohit Read The Tip

Hello All

I am struggling to get XML -> LINQ working for  below sample - not sure if this is simple or  I am being dumb?

 

My requirment is to get Traid id's for eac RikSubject ?

 Any pointers wuld be great help.

<Tabs> 

<RiskSubjectGroup ="ldn00c77170" Key="true"/> </Properties>

<RiskSubject Version="23">

<Properties>

    <Mon Value="abc://dn00c77170?version=23" Key="true"/>

   <TradeId Value="ldn00c77170" Key="false"/>

</Properties>

 

</RiskSubject>

</RiskSubjectGroup>

<RiskSubjectGroup ="ldn00c88180" Key="true"/> </Properties

>

<RiskSubject Version="23">

<Properties>

    <Mon Value="ldn00c88180?version=23" Key="true"/>

   <TradeId Value="ldn00c88180" Key="false"/>

</Properties>

 

</RiskSubject>

</RiskSubjectGroup>

<Tabs> 


Wednesday, November 28, 2012 - 2:58:08 PM - blah blah Read The Tip

If you're going to re-publish an old article from 2008, at least make sure the links work!

http://www.geocities.com/edwin_sarmiento/Customers.xml



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.