Solved

Help with deleting records in File2 when compared with File1 using VB.NET

Posted on 2016-10-08
7
70 Views
Last Modified: 2016-10-14
Hi,

How do I delete records in file if the values of two match fields are not identical? For example when I compare the first two files below, how do I obtain File3.xml?

File1.xml
 <?xml version="1.0" encoding="utf-8"?>
 <Root>
 <Table1>
   <Link_ID>1</Link_ID>
   <Receiver>USA</Receiver>
   <AGD>XXX</AGD>
   <NSC>FFF</NSC>
   <FCT>UUU</FCT>
 </Table1>
 <Table1>
   <Link_ID>2</Link_ID>
   <Receiver>BEL</Receiver>
   <AGD>XXX</AGD>
   <NSC>GGG</NSC>
   <FCT>HHH</FCT>
 </Table1>
 <Table1>
   <Link_ID>3</Link_ID>
   <Receiver>ESP</Receiver>
   <AGD>XXX</AGD>
   <NSC>EEE</NSC>
   <FCT>PPP</FCT>
 </Table1>
</Root>

File2.xml
 <?xml version="1.0" encoding="utf-8"?>
 <Root>
 <Table2>
   <Link_ID>1</Link_ID>
   <Receiver>DEU</Receiver>
   <AGD>LLL</AGD>
   <NSC>FFF</NSC>
   <FCT>UUU</FCT>
 </Table2>
 <Table2>
   <Link_ID>2</Link_ID>
   <Receiver>BEL</Receiver>
   <AGD>XXX</AGD>
   <NSC>GGG</NSC>
   <FCT>HHH</FCT>
 </Table2>
 <Table2>
   <Link_ID>3</Link_ID>
   <Receiver>ESP</Receiver>
   <AGD>XXX</AGD>
   <NSC>EEE</NSC>
   <FCT>PPP</FCT>
 </Table2>
</Root>

File3.xml
 <?xml version="1.0" encoding="utf-8"?>
 <Root>
 <Table2>
   <Link_ID>2</Link_ID>
   <Receiver>BEL</Receiver>
   <AGD>XXX</AGD>
   <NSC>GGG</NSC>
   <FCT>HHH</FCT>
 </Table2>
 <Table2>
   <Link_ID>3</Link_ID>
   <Receiver>ESP</Receiver>
   <AGD>XXX</AGD>
   <NSC>EEE</NSC>
   <FCT>PPP</FCT>
 </Table2>
 </Root>

Thanks,

Victor
0
Comment
Question by:vcharles
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

by:louisfr
ID: 41836425
This should do it:
    Sub Main()
        Dim f1 = XDocument.Load("d:/temp/file1.xml")
        Dim f2 = XDocument.Load("d:/temp/File2.xml")
        Dim dest = From e1 In f1.Root.Elements
                   From e2 In f2.Root.Elements
                   Where AreEqual(e1.Elements(), e2.Elements())
                   Select e2
        With New XDocument(New XElement("Root", dest))
            .Save("d:/temp/File3.xml")
        End With
    End Sub
    Function AreEqual(x1 As IEnumerable(Of XElement), x2 As IEnumerable(Of XElement)) As Boolean
        Return Aggregate e1 In x1
                   Join e2 In x2 On e1.Name Equals e2.Name
                           Into All(e1.Value = e2.Value)
    End Function

Open in new window

0
 

Author Comment

by:vcharles
ID: 41836648
Hi,

Thanks for the code.

Where in the code do I include the condition to create file3/xml? for example Receiver and AGD don't have the same values.

Victor
0
 
LVL 11

Expert Comment

by:louisfr
ID: 41840172
You can do a selection in the query like
        Dim dest = From e1 In f1.Root.Elements
                   Where e1.Element("Receiver").Value <> e1.Element("AGD").Value
                   From e2 In f2.Root.Elements
                   Where AreEqual(e1.Elements(), e2.Elements())
                   Select e2

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vcharles
ID: 41840959
Hi,

I tried the code below and also tried to make some changes but my output data in File3.xml does not include the records in File2.xml that don't match the condition. I'm trying to create a new file for where the values in both Receiver and AGD don't match in File1 and File2.

Private Sub Button11_Click(sender As System.Object, e As System.EventArgs) Handles Button11.Click
        Dim f1 = XDocument.Load(System.Windows.Forms.Application.StartupPath + "\File1.xml")
        Dim f2 = XDocument.Load(System.Windows.Forms.Application.StartupPath + "\File2.xml")
        Dim dest = From e1 In f1.Root.Elements
                  Where e1.Element("Receiver").Value <> e1.Element("AGD").Value
                  From e2 In f2.Root.Elements
                  Where AreEqual(e1.Elements(), e2.Elements())
                  Select e2

        With New XDocument(New XElement("Root", dest))
            .Save(System.Windows.Forms.Application.StartupPath + "\File3.xml")
        End With

Thanks,

Victor
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41841304
Hi Victor;

I think this will do what you want.
'' Load XML Documents intomemory
Dim file1 = XDocument.Load("File1.XML")
Dim file2 = XDocument.Load("File2.XML")

'' Go through the nodes and transform into the two value you will be searching for in the other XML document 
Dim file1List = (From n1 In file1.Root.Elements
                 Select New With {
                     .Receiver = n1.Element("Receiver").Value,
                     .AGD = n1.Element("AGD").Value,
                     .Node = n1
                }).ToList()

'' Go through the nodes and transform into the two value you will be searching for in the other XML document 
Dim file2List = (From n2 In file2.Root.Elements
                 Select New With {
                     .Receiver = n2.Element("Receiver").Value,
                     .AGD = n2.Element("AGD").Value,
                     .Node = n2
                }).ToList()

'' What does not exist or does not have the same values in the two tags
Dim notInFile2 = (From f1 In file1List
                  Where Not file2List.Any(Function(v) v.Receiver = f1.Receiver AndAlso v.AGD = f1.AGD)
                  Select f1).ToList()

Dim notInFile1 = (From f2 In file2List
                  Where Not file1List.Any(Function(v) v.Receiver = f2.Receiver AndAlso v.AGD = f2.AGD)
                  Select f2).ToList()

'' If the above has values in them then load them into the new document
If notInFile2.Count > 0 OrElse notInFile1.Count > 0 Then
    '' Create new XDocument for the results
    Dim file3 = New XDocument(New XDeclaration(file1.Declaration))
    file3.Add(New XElement("Root"))

    '' Add all the XElements from the notInFile1 list
    For Each n In notInFile1
        file3.Root.Add(n.Node)
    Next

    '' Add all the XElements from the notInFile2 list
    For Each n In notInFile2
        file3.Root.Add(n.Node)
    Next

    file3.Save("file3.xml")
    Console.WriteLine()
End If

Open in new window

0
 

Author Closing Comment

by:vcharles
ID: 41844529
Thank You.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41844538
Not a problem Victor, glad to help.
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.

Question has a verified solution.

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

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 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