Solved

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

Posted on 2016-10-08
7
58 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 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

15 Experts available now in Live!

Get 1:1 Help Now