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

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
vcharlesAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Fernando SotoConnect With a Mentor RetiredCommented:
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
 
vcharlesAuthor Commented:
Help!
0
 
LajuanTaylorCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
Thank You.
0
 
Fernando SotoRetiredCommented:
Not a problem Victor, glad to help.
0
All Courses

From novice to tech pro — start learning today.