Solved

How do I select Parent, Grandparent, Grand-grandparent and so on from XML using LINQ

Posted on 2014-01-28
7
446 Views
Last Modified: 2016-02-10
Hello. I have the following structure of the xml file.  It is a direct reflection of a directory tree. It can be 5,6,7 and more levels deep.

<?xml version="1.0" encoding="utf-8"?>
<Folders xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" FolderName="myTestFdv" FolderCreatedOn="3/2/2013 1:58:35 AM" FolderId="0f828a3a-b361-480c-982c-db4f92d7bb9b">
  <FolderList>
    <Folder FolderName="12345" FolderCreatedOn="3/13/2013 12:25:41 AM" FolderId="fe261d0e-7ca8-4a65-bad0-df9eed59bfc0">
      <FolderList>
        <Folder FolderName="images" FolderCreatedOn="3/13/2013 12:25:41 AM" FolderId="fa9932e1-0ea9-4eef-bd61-4d9e1717fa72">
          <FolderList>
            <Folder FolderName="icons" FolderCreatedOn="3/13/2013 12:25:41 AM" FolderId="79b240d4-6e49-49e0-a569-be667376acbc">
              <FolderList />
              <FileList>
                <File FileName="accdb.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="584322_accdb.jpg" Size="927" />
                <File FileName="bmp.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="338435_bmp.jpg" Size="276" />
                <File FileName="docx.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="428127_docx.jpg" Size="932" />
                <File FileName="gif.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="995346_gif.jpg" Size="822" />
                <File FileName="jpg.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="40293_jpg.jpg" Size="818" />
                <File FileName="png.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="607512_png.jpg" Size="893" />
                <File FileName="pptx.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="174831_pptx.jpg" Size="927" />
                <File FileName="psd.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="742050_psd.jpg" Size="809" />
                <File FileName="tif.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="786896_tif.jpg" Size="768" />
                <File FileName="txt.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="354216_txt.jpg" Size="506" />
                <File FileName="xlsx.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="921434_xlsx.jpg" Size="949" />
              </FileList>
            </Folder>
            <Folder FolderName="thumbnails" FolderCreatedOn="3/13/2013 12:25:41 AM" FolderId="f19b98a3-f76c-4c10-a6c0-b6fd903c4080">
              <FolderList />
              <FileList>
                <File FileName="570153_Untitled.jpg" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="488754_570153_Untitled.jpg" Size="1012" />
                <File FileName="60259_yellow.jpg" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="578446_60259_yellow.jpg" Size="1946" />
                <File FileName="884076_Untitled.jpg" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="145766_884076_Untitled.jpg" Size="1009" />
                <File FileName="941401_photoshop1.jpg" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="712985_941401_photoshop1.jpg" Size="228" />
              </FileList>
            </Folder>
          </FolderList>
          <FileList />
        </Folder>
      </FolderList>
      <FileList />
    </Folder>
    <Folder FolderName="Subfolder1" FolderCreatedOn="3/2/2013 1:58:48 AM" FolderId="7e21b53b-8ff2-45e9-8e11-20dcdd0b9dde">
      <FolderList>
        <Folder FolderName="SubSubFolder1_1" FolderCreatedOn="3/3/2013 9:35:47 PM" FolderId="bf7812df-c98f-4f0e-b348-a2a1eb9d99ef">
          <FolderList />
          <FileList>
            <File FileName="ExcelWorksheet.xlsx" FileCreatedOn="3/3/2013 9:36:01 PM" FileUpdatedOn="3/3/2013 9:36:01 PM" FileType="file" Size="8746" />
          </FileList>
        </Folder>
      </FolderList>
      <FileList />
    </Folder>
    <Folder FolderName="Subfolder2" FolderCreatedOn="3/2/2013 1:58:51 AM" FolderId="015b93c2-9324-46fd-8a0c-8a8a9ecc10a1">
      <FolderList />
      <FileList>
        <File FileName="PPPresentation.pptx" FileCreatedOn="3/2/2013 1:59:19 AM" FileUpdatedOn="3/2/2013 1:59:19 AM" FileType="file" Size="27140" />
        <File FileName="Untitled.jpg" FileCreatedOn="3/7/2013 9:31:59 PM" FileUpdatedOn="3/7/2013 9:31:59 PM" FileType="image" Thumbnail="639073_Untitled.jpg" Size="45956" />
      </FileList>
    </Folder>
    <Folder FolderName="Subfolder3" FolderCreatedOn="3/2/2013 1:58:53 AM" FolderId="f5f20a02-5c27-4e13-a63f-86173431b12b">
      <FolderList>
        <Folder FolderName="SubSubFolder3_1" FolderCreatedOn="3/2/2013 2:05:30 AM" FolderId="f95411b4-47b8-4261-8a2c-9101dc7a6acf">
          <FolderList />
          <FileList />
        </Folder>
        <Folder FolderName="SubSubFolder3_2" FolderCreatedOn="3/2/2013 2:05:33 AM" FolderId="552f12cd-d3d7-49b6-8a4d-f453ae5efb54">
          <FolderList />
          <FileList>
            <File FileName="New Microsoft Access Database.accdb" FileCreatedOn="3/2/2013 2:05:42 AM" FileUpdatedOn="3/2/2013 2:05:42 AM" FileType="file" Size="495616" />
            <File FileName="New Microsoft Excel Worksheet.xlsx" FileCreatedOn="3/2/2013 2:05:46 AM" FileUpdatedOn="3/2/2013 2:05:46 AM" FileType="file" Size="8746" />
            <File FileName="Untitled.bmp" FileCreatedOn="3/7/2013 9:31:33 PM" FileUpdatedOn="3/7/2013 9:31:34 PM" FileType="image" Thumbnail="87635_Untitled.jpg" Size="2239542" />
            <File FileName="yellow.tif" FileCreatedOn="3/8/2013 7:35:36 PM" FileUpdatedOn="3/8/2013 7:35:39 PM" FileType="image" Thumbnail="392459_yellow.jpg" Size="144078324" />
          </FileList>
        </Folder>
      </FolderList>
      <FileList />
    </Folder>
    <Folder FolderName="Subfolder4" FolderCreatedOn="3/2/2013 1:58:59 AM" FolderId="05e39966-9661-493c-80a2-be7dff925828">
      <FolderList />
      <FileList>
        <File FileName="photoshop1.png" FileCreatedOn="3/7/2013 10:11:05 PM" FileUpdatedOn="3/7/2013 10:11:08 PM" FileType="image" Thumbnail="796074_photoshop1.jpg" Size="4760" />
        <File FileName="photoshop1.psd" FileCreatedOn="3/7/2013 10:07:32 PM" FileUpdatedOn="3/7/2013 10:07:32 PM" FileType="file" Size="47654" />
      </FileList>
    </Folder>
    <Folder FolderName="Subfolder5" FolderCreatedOn="3/2/2013 1:58:56 AM" FolderId="5d3e48b3-ecb1-4ec1-b52c-e45bc3dd9c59">
      <FolderList />
      <FileList>
        <File FileName="myAccessDatabase.accdb" FileCreatedOn="3/2/2013 1:59:37 AM" FileUpdatedOn="3/2/2013 1:59:37 AM" FileType="file" Size="495616" />
        <File FileName="myTextDocument.txt" FileCreatedOn="3/2/2013 1:59:41 AM" FileUpdatedOn="3/2/2013 1:59:41 AM" FileType="file" Size="0" />
      </FileList>
    </Folder>
    <Folder FolderName="Subfolder6" FolderCreatedOn="3/5/2013 10:49:08 PM" FolderId="96fd5601-d0ad-4b71-b05a-a9272b440fa3">
      <FolderList />
      <FileList>
        <File FileName="Untitled.png" FileCreatedOn="3/7/2013 9:31:17 PM" FileUpdatedOn="3/7/2013 9:31:17 PM" FileType="image" Thumbnail="379174_Untitled.jpg" Size="33093" />
      </FileList>
    </Folder>
    <Folder FolderName="Subfolder7" FolderCreatedOn="3/5/2013 10:49:19 PM" FolderId="9812d6e3-e535-4ea0-8715-b0f3fc1efaeb">
      <FolderList />
      <FileList />
    </Folder>
  </FolderList>
  <FileList>
    <File FileName="MyRootTextDocument.txt" FileCreatedOn="3/2/2013 1:59:08 AM" FileUpdatedOn="3/2/2013 1:59:08 AM" FileType="file" Size="0" />
    <File FileName="MyRootWordDocument.docx" FileCreatedOn="3/2/2013 1:59:03 AM" FileUpdatedOn="3/2/2013 1:59:03 AM" FileType="file" Size="0" />
  </FileList>
