Solved

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

Posted on 2015-01-31
11
28 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
[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
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 81

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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 …
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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