Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

Help with creating new xml file when comparing two xml files with two or more identical values in a data element using VB.NET

Hi,

How do you create a new xml file (i.e. File2.xml) for all records in File1.xml where two or more records have the same values in the NSN field?  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>
  <ID>6</ID>
  <Receiver>ESP</Receiver>
  <Donor>LUX</Donor>
  <NSN>113</NSN>
  </Table1>
  </Root>

How do I create File2.xml sorted by NSN?

Fie2.xml
 
<Root>
  <Table1>
  <ID>2</ID>
  <Receiver>FRA</Receiver>
  <Donor>DNK</Donor>
  <NSN>112</NSN>
  </Table1>
 <Table1>
  <ID>4</ID>
  <Receiver>POL</Receiver>
  <Donor>HUN</Donor>
  <NSN>112</NSN>
  </Table1>
  <Table1>
  <ID>3</ID>
  <Receiver>ITA</Receiver>
  <Donor>GBR</Donor>
  <NSN>113</NSN>
  </Table1>
  <Table1>
  <ID>6</ID>
  <Receiver>ESP</Receiver>
  <Donor>LUX</Donor>
  <NSN>113</NSN>
  </Table1>
  </Root>

Thanks,

Victor
0
vcharles
Asked:
vcharles
  • 8
  • 8
  • 2
2 Solutions
 
Fernando SotoCommented:
Hi Victor;

I ordered the nodes in numerical order if NSN can have alphabetical characters then you will need to remove Integer.Parse( ) leaving node.Element("NSN").Value in the query.
'' Load XML file1
Dim XDocF1 = XDocument.Load("C:\Working Directory\File1Unsorted.xml")
'' Order the nodes                                                                     
Dim results = (From node In XDocF1.Descendants("Table1")             
               Order By Integer.Parse(node.Element("NSN").Value)     
               Select node).ToList()                                       
'' Create new XML document from XDocF1			                                                               
Dim XDocF2 = XDocF1.Document()        
'' Replace all nodes with the ordered set of nodes                               
XDocF2.Root.ReplaceAll(results)                   
'' Save the new Document                   
XDocF2.Save("Give file name and path for new document")		

Open in new window

0
 
vcharlesAuthor Commented:
Thank You, I will try it and get back to you.
0
 
Jeff DarlingDeveloper AnalystCommented:
I noticed that the first XML file is invalid.  There is a mismatched Table1 Tag.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
vcharlesAuthor Commented:
Hi Fernando,

I'm trying the code below but it's not working, the NSN field is separated by dashes (i.e. xxx-xxx-xx), which might be the issue. The file is returning records that don't have two or more identical NSN values.

   'Retreive two or more records with identical NSN from file2.xml

        '' Load XML file2
        Dim XDocF1 = XDocument.Load(Application.StartupPath + "\file2.xml")
        '' Order the nodes                                                                    
        Dim results = (From node In XDocF1.Descendants("Table1")
                       Order By (node.Element("NSN").Value)
                       Select node).ToList()
        '' Create new XML document from XDocF1                                                                                 
        Dim XDocF2 = XDocF1.Document()
        '' Replace all nodes with the ordered set of nodes                              
        XDocF2.Root.ReplaceAll(results)
        '' Save the new Document                  
        XDocF2.Save(Application.StartupPath + "\file3.xml")


Thanks,

Victor
0
 
Fernando SotoCommented:
Can you please post a more correct XML file so that I can test with.
0
 
vcharlesAuthor Commented:
Hi,

Below is another version of the xml files.

File1.xml:

   <Root>
   <Table1>
   <ID>1</ID>
   <Receiver>BEL</Receiver>
   <Donor>USA</Donor>
   <NSN>111-04-11</NSN>
   </Table1>
   <Table1>
   <ID>2</ID>
   <Receiver>FRA</Receiver>
   <Donor>DNK</Donor>
   <NSN>112-05-11</NSN>
   </Table1>
   <Table1>
   <ID>3</ID>
   <Receiver>ITA</Receiver>
   <Donor>GBR</Donor>
   <NSN>113-09-99</NSN>
   </Table1>
  <Table1>
   <ID>4</ID>
   <Receiver>POL</Receiver>
   <Donor>HUN</Donor>
   <NSN>112-05-11</NSN>
   </Table1>
  <Table1>
   <ID>6</ID>
   <Receiver>ESP</Receiver>
   <Donor>LUX</Donor>
   <NSN>113-08-55</NSN>
   </Table1>
  <Table1>
   <ID>7</ID>
   <Receiver>GBR</Receiver>
   <Donor>LUX</Donor>
   <NSN>113-08-55</NSN>
   </Table1>
  <Table1>
   <ID>8</ID>
   <Receiver>ESP</Receiver>
   <Donor>LUX</Donor>
   <NSN>113-00-55</NSN>
   </Table1>
   </Root>

 How do I create File2.xml sorted by NSN?

 Fie2.xml
 