</Folders>

Open in new window



I am using the following query to search for files:

 Dim xdoc As XDocument = XDocument.Load("D:\db.xml")
 Dim searchKey = Request.Form("searchByNameText")
Dim listOfFiles = From sfn In xdoc.Descendants("File") _
                              Where sfn.Attribute("FileName").Value.ToLower().Contains(searchKey) _
                              Select sfn

Open in new window



Now, how can I create a Select statement that also brings back the Path in which the file is located, based on its location in the XML file? Or, in other words, get its parent, grandparent, and so on...
What would be the statement for something like that?
0
Comment
Question by:vituxa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39817158
Hi vituxa;

I think this will give you what you want.

Dim listOfFiles = (From sfn In xdoc.Descendants("File") _
                   Where sfn.Attribute("FileName").Value.ToLower().Contains(searchKey) _
                   Select _
                       sfn.Ancestors("Folder").Aggregate("", Function(path As String, current As XElement) current.Attribute("FolderName").Value & "\" & path) & sfn.Attribute("FileName").Value _
                  ).ToList()

Open in new window

0
 
LVL 1

Author Comment

by:vituxa
ID: 39817926
Thank you Prodigy for that, that is very helpful. However, this is now a string that I am selecting. Is there a way that I can select this string along with the XML Element with all its properties? So that in the list I can show a File, its Location, Size, LastUpdated and so on.

Edit:
I suppose I can create a giant string with all the attributes and to dissect in the loop, but that feels like a hack :)
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39817980
Hi vituxa;

