• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

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
0
vcharles
Asked:
vcharles
  • 4
  • 3
1 Solution
 
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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