Solved

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

Posted on 2014-07-31
14
209 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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