Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 34
  • Last Modified:

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

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
vcharles
Asked:
vcharles
  • 6
  • 4
1 Solution
 
Fernando SotoCommented:
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
 
vcharlesAuthor Commented:
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
 
Fernando SotoCommented:
Victor, the node <SN> STILL does not have a closing tag which makes the XML invalid.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vcharlesAuthor Commented:
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
 
David Johnson, CD, MVPOwnerCommented:
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
 
vcharlesAuthor Commented:
Hi,

Got it. it looks like a better format.  

Thanks,

Victor
0
 
Fernando SotoCommented:
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
 
vcharlesAuthor Commented:
Hi Fernando,

Thank you, will try it when i get home.
Victor
0
 
vcharlesAuthor Commented:
Hi Fernando,

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

Thanks,

Victor
0
 
vcharlesAuthor Commented:
Hi Fernando,

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

Thanks,

Victor
0
 
Fernando SotoCommented:
Hi Victor;

Please close this question. Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now