[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with merging multiple files in file1 based on identical values in file2 using VB.NET

Posted on 2014-08-03
7
Medium Priority
?
148 Views
Last Modified: 2014-08-04
Hi,

If file1.xml contains:

<?xml version="1.0" encoding="utf-8"?>
<Root>
  <AOP6A>
    <SN>10412</SN>
    <CHPT>104</CHPT>
    <CA></CA>
</AOP6A>
  <AOP6A>
    <SN>10471</SN>
    <CHPT>105</CHPT>
    <CA></CA>
  </AOP6A>
</Root>

and file2.xml contains:

<?xml version="1.0" encoding="utf-8"?>
<Root>
  <AOP5>
    <SN>10412</SN>
    <CHPT>104</CHPT>
    <Ctry>BEL</Ctry>
</AOP5>
  <AOP5>
    <SN>10412</SN>
    <CHPT>105</CHPT>
    <Ctry>FRA</Ctry>
  </AOP6A>

How do I enter Ctry values to iCA element in file1 based on matching SN, for example above, file1.xml should be as follows:

<?xml version="1.0" encoding="utf-8"?>
<Root>
  <AOP6A>
    <SN>10412</SN>
    <CHPT>104</CHPT>
    <CA>BEL,FRA</CA> 'Data from file2 based on SN = 10412 in both files
</AOP6A>
  <AOP6A>
    <SN>10471</SN>
    <CHPT>105</CHPT>
    <CA></CA>
  </AOP6A>
</Root>
 
Thanks,

Victor
0
Comment
Question by:vcharles
  • 5
  • 2
7 Comments
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 40239288
Hi Victor;

This should do what you need.

'' Load the XML files
Dim xdoc1 = XDocument.Load("...\file1.xml")
Dim xdoc2 = XDocument.Load("...\file2.xml")

'' Query both documents and return values to be updated from file2
Dim results = (From n1 In xdoc1.Descendants("SN") _
               Join n2 In xdoc2.Descendants("SN") On n1.Value Equals  n2.Value _
               Where n2.Parent.Element("Ctry") IsNot Nothing AndAlso Not String.IsNullOrEmpty(n2.Parent.Element("Ctry").Value) _ 
               Select _
                   parent = n1.Parent, _
                   n1, _
                   n2, _
                   Ctry = n2.Parent.Element("Ctry").Value _
               ).ToList()

'' Modify file1 with the data from file2
For Each nodes in results
    If nodes.parent.Element("CA") Is Nothing Then    
        nodes.parent.Add(new XElement("CA", nodes.Ctry))
    Else
        Dim ca = nodes.parent.Element("CA").Value
        If String.IsNullOrEmpty(ca) Then
            nodes.parent.Element("CA").Value = nodes.Ctry
        Else
            nodes.parent.Element("CA").Value += "," + nodes.Ctry
        End If
    End If
Next

xdoc1.Save("... .xml")

Open in new window

0
 

Author Comment

by:vcharles
ID: 40239434
Thanks, will try it and get back to you.
0
 

Author Comment

by:vcharles
ID: 40239516
Hi Fernando,

How do you modify the code to remove duplicates in <CA> and also sort the data.

Thank You.

victor
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:vcharles
ID: 40239540
Hi,

I am able to sort the data In CA, but how do you remove or avoid duplicates in <CA>.

Thanks,

Victor
0
 

Author Comment

by:vcharles
ID: 40239546
Hi,

The solution for the second question in in another post.

Thanks,

victor
0
 

Author Closing Comment

by:vcharles
ID: 40239548
Thank You!
0
 
LVL 64

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

868 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