Solved

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

Posted on 2015-01-29
7
117 Views
Last Modified: 2015-01-29
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
Comment
Question by:vcharles
  • 4
  • 3
7 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40578171
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
 

Author Comment

by:vcharles
ID: 40578276
Hi Fernando,

I will change the format as you suggested.

Thanks,

Victor
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40578425
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
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: 40578436
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40578450
That is correct.
0
 

Author Comment

by:vcharles
ID: 40578660
Hi,

It works!

Thank You!.

Victor
0
 

Author Closing Comment

by:vcharles
ID: 40578661
Thank You,
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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

805 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