Solved

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

Posted on 2016-10-08
7
54 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 62

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 62

Expert Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now