Solved

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

Posted on 2016-11-30
49 Views
Last Modified: 2016-11-30
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
Question by:vcharles
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 8
• 8
• 2
18 Comments

LVL 63

Expert Comment

ID: 41907476
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")

0

Author Comment

ID: 41907505
Thank You, I will try it and get back to you.
0

LVL 13

Expert Comment

ID: 41907528
I noticed that the first XML file is invalid.  There is a mismatched Table1 Tag.
0

Author Comment

ID: 41907716
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

LVL 63

Expert Comment

ID: 41907751
Can you please post a more correct XML file so that I can test with.
0

Author Comment

ID: 41907764
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

LVL 63

Expert Comment

ID: 41907786
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")

0

Author Comment

ID: 41907803
Hi Fernando,

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

Thanks,

Victor
0

LVL 63

Expert Comment

ID: 41907808
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

LVL 13

Assisted Solution

Jeff Darling earned 250 total points
ID: 41907810
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")

0

LVL 63

Expert Comment

ID: 41907819
@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

LVL 63

Expert Comment

ID: 41907831
Wow I don't know how I missed that, Thanks. But he also seems to have them Order'ed by.
0

LVL 63

Accepted Solution

Fernando Soto earned 250 total points
ID: 41907833
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()

1

Author Comment

ID: 41907865
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

LVL 63

Expert Comment

ID: 41907871
Victor please see my last post.
0

Author Comment

ID: 41907872
Hi,

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

Thanks,

Victor
0

Author Comment

ID: 41907881
Hi,

It works!

Thank You both.

Victor
0

Author Closing Comment

ID: 41907882
Thank You.
0

## Featured Post

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
###### Suggested Courses
Course of the Month6 days, 5 hours left to enroll

#### 627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.