?
Solved

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

Posted on 2015-01-31
11
Medium Priority
?
30 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 82

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 2000 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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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 …
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…

770 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