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
51 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 62

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 62

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

773 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