Solved

C# Linq to Get XML

Posted on 2016-08-10
38
69 Views
Last Modified: 2016-08-12
If I have the below XML.  I want to do 2 things.

First get everything between <key></key> so I can fill up my models in my .NET code.  
Second Delete Key id 123 and everything between

How can I accomplish that with LINQ?

<Reports>
    <personnel></personnel>
    <events>
        <key id="123">
            <Model1>
                <Test1>1</Test1>
                <Test1>2</Test1>
                <Test1>3</Test1>
            </Model1>
            <Object>
                <ABC>
                    <Test1>1</Test1>
                    <Test1>2</Test1>
                    <Test1>3</Test1>
                </ABC>
                <ABC>
                    <Test1>4</Test1>
                    <Test1>5</Test1>
                    <Test1>6</Test1>
                </ABC>
            <Object>
        </key>
    </events>

Open in new window

0
Comment
Question by:CipherIS
  • 22
  • 15
38 Comments
 
LVL 29

Expert Comment

by:anarki_jimbel
ID: 41751818
I'd say the question is not very clear. XML has a bit strange structure. For example, How many <ABC> elements can be under Object? Why we have very identical ABC at all?
What is the model/object you want to fill?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41751830
For ABC there can be 1:n  I wrote model object.  They are coming from my collections in .NET.  That is why they can be 1:n.

When I'm selecting by value it's like a string.  I'm also having problems deleting.  

How is it not clear?
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41752373
Please post the model class you will be filling the XML data with.

In your XML can there be multiple nodes of name "key"?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41752467
Yes, there will be multiple nodes of the name key.  
For the classes as an example.
public class Model
{
    public string ReportName {get; set; } = string.Empty;
    public string FirstName{get; set; } = string.Empty;
    public string LastName{get; set; } = string.Empty;
}
public class Object
{
    public string ObjectName {get; set; } = string.Empty;
    public string Descrtiption {get; set; } = string.Empty;
    public intTotal {get; set; } = 0;
}

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41752500
One more thing can you please post an actual schema of the XML that shows the fields from the class that corresponds to the nodes in the XML file.

Thanks
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41752526
I've attached everything.
Reports.xml
DoorModel.cs
Access.cs
0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 500 total points
ID: 41752841
Hi  CipherIS;

This code sample should do what you need.
using System.Xml.Linq;

// Using XDocument class to process the XML document. Change the parameter to path and file name of XML
var xdoc = XDocument.Load("../../Reports.xml");

// This Linq to XML query will go through the document and create a class I added called DataContainer
// That holds an instance of DoorSelectedModel and AccessEventsMainModel so that only one query is needed
// to parse the XML for each occurence of "key" node. The variable results holds a IEnumerable<DataContainer>
// every member of this collection will hold one "Key" from the XML. Use the code snippet below select statement
// as a template to add the other files.
var results = from d in xdoc.Descendants("key")
              let door = d.Element("DoorSelectedModel").Element("DoorModel")
              let access = d.Element("AccessEventsMainModel")
              select new DataContainer
              {
                  Door = new DoorSelectedModel
                  {
                      Name = door.Element("Name").Value,
                      DoorName = door.Element("DoorName").Value,
                      Total = int.Parse(door.Element("Total").Value)
                  },
                  Access = new AccessEventsMainModel
                  {
                      ReportName = access.Element("ReportName").Value,
                      SelectStatement = access.Element("SelectStatement").Value,
                      StartDate = DateTime.Parse(access.Element("StartDate").Value),
                      EndDate = DateTime.Parse(access.Element("EndDate").Value)
                  }
              };
              
// To delete the key ID="123" you can do the following
// using the same xdoc variable from above
// this will find the key node you wish to delete
var deleteMe = (from d in xdoc.Descendants("key")
                where d.Attribute("ID").Value == "123"
                select d).SingleOrDefault();
if (deleteMe != null)
{
    // Removes the node from the document
    deleteMe.Remove();
    // Save the modified document to the filesystem
    xdoc.Save(@"Z:\VS Projects\MSDN Forum\WindowsFormsApplication3\WindowsFormsApplication3\ReportsUpdated.xml");
}
  


public class DataContainer                           
{                                                    
    public DoorSelectedModel Door { get; set; }      
    public AccessEventsMainModel Access { get; set; }
}                                               

Open in new window

0
 
LVL 1

Author Comment

by:CipherIS
ID: 41752859
Delete is not working.  So, when I type v.Attributes("ID").value the Value gives me an error.  It doesn't exist.  I need to type ToString().

It returns a null.

When I do this.
            var results4 = xdoc.Root.Element(element).Elements("key").Descendants("ID")
                                  .Where(k => k.Value == keyToDelete)
                                  .Select(k => k)
                                  .ToList();

Open in new window

Then do a RemoveAt(0); I do not get an error but nothing deleted.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41752886
I'm getting an error on this line.  
select new DataContainer

Open in new window

