Solved

Help with merging data from two xml files based on identical IDs using VB.NET

Posted on 2014-07-31
14
204 Views
Last Modified: 2014-08-01
Hi,

I have two xml files with identical SN data element, How do I transfer data from file2.xml to file1.xml based on identical SN? For example if file1.xml contains

<Root>
<Table1>
 <SN>10411</SN>
 <NSC>ITEMA</NSC>
 </Table1>
 <Table1>
 <SN>10412</SN>
 <NSC>ITEMB</NSC>
 </Table1>
 <Table1>
 <SN>10421</SN>
 <NSC>ITEMC</NSC>
 </Table1>
 <Table1>
 <SN>10521</SN>
 <NSC>ITEMD</NSC>
 </Table1>
</Root>

and File2.xml contains
<Root>
<Table2>
 <SN>10411</SN>
 <RIC>ITEMA1</RIC>
 </Table2>
 <Table2>
 <SN>10412</SN>
 <RIC>ITEMB1</RIC>
 </Table2>
 <Table2>
 <SN>10421</SN>
 <RIC>ITEMC1</RIC>
 </Table2>
 </Root>

How do I obtain thye following data in file1.xml

<Root>
<Table1>
 <SN>10411</SN>
 <NSC>ITEMA</NSC>
 <RIC>ITEMA1</RIC>
 </Table1>
 <Table1>
 <SN>10412</SN>
 <NSC>ITEMB</NSC>
<RIC>ITEMB1</RIC>
 </Table1>
 <Table1>
 <SN>10421</SN>
 <NSC>ITEMC</NSC>
<RIC>ITEMC1</RIC>
 </Table1>
 <Table1>
 <SN>10521</SN>
 <NSC>ITEMD</NSC>
 </Table1>
</Root>


Thanks,

Victor
0
Comment
Question by:vcharles
  • 7
  • 7
14 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 40233657
Not sure if you're looking for a LINQ solution here but you could do it with a simple loop over all nodes and a lookup in the second document:
        Dim x1 As New XmlDocument, x2 As New XmlDocument
        x1.Load("File1.xml")
        x2.Load("File2.xml")

        Dim x As XmlElement = x1.DocumentElement.FirstChild
        While x IsNot Nothing ' loop over all Table1 nodes
            Dim y As XmlElement = x2.SelectSingleNode("//Table2[SN='" & x.SelectSingleNode("SN").InnerText & "']")
            If y IsNot Nothing Then x.SelectSingleNode("NSC").InnerText = y.SelectSingleNode("RIC").InnerText
            x = x.NextSibling
        End While

        x1.Save("NewFile1.xml")

Open in new window

Note that you should probably add some checking for non existing subnodes or at least put this in a Try/Catch block.
0
 

Author Closing Comment

by:vcharles
ID: 40233789
Thank You.
0
 

Author Comment

by:vcharles
ID: 40233848
Hi,

Just noticed the new file created only contains records where there is a match between both files, how do  modify the code to include all records in file1? When there is no match the RIC element should remain blank instead of not including that record.

Thanks,

Victor
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40233891
Can you give new example files? Because I tested with your files and the output was as you specified. Unless I misunderstood earlier, it's not clear to me which situation you refer to now.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40233894
oops, sorry I see that I DID misunderstand: the output should contain both nodes. I'll post new code shortly.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40233906
Try this please:
        Dim x As XmlElement = x1.DocumentElement.FirstChild
        While x IsNot Nothing ' loop over all Table1 nodes
            ' check if SN node exists under current Table1 node
            Dim sn1 As XmlElement = x.SelectSingleNode("SN")
            If sn1 IsNot Nothing Then
                ' check if there is text in the SN node
                Dim sn1t As String = sn1.InnerText
                If Not String.IsNullOrWhiteSpace(sn1t) Then
                    ' check if NSC node exists under current Table1 node
                    Dim nsc1 As XmlElement = x.SelectSingleNode("NSC")
                    If nsc1 IsNot Nothing Then
                        ' check if same SN exists in File2
                        Dim y As XmlElement = x2.SelectSingleNode("//Table2[SN='" & sn1t & "']")
                        If y IsNot Nothing Then
                            ' check if there is a new value present
                            Dim ric2 As XmlElement = y.SelectSingleNode("RIC")
                            If ric2 IsNot Nothing Then
                                ' check if RIC node exists under current Table1 node
                                Dim ric1 As XmlElement = x.SelectSingleNode("RIC")
                                If ric1 Is Nothing Then ' create it
                                    ric1 = x.AppendChild(x1.CreateElement("RIC"))
                                End If
                                ric1.InnerText = ric2.InnerText
                            End If
                        End If
                    End If
                End If
            End If
            x = x.NextSibling
        End While

Open in new window

0
 

Author Comment

by:vcharles
ID: 40233920
Hi,

Thanks for the code, I need to add the RIC data in file1 not replace the NSC data. I modified the code but problem was the records in file1 that did not include a match between both files were not included in the final file.

I will try your latest code and get back to you.

V.
0
 

Author Comment

by:vcharles
ID: 40233943
Still the same, all the records in he new file contain RIC records with data. How do you count number of records so I find out how many records are missing in the new file. Would like to compare number of records in file1 with new file created.

Thanks,

V.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40233944
I don't understand, this is my newest output, which matches your example:
<Root>
  <Table1>
    <SN>10411</SN>
    <NSC>ITEMA</NSC>
    <RIC>ITEMA1</RIC>
  </Table1>
  <Table1>
    <SN>10412</SN>
    <NSC>ITEMB</NSC>
    <RIC>ITEMB1</RIC>
  </Table1>
  <Table1>
    <SN>10421</SN>
    <NSC>ITEMC</NSC>
    <RIC>ITEMC1</RIC>
  </Table1>
  <Table1>
    <SN>10521</SN>
    <NSC>ITEMD</NSC>
  </Table1>
</Root>

Open in new window

0
 

Author Comment

by:vcharles
ID: 40233958
What I sent you was an example, file contains SN values that are not in file2 because there are no RIC available for the those files, for example file1 has the following:

<Root>
 <Table1>
  <SN>10411</SN>
  <NSC>ITEMA</NSC>
  </Table1>
  <Table1>
  <SN>10412</SN>
  <NSC>ITEMB</NSC>
  </Table1>
  <Table1>
  <SN>10421</SN>
  <NSC>ITEMC</NSC>
  </Table1>
  <Table1>
  <SN>10521</SN>
  <NSC>ITEMD</NSC>
  </Table1>
<Table1>
  <SN>10522</SN>
  <NSC>ITEME</NSC>
  </Table1>
<Table1>
  <SN>10523</SN>
  <NSC>ITEMF</NSC>
  </Table1>
 </Root>

I still need to keep the last two records (10522, 10523) in my new file created even if there was no match in file2.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40233963
That should be what my code does because no nodes are removed from "x1" and that document is saved (to a new file in my initial post). Can you post your exact code because I feel you've changed something (x1/x2) but maybe I'm wrong.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40233964
By the way: you can count with:
MsgBox(x1.DocumentElement.ChildNodes.Count)

Open in new window

The number of nodes doesn't change though, this is how I understood your request.
0
 

Author Comment

by:vcharles
ID: 40234006
Ok, will run more test and get back to you.hanks
0
 

Author Comment

by:vcharles
ID: 40234086
Hi,

You were right.

Thanks,

Victor
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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