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
113 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now