Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
123 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 64

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 64

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

886 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