Help with adding multiple data elements from File2 to File 1 based on identical SN using VB.NET

Hi,

How do I modify the code to copy multiple data elements from fil2 to file1 based on identical SN, instead of running the code separately for each value in data element to be added in file1 in code below?

' Load the file1.xml and file2.xml into memory
Dim f1 = XDocument.Load("C:\Working Directory\file1.xml")
Dim f2 = XDocument.Load("C:\Working Directory\file2.xml")

' 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("Table1") _
              Where n2.Element("SN") IsNot Nothing AndAlso n1.Element("SN").Value = n2.Element("SN").Value _
              Select n1, n2
' Add the NSD node in file2.xml to the corresponding node in file1.xml
For Each pair in results
    pair.n1.Add(pair.n2.Element("NSD"))
Next

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

Open in new window



XML Files:

File 1.

<Root>
<Table1>
<ID>1</ID>
<SN>101</SN>
<ItemB></ItemB>
</Table1>
<Table1>
<ID>2</ID>
<SN></102>
<ItemB></ItemB>
</Table1>
<Table1>
<ID>3</ID>
<SN>103</SN>
<ItemB></ItemB>
</Table1>

File 2

<Root>
<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>
<SN>104<SN>
<ItemX>J</ItemX>
<ItemY>K</ItemY>
<ItemZ>L</ItemZ>
</Table2>

New File 1 should comtain:
<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>
<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>


Thanks,

Victor
vcharlesAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
Hi Victor;

I have modified the code to what you stated.

' Load the file1.xml and file2.xml into memory
Dim f1 = XDocument.Load("C:\Working Directory\file1.xml")
Dim f2 = XDocument.Load("C:\Working Directory\file2.xml")

' 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

0
 
Fernando SotoRetiredCommented:
Hi Victor;

I needed to correct the XML file that you posted which had malformed nodes and missing closing Root node. Here is the File2 XML as you posted it with corrections. Is this format set in concrete? All nodes are children of Table2 node . This is not the best schema and will make it prone to error when updating File1.

<Root>
  <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>
    <SN>104</SN>
    <ItemX>J</ItemX>
    <ItemY>K</ItemY>
    <ItemZ>L</ItemZ>
  </Table2>
</Root>

Open in new window

My I suggest the following schema for file2 which will separate all values belonging to the same parent node.

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

Open in new window

0
 
vcharlesAuthor Commented:
Hi Fernando,

I will change the format as you suggested.

Thanks,

Victor
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
vcharlesAuthor Commented:
Hi Fernando,

I did not get a chance to test the code yet, but just wanted to confirm that it will only work with the new xml format you proposed.

Thanks,

Victor
0
 
Fernando SotoRetiredCommented:
That is correct.
0
 
vcharlesAuthor Commented:
Hi,

It works!

Thank You!.

Victor
0
 
vcharlesAuthor Commented:
Thank You,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.