Solved

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

Posted on 2014-07-31
14
222 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

627 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