• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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

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
vcharles
Asked:
vcharles
  • 7
  • 7
1 Solution
 
Robert SchuttSoftware EngineerCommented:
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
 
vcharlesAuthor Commented:
Thank You.
0
 
vcharlesAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Robert SchuttSoftware EngineerCommented:
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
 
Robert SchuttSoftware EngineerCommented:
oops, sorry I see that I DID misunderstand: the output should contain both nodes. I'll post new code shortly.
0
 
Robert SchuttSoftware EngineerCommented:
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
 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
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
 
Robert SchuttSoftware EngineerCommented:
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
 
vcharlesAuthor Commented:
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
 
Robert SchuttSoftware EngineerCommented:
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
 
Robert SchuttSoftware EngineerCommented:
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
 
vcharlesAuthor Commented:
Ok, will run more test and get back to you.hanks
0
 
vcharlesAuthor Commented:
Hi,

You were right.

Thanks,

Victor
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now