Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-08
7
Medium Priority
?
88 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
[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
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

Expert Comment

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

670 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