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
19 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
Comment Utility
Help!
0
 
LVL 8

Expert Comment

by:LajuanTaylor
Comment Utility
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
Comment Utility
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
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.

 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
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
Comment Utility
Thank You.
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Not a problem Victor, glad to help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

11 Experts available now in Live!

Get 1:1 Help Now