Solved

Help with adding data from xml file2 to file1 using VB.NET

Posted on 2015-01-31
11
26 Views
Last Modified: 2015-10-27
Hi Fernando,

How to I modify the code to copy the value of data element from file2 to existing data element in file1? For example if file 1 contains:

<Root>
<Table1>
<ID>1</ID>
<SN>101</SN>
<ItemB></ItemB>
<ItemX></ItemX>
<ItemY></ItemY>
<ItemZ>C</ItemZ>
</Table1>
<Table1>
<ID>2</ID>
<SN></102>
<ItemB></ItemB>
<ItemX>D</ItemX>
<ItemY></ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>3</ID>
<SN>103</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
</Root>
and file2 contains:

<Root>
<Table>
<Table2>
<SN>101<SN>
<ItemX>A</ItemX>
<ItemY>B</ItemY>
<ItemZ>C</ItemZ>
<SN>102<SN>
<ItemX>D</ItemX>
<ItemY>E</ItemY>
<ItemZ>F</ItemZ>
<SN>103<SN>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ>I</ItemZ>
</Table2>
<table>
</Root>

New File 1 should contain:
<Root>
<Table1>
<ID>1</ID>
<SN>101</SN>
<ItemB></ItemB>
<ItemX>A</ItemX>
<ItemY>B</ItemY>
<ItemZ>C</ItemZ>
</Table1>
<Table1>
<ID>2</ID>
<SN>102</SN>
<ItemB></ItemB>
<ItemX>D</ItemX>
<ItemY>E</ItemY>
<ItemZ>F</ItemZ>
</Table1>
<Table1>
<ID>3</ID>
<SN>103</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ>I</ItemZ>
</Table1>


' Query both documents and Join on the SN node in each 
Dim results = From n1 In f1.Descendants("Table1") _
              Where n1.Element("SN") IsNot Nothing _
              From n2 In f2.Descendants("Table") _
              Where n2.Element("SN") IsNot Nothing AndAlso n1.Element("SN").Value = n2.Element("SN").Value _
              Select n1, n2

' Copy the elements from File2 to File1
For Each pair In results
    Dim removeSN = pair.n2.Element("SN")
    removeSN.Remove()
    For Each element As XElement In pair.n2.Descendants()
        pair.n1.Add(element)
    Next
Next

' Save file1.xml as file3.xml
f1.Save("C:\Working Directory\file3.xml")

Open in new window

Thanks,

Victor
0
Comment
Question by:vcharles
  • 6
  • 4
11 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40582063
Hi Victor;

Please correct the XML file2 so I can come up with a solution. The node <SN> does not have a closing tag.

<Root>          
  <Table>         
    <Table2>        
      <SN>101<SN>     
      <ItemX>A</ItemX>
      <ItemY>B</ItemY>
      <ItemZ>C</ItemZ>
      <SN>102<SN>     
      <ItemX>D</ItemX>
      <ItemY>E</ItemY>
      <ItemZ>F</ItemZ>
      <SN>103<SN>     
      <ItemX>G</ItemX>
      <ItemY>H</ItemY>
      <ItemZ>I</ItemZ>
    </Table2>       
  </Table>         
</Root>  

Open in new window

0
 

Author Comment

by:vcharles
ID: 40582067
Hi,

File2 should be as follows:

<Root>
 <Table>
 <Table2>
 <SN>101<SN>
 <ItemX>A</ItemX>
 <ItemY>B</ItemY>
 <ItemZ>C</ItemZ>
</Table2>
<Table2>
 <SN>102<SN>
 <ItemX>D</ItemX>
 <ItemY>E</ItemY>
 <ItemZ>F</ItemZ>
</Table2>
<Table2>
 <SN>103<SN>
 <ItemX>G</ItemX>
 <ItemY>H</ItemY>
 <ItemZ>I</ItemZ>
 </Table2>
 </Table>