Then you can use this query instead.

Dim listOfFiles = (From sfn In xdoc.Descendants("File") _
                   Where sfn.Attribute("FileName").Value.ToLower().Contains(searchKey) _
                   Select New With _
                   { _
                       .FileNameNode = sfn.Attribute("FileName"), _
                       .FolderAncestors = sfn.Ancestors("Folder") _
                  }).ToList()

Open in new window

0
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39817992
Not sure what you mean by this :

"Is there a way that I can select this string along with the XML Element with all its properties?"

Can you give an example of what you want the output to look like.
0
 
LVL 1

Author Comment

by:vituxa
ID: 39818102
In Other Words how can I select:
 <File FileName="png.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="607512_png.jpg" Size="893" />
And am able to get the Thumbnail Value, and the FileType value and FileUpdatedOn and so on... As well as this file's path string (to which you have kindly provided the above example)
How can I select them Together? So when I loop through the records I can display a path for each one as well as FileType value and FileUpdatedOn and so on...

I hope I am being clear...
0
 
LVL 1

Author Closing Comment

by:vituxa
ID: 39818313
Thank you, Thank you, Thank you!!!!!! I will be asking more stupid questions I hope you'll be able to help!

Thank you Again!
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39818922
Not a problem, glad I was able to help.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

728 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