Solved

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

Posted on 2015-01-31
11
25 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 79

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
write xml in vb.net 2 24
.NET 2008 VB and C# 6 28
Firing an ASP.NET event on selection of a JQuery datepicker 4 21
Call Controller Action Method from ASPX 2 16
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

831 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