Solved

Help with comparing 2 xml files and retreiving all records in file2 where values in NSN field are available in File1 using VB.NET

Posted on 2016-11-30
6
65 Views
Last Modified: 2016-12-01
Hi,

How do I retrieve all records in File2 if the values in NSN field exist in File1? For example if:

File1.xml:

   <Root>
   <Table1>
   <ID>1</ID>
   <Receiver>BEL</Receiver>
   <Donor>USA</Donor>
   <NSN>111</NSN>
   </Table1>
   <Table1>
   <ID>2</ID>
   <Receiver>FRA</Receiver>
   <Donor>DNK</Donor>
   <NSN>112</NSN>
   </Table1>
   <Table1>
   <ID>3</ID>
   <Receiver>ITA</Receiver>
   <Donor>GBR</Donor>
   <NSN>113</NSN>
   </Table1>
  <Table1>
   <ID>4</ID>
   <Receiver>POL</Receiver>
   <Donor>HUN</Donor>
   <NSN>112</NSN>
   </Table1>
 <Table1>
   <ID>5</ID>
   <Receiver>POL</Receiver>
   <Donor>HUN</Donor>
   <NSN>112</NSN>
   </Table1>
 <Table1>
   <ID>6</ID>
   <Receiver>CAN</Receiver>
   <Donor>USA</Donor>
   <NSN>999</NSN>
   </Table1>

   </Root>

and File2 contains:

   <Root>
   <Table1>
   <ID>1</ID>
   <Receiver>GBR</Receiver>
   <Donor>NLD</Donor>
   <NSN>111</NSN>
   </Table1>
   <Table1>
   <ID>2</ID>
   <Receiver>FRA</Receiver>
   <Donor>DNK</Donor>
   <NSN>112</NSN>
   </Table1>
   <Table1>
   <ID>3</ID>
   <Receiver>ITA</Receiver>
   <Donor>GBR</Donor>
   <NSN>113</NSN>
   </Table1>
  <Table1>
   <ID>4</ID>
   <Receiver>POL</Receiver>
   <Donor>HUN</Donor>
   <NSN>112</NSN>
   </Table1>
 <Table1>
   <ID>5</ID>
   <Receiver>ALB</Receiver>
   <Donor>LTV</Donor>
   <NSN>112</NSN>
   </Table1>
 <Table1>
   <ID>5</ID>
   <Receiver>ALB</Receiver>
   <Donor>LTV</Donor>
   <NSN>120</NSN>
   </Table1>
   </Root>


 How do I create File3.xml from File2.xml sorted by NSN?

 Fie3.xml

<Root>
   <Table1>
   <ID>1</ID>
   <Receiver>GBR</Receiver>
   <Donor>NLD</Donor>
   <NSN>111</NSN>
   </Table1>
   <Table1>
   <ID>2</ID>
   <Receiver>FRA</Receiver>
   <Donor>DNK</Donor>
   <NSN>112</NSN>
   </Table1>
   <Table1>
   <ID>3</ID>
   <Receiver>ITA</Receiver>
   <Donor>GBR</Donor>
   <NSN>113</NSN>
   </Table1>
  <Table1>
   <ID>4</ID>
   <Receiver>POL</Receiver>
   <Donor>HUN</Donor>
   <NSN>112</NSN>
   </Table1>
 <Table1>
   <ID>5</ID>
   <Receiver>ALB</Receiver>
   <Donor>LTV</Donor>
   <NSN>112</NSN>
   </Table1>
   </Root>

 Thanks,

 Victor
0
Comment
Question by:vcharles
[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
  • 3
  • 2
6 Comments
 

Author Comment

by:vcharles
ID: 41907889
Help!
0
 
LVL 8

Expert Comment

by:LajuanTaylor
ID: 41908013
Hi @vcharles,

Microsoft has a couple posts with software utilities that should help you compare two XML files and produce an updated file.

In particular, the first article below contains a download link to a utility that leverages the XMLDiff class. It allows you to determine if two files are in fact different based on the conditions you specify.

Both links were checked for validity on - 11/30/2016
https://msdn.microsoft.com/en-gb/library/aa302294.aspx

GUI Version
https://msdn.microsoft.com/en-us/library/aa302295.aspx
0
 

Author Comment

by:vcharles
ID: 41908018
Hi,

Thanks for the link, can it also be done with Linq or regular Query by including all the values on NSN from file1 and use it as a search criteria to find matching values for NSN in file2?

Thanks,

Victor
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41908180
Hi Victor;

The following code snippet should do what you need.

'' Load XML documents into memory
Dim XDocF1 = XDocument.Load("C:\Working Directory\VictorFile-1-3.xml")
Dim XDocF2 = XDocument.Load("C:\Working Directory\VictorFile-2-3.xml")

'' Get a List of all NSN values                                    
Dim result1 = (From n1 In XDocF1.Descendants("Table1")                
               Order By n1.Element("NSN").Value                              
               Select n1.Element("NSN").Value).ToList()                      

'' Find all NSN in file 2 that are in file 1
Dim result2 = (From n2 In XDocF2.Descendants("Table1")                
               Order By n2.Element("NSN").Value                      
               Where result1.Contains(n2.Element("NSN").Value)              
               Select n2).ToList()                                          

'' Create new XML document                                                                      
Dim XDocf3 = XDocF2.Document()
XDocf3.Root.ReplaceAll(result2)
'' Save the new XML document
XDocF3.Save("Give file name and path to where to save file")
0
 

Author Closing Comment

by:vcharles
ID: 41908512
Thank You.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41908542
Not a problem Victor, glad to help.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

751 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