I'm putting this code in a class.  Any idea why?  Thanks.
0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 500 total points
ID: 41752890
To your statement, "Delete is not working.  So, when I type v.Attributes("ID").value the Value gives me an error.  It doesn't exist.  I need to type ToString().", The issue is that you used Attributes, which returns a collection where you should be using Attribute which does have a definition for Value. How many key node will have the same ID attribute?
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41752901
To your statement, "I'm getting an error on this line.  I'm putting this code in a class.  Any idea why? " , without seeing  the code no idea. What is the exact error message you are getting?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41752941
Thanks.  The delete works.  
Changed from
            var delete = (from d in xdoc.Element(elemen).Descendants("key")
                          where d.Attribute("ID").Value == keyToDelete
                          select d).SingleOrDefault();

To
            var delete = (from d in xdoc.Descendants("key")
                          where d.Attribute("ID").Value == keyToDelete
                          select d).SingleOrDefault();

Open in new window

The question I have is what happens if the Key has the same name in "Personnel" and "Events" section?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41752950
I've attached a pic of the error.

For the DataContainer it says

The type or namespace name 'DataContainer' could not be found (are you missing a using directive or an assembly reference?)
Error.jpg
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41752958
Also, the DoorModel is a LIst.  Would that matter in the code?
              let door = d.Element("DoorSelectedModel").Element("DoorModel")
              let access = d.Element("AccessEventsMainModel")
              select new DataContainer
              {
                  Door = new DoorSelectedModel
                  {
                      Name = door.Element("Name").Value,
                      DoorName = door.Element("DoorName").Value,
                      Total = int.Parse(door.Element("Total").Value)
                  },
                  Access = new AccessEventsMainModel
                  {
                      ReportName = access.Element("ReportName").Value,
                      SelectStatement = access.Element("SelectStatement").Value,
                      StartDate = DateTime.Parse(access.Element("StartDate").Value),
                      EndDate = DateTime.Parse(access.Element("EndDate").Value)
                  }
              };

Open in new window

0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 500 total points
ID: 41753023
To your question, "The question I have is what happens if the Key has the same name in "Personnel" and "Events" section?", XML document have a structure to them and to access a specific node you need to provide a path to the nodes you need. For example lets say we had the following XML structure.
<Reports>
  <personnel>
    <key ID="PersonelReport"/>                   1
    <Events>
      <key ID="PersonelReport"/>                 2
    </Events>
  </personnel>
  <accessevents>
    <key ID="AccessEventsReport_20160811" />     3

Open in new window

The line marked 1 above path is Reports -> personnel
The line marked 2 above path is Reports -> personnel -> Events
The line marked 3 above path is Reports -> accessevents

When we use a method like xdoc.Descendants("key") it returns a collection of all key nodes. To see if we have the correct one we want to work with you could add a where clause to test who the parent is and if we have the right parent then select it. Like the following were accessevents node is the parent of the key node we want..
var delete = (from d in xdoc.Descendants("key")
              where d.Parent.Name.LocalName == "accessevents"
              where d.Attribute("ID").Value == keyToDelete
              select d).SingleOrDefault();

Open in new window

1
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41753038
To the post #a41752950 Is the namespace of the DataContainer in the same namespace as the two classes it contains. It looks like you are missing a using statement in the file you have the query in.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753050
I found a code sample online  I'm trying to break it down and just get one object.  It returns a null.  Do you see anything wrong with the code?
            IEnumerable<AccessEventsMainModel> results = (from d in xdoc.Descendants("key")
                                              let access = d.Element("AccessEventsMainModel")
                                              where d.Attribute("ID").Value == sKey
                                              select new AccessEventsMainModel()
                                              {
                                                ReportName = access.Element("ReportName").Value,
                                                SelectStatement = access.Element("SelectStatement").Value,
                                                StartDate = DateTime.Parse(access.Element("StartDate").Value),
                                                EndDate = DateTime.Parse(access.Element("EndDate").Value)
                                              });

Open in new window

0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753053
What would the using statement be for the DataContainer?  Ah, the DataContainer is the Object model, correct?
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41753056
To your question, "Also, the DoorModel is a LIst.  Would that matter in the code?", is it a List within the the same key node or different key node?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:CipherIS
ID: 41753062
In the XML it is repeated multiple times.  It was generated as such because it came from a list.  I would want all the DoorModels in the XML to be pulled in as List<DoorModel>
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41753077
Are you looking for all key nodes to be in one list and all DoorModel to be in another list without having a DataContainer just two seperate lists?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753144
That is  what  I'm  planning to do. Have  them in 2 separate list.Door Model is a List<>  Other is just an  object. I figured I can pull  the data for each one.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753185
Ok, close.  The top one works.  Second one doesn't.  Any idea? When I added ToList() it helped.
//This works
                var access = (from d in xdoc.Descendants("key")
                                       let access = d.Element("AccessEventsMainModel")
                                       where d.Attribute("ID").Value == sKey
                                       select new AccessMainModel()
                                       {
                                           ReportName = access.Element("ReportName").Value,
                                           SelectStatement = access.Element("SelectStatement").Value,
                                           StartDate = DateTime.Parse(access.Element("StartDate").Value),
                                           EndDate = DateTime.Parse(access.Element("EndDate").Value)
                                       }).ToList();
