Solved

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

Posted on 2014-07-31
14
197 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
Comment Utility
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
Comment Utility
Thank You.
0
 

Author Comment

by:vcharles
Comment Utility
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
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:vcharles
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, will run more test and get back to you.hanks
0
 

Author Comment

by:vcharles
Comment Utility
Hi,

You were right.

Thanks,

Victor
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now