</Root>

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40582069
Victor, the node <SN> STILL does not have a closing tag which makes the XML invalid.
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.

 

Author Comment

by:vcharles
ID: 40582084
Fernando,

I missed it, the example below should be ok.

<Root>
  <Table>
  <Table2>
  <SN>101</SN>
  <ItemX>A</ItemX>
  <ItemY>B</ItemY>
  <ItemZ>C</ItemZ>
 </Table2>
 <Table2>
  <SN>102</SN>
  <ItemX>D</ItemX>
  <ItemY>E</ItemY>
  <ItemZ>F</ItemZ>
 </Table2>
 <Table2>
  <SN>103</SN>
  <ItemX>G</ItemX>
  <ItemY>H</ItemY>
  <ItemZ>I</ItemZ>
  </Table2>
  </Table>
 </Root>
Thanks,

Victor
0
 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 40582093
I think this is the correct file2.xml
<Root>          
  <Table>         
    <Table2>        
      <SN>101</SN>     
      <ItemX>A</ItemX>
      <ItemY>B</ItemY>
      <ItemZ>C</ItemZ>
      <SN>102</SN>     
      <ItemX>D</ItemX>
      <ItemY>E</ItemY>
      <ItemZ>F</ItemZ>
      <SN>103</SN>     
      <ItemX>G</ItemX>
      <ItemY>H</ItemY>
      <ItemZ>I</ItemZ>
    </Table2>       
  </Table>         
</Root>  
                                          

Open in new window

0
 

Author Comment

by:vcharles
ID: 40582293
Hi,

Got it. it looks like a better format.  

Thanks,

Victor
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40582611
Hi Victor;

This code snippet should give you what you need

'' Load XML Documents
Dim xdocFile1 As XDocument = XDocument.Load("C:\Working Directory\file1.xml")
Dim xdocFile2 As XDocument = XDocument.Load("C:\Working Directory\file2.xml")

'' Get elements to be modified
Dim results = From f1 As XElement In xdocFile1.Descendants("Table1") _
              Join f2 As XElement In xdocFile2.Descendants("SN") On f1.Element("SN").Value Equals f2.Value
              Select f1, f2

'' Holds the elements in File2 which are to be modified in File1 by the SN Key
'' Tne Value of the Key/Value pare is a List(Of XElement) that belong to the SN
Dim items As New Dictionary(Of String, List(Of XElement))

'' Loads the dictionary
For Each matchF2 In results
    Dim itemsSN As New List(Of XElement)()
    Dim SNNode As String = matchF2.f2.Value
    For Each n2 In matchF2.f2.ElementsAfterSelf()
        If n2.Name = "SN" Then
            Exit For
        End If
        itemsSN.Add(n2)
    Next
    items.Add(SNNode, itemsSN)
Next

'' Modify the elements
For Each matchF1 In results
    For Each id In matchF1.f1.Elements()
        Dim sn As String = CType(id.NextNode(), XElement).Value
        If items.ContainsKey(sn) Then
            Dim item As List(Of XElement) = items(sn)
            For Each f2Item As XElement In item
                Dim replaceThis As XElement = id.Parent.Element(f2Item.Name.LocalName)
                replaceThis.ReplaceWith(f2Item)
            Next
        Else
            Exit For
        End If
    Next
Next

'' Save the modified document
xdocFile1.Save("C:\Working Directory\File1-New.xml")

Open in new window

0
 

Author Comment

by:vcharles
ID: 40582645
Hi Fernando,

Thank you, will try it when i get home.
Victor
0
 

Author Comment

by:vcharles
ID: 40584117
Hi Fernando,

Unfortunately  it is not working, running  more test to see if it because of the xml files.

Thanks,

Victor
0
 

Author Comment

by:vcharles
ID: 40665719
Hi Fernando,

I just posted a new issue related to this issue which I never managed to resolve.

Thanks,

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41124326
Hi Victor;

Please close this question. Thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
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…

856 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