//Object not set error
                var door = (from d in xdoc.Descendants("key")
                                    let doormodel = d.Element("DoorModel")
                                    where d.Attribute("ID").Value == sKey
                                    select new DoorSelectedModel()
                                    {
                                        Name = doormodel.Element("Name").Value,
                                        DoorName = doormodel.Element("DoorName").Value,
                                        Total = Int32.Parse(doormodel.Element("Total").Value)
                                    }).ToList();

Open in new window

0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753197
Ok, last one. Im getting data but it is just getting one and not both doors. How do I get both values from the XmL?
var door = (from d in xdoc.Descendants("key")
			let doormodel = d.Element("DoorSelectedModel").Element("DoorModel")
			where d.Parent.Name.LocalName == element
			where d.Attribute("ID").Value == sKey
			select new DoorSelectedModel()
			{
				Name = doormodel.Element("Name").Value,
				DoorName = doormodel.Element("DoorName").Value,
				Total = Int32.Parse(doormodel.Element("Total").Value)
			}).ToList();

Open in new window

0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41753201
I re-wrote the query to do it in two calls they both work and return a list of their respective objects.
var xdoc = XDocument.Load("../../Reports.xml");

var doors = (from d in xdoc.Descendants("DoorModel")
             select new DoorSelectedModel
             {                               
                 Name = d.Element("Name").Value,
                 DoorName = d.Element("DoorName").Value,
                 Total = int.Parse(d.Element("Total").Value)
             }).ToList();

var access = (from d in xdoc.Descendants("AccessEventsMainModel")
              select new AccessEventsMainModel
              {
                  ReportName = d.Element("ReportName").Value,
                  SelectStatement = d.Element("SelectStatement").Value,
                  StartDate = DateTime.Parse(d.Element("StartDate").Value),
                  EndDate = DateTime.Parse(d.Element("EndDate").Value)
              }).ToList();

Open in new window

1
 
LVL 1

Author Comment

by:CipherIS
ID: 41753203
I like it separate.  It will help me in the long run.  The door is only picking up one of the XML's nodes  though.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753205
Yours works.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753206
But I need to get it by key because there could be multiple records in the file.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41753213
I don't see a field in either of the classes to store the ID of the ley node?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753217
In the file I attached the ID is in the key.  It's <Key ID=123>  It's an attribute.  Variable sKey below is how I'm passing it.
This works fine but I'm only getting one record.

var access = (from d in xdoc.Descendants("key")
                                       let access = d.Element("AccessEventsMainModel")
                                       where d.Attribute("ID").Value == sKey
                                       select new AccessMainModel()
                                       {
                                           ReportName = access.Element("ReportName").Value,
                                           SelectStatement = access.Element("SelectStatement").Value,
                                           StartDate = DateTime.Parse(access.Element("StartDate").Value),
                                           EndDate = DateTime.Parse(access.Element("EndDate").Value)
                                       }).ToList();

Open in new window

This works but returns only one Door

var door = (from d in xdoc.Descendants("key")
			let doormodel = d.Element("DoorSelectedModel").Element("DoorModel")
			where d.Parent.Name.LocalName == element
			where d.Attribute("ID").Value == sKey
			select new DoorSelectedModel()
			{
				Name = doormodel.Element("Name").Value,
				DoorName = doormodel.Element("DoorName").Value,
				Total = Int32.Parse(doormodel.Element("Total").Value)
			}).ToList();

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41753219
This only returns one because of this line, where d.Attribute("ID").Value == sKey, most likely only one has the value of sKey?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753220
I want to make sure that I'm getting all the doors for <key ID=123> and not <key ID=234>  make sense?  THat is why I put that there.  The file will have more than one <key>
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41753224
Can you post the XML file that you are using because the one that you posted before only has one door.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753226
Here it is.
Reports.xml
0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 500 total points
ID: 41753237
try it like this.
var doors = (from d in xdoc.Descendants("key")
             where d.Attribute("ID").Value == sKey
             from dm in d.Element("DoorSelectedModel").Elements("DoorModel")
             select new DoorSelectedModel
             {
                 Name = dm.Element("Name").Value,
                 DoorName = dm.Element("DoorName").Value,
                 Total = int.Parse(dm.Element("Total").Value)
             }).ToList()

Open in new window

0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753240
Seems to work.  I typed what I saw.  It's exact.  I don't know what the difference is.  I copied and pasted and seems to work.  Should the parent be in the code to make sure it's coming from the correct node?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41753242
I added the parent code.  Where d.Parent.Name.LocalName = element (element being a variable).

Going to continue testing.  Wonder why the other code that I typed didn't work.  

THanks
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41753243
I used your XML you last posted and it returned two records. The only thing I can think of is that you may be using the wrong value for sKey. You have posted many times this,  <key ID=123>, but in the file it is this, <key ID="AccessEventsReport_20160811">.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now