<Root>
     <Table1>
   <ID>2</ID>
   <Receiver>FRA</Receiver>
   <Donor>DNK</Donor>
   <NSN>112-05-11</NSN>
   </Table1>
   <Table1>
   <ID>4</ID>
   <Receiver>POL</Receiver>
   <Donor>HUN</Donor>
   <NSN>112-05-11</NSN>
   </Table1>
  <Table1>
   <ID>6</ID>
   <Receiver>ESP</Receiver>
   <Donor>LUX</Donor>
   <NSN>113-08-55</NSN>
   </Table1>
  <Table1>
   <ID>7</ID>
   <Receiver>GBR</Receiver>
   <Donor>LUX</Donor>
   <NSN>113-08-55</NSN>
   </Table1>
   </Root>

Only records with two or more records with same NSN values are included in File2.

 Thanks,

 Victor
0
 
Fernando SotoCommented:
OK Victor, then try it like this.
'' Load XML file1                                                                                          
Dim XDocF1 = XDocument.Load("C:\Working Directory\File1Unsorted-2.xml")                                    
'' Order the nodes                                                                                         
Dim results = (From node In XDocF1.Descendants("Table1")                                                   
               Group node By node.Element("NSN").Value.Trim() Into nsnGrouping = Group                     
               Where nsnGrouping.Count > 1                                                                       
               Select nsnGrouping.First()).ToList()                                                        
'' Create new XML document from XDocF1			                                                               
Dim XDocF2 = XDocF1.Document()                                                                             
'' Replace all nodes with the ordered set of nodes                                                         
XDocF2.Root.ReplaceAll(results)                                                                            
'' Save the new Document                                                                                   
XDocF2.Save("Give file name and path for new document")

Open in new window

0
 
vcharlesAuthor Commented:
Hi Fernando,

Thanks for the code, unfortunately records without duplicate NSNs values still show up on File3.xml.

Thanks,

Victor
0
 
Fernando SotoCommented:
Hi Victor;

There is only one XML file it processes and the results are stored in a new XML file called XDocF2. Is this what you are calling File3.xml? I tested with the last XML file you posted and it seem to work giving the results as the results you are looking for. You will need to post the XML that this is giving you issues with so I can see what is going on.
0
 
Jeff DarlingDeveloper AnalystCommented:
Fernandos code worked when I tried it, except for the select needs to be slightly different.  remove First()

like this.

        '' Load XML file1                                                                                          
        Dim XDocF1 = XDocument.Load("C:\Users\jdarling\ee\20161130\01\file1.xml")
        '' Order the nodes                                                                                         
        Dim results = (From node In XDocF1.Descendants("Table1")
                       Group node By node.Element("NSN").Value.Trim() Into nsnGrouping = Group
                       Where nsnGrouping.Count > 1
                       Select nsnGrouping).ToList()
        '' Create new XML document from XDocF1			                                                               
        Dim XDocF2 = XDocF1.Document()
        '' Replace all nodes with the ordered set of nodes                                                         
        XDocF2.Root.ReplaceAll(results)
        '' Save the new Document                                                                                   
        XDocF2.Save("C:\Users\jdarling\ee\20161130\01\file3.xml")

Open in new window

0
 
Fernando SotoCommented:
@Jeff, from the example Victor posted he only wants one of the two or more to show up in the resulting XML. Removing First() will load all the nodes from each group.
0
 
Fernando SotoCommented:
Wow I don't know how I missed that, Thanks. But he also seems to have them Order'ed by.
0
 
Fernando SotoCommented:
Victor, update on query.
Dim results = (From node In XDocF1.Descendants("Table1")
               Group node By node.Element("NSN").Value.Trim() Into nsnGrouping = Group
               Where nsnGrouping.Count > 1
               Select nsnGrouping.OrderBy(Function(o) o.Element("NSN").Value.Trim())).ToList()

Open in new window

1
 
vcharlesAuthor Commented:
Hi Fernando,

I'm used less records to get a better idea of what's going on and  noticed records that have duplicates are listed in file3, but only one record for each. I need to list all the records that have duplicates not only one of each.

Thanks,

Victor
0
 
Fernando SotoCommented:
Victor please see my last post.
0
 
vcharlesAuthor Commented:
Hi,

Just noticed the other messages posted before my message, will get back to you.

Thanks,

Victor
0
 
vcharlesAuthor Commented:
Hi,

It works!

Thank You both.

Victor
0
 
vcharlesAuthor Commented:
Thank You.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now