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
57 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
  • 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

820 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