Help with merging data from multiple XML files using VB.NET

Hi,

 I'm exporting data from multiple C1TrueDBGrid controls to multiple excel files. How do I create 1 xml file from multiple xml files based on identical IDs using vb.net?  For example if first execel file contains:

 id   nsn   add
 1    888   hnm
 2    999   ujk
 3    000   NNN
 4    444   IKK

 and excel file 2 contains:

 id   NAS  NMN
 1    777   ccc
 2    666   ddd


and excel file 3 contains:

 id   NOP  PCC
 1    888   TTT
 2    999   MMM

 I would like to have the following xml file:

 id   nsn   add     NAS   NMN   NOP      PCC
 1    888   hnm    777    ccc       888        TTT
 2    999   ujk      666    ddd       999       MMM
 3    000   NNN
 4    444   IKK

I would like to use file1 as the main file and keep all its records even when there is no ID match with the other files.

 Thanks,

 Victor
vcharlesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi Victor;

This well do what you asked for in your question.
'' Your First XML file
Dim doc1 As XDocument = XDocument.Load("C:\Working Directory\ExcelBookData1.xml")
'' Your Second XML file
Dim doc2 As XDocument = XDocument.Load("C:\Working Directory\ExcelBookData2.xml")
'' Your Third XML file
Dim doc3 As XDocument = XDocument.Load("C:\Working Directory\ExcelBookData3.xml")
'' Resulting XML file
Dim doc12 As New XDocument(New XElement("Root"))

'' Join the row of the XML files on "ID" and creating a new XElement for each.
Dim result = (From d1 In doc1.Root.Descendants("Table1")
              Select New XElement("Table1",
                New XElement("ID", d1.Element("ID").Value),
                New XElement("NSN", d1.Element("NSN").Value),
                New XElement("ADD", d1.Element("ADD").Value),
                New XElement("NAS", 
                    (doc2.Descendants("Table1").
                         Where(Function(t) t.Element("ID").Value = d1.Element("ID").Value).
                         Select(Function(t) t.Element("NAS").Value)).FirstOrDefault()),
                New XElement("NMN", 
                    (doc2.Descendants("Table1").
                         Where(Function(t) t.Element("ID").Value = d1.Element("ID").Value).
                         Select(Function(t) t.Element("NMN").Value)).FirstOrDefault()),
                New XElement("NOP",
                    (doc3.Descendants("Table1").
                         Where(Function(t) t.Element("ID").Value = d1.Element("ID").Value).
                         Select(Function(t) t.Element("NOP").Value)).FirstOrDefault()),
                New XElement("PCC",
                    (doc3.Descendants("Table1").
                         Where(Function(t) t.Element("ID").Value = d1.Element("ID").Value).
                         Select(Function(t) t.Element("PCC").Value)).FirstOrDefault())
              )).ToList()

'' Add new elements to the new XDocument
result.ForEach(Sub(node) doc12.Root.Add(node))
Save the new XDocument
''doc12.Save("Path on the File Syatem")

Open in new window


Assuming that the XML files are in the following specification.
File 1
<Root>
  <Table1>
    <ID>1</ID>
    <NSN>888</NSN>
    <ADD>hnm</ADD>
  </Table1>
  <Table1>
    <ID>2</ID>
    <NSN>999</NSN>
    <ADD>ujk</ADD>
  </Table1>
  <Table1>
    <ID>3</ID>
    <NSN>000</NSN>
    <ADD>NNN</ADD>
  </Table1>
  <Table1>
    <ID>4</ID>
    <NSN>444</NSN>
    <ADD>IKK</ADD>
  </Table1>  
</Root>

File 2
<Root>
  <Table1>
    <ID>1</ID>
    <NAS>777</NAS>
    <NMN>ccc</NMN>
  </Table1>
  <Table1>
    <ID>2</ID>
    <NAS>666</NAS>
    <NMN>ddd</NMN>
  </Table1>
</Root>

File 3
<Root>
  <Table1>
    <ID>1</ID>
    <NOP>888</NOP>
    <PCC>TTT</PCC>
  </Table1>
  <Table1>
    <ID>2</ID>
    <NOP>999</NOP>
    <PCC>MMM</PCC>
  </Table1>
</Root>

Resulting XML file
<Root>
  <Table1>
    <ID>1</ID>
    <NSN>888</NSN>
    <ADD>hnm</ADD>
    <NAS>777</NAS>
    <NMN>ccc</NMN>
    <NOP>888</NOP>
    <PCC>TTT</PCC>
  </Table1>
  <Table1>
    <ID>2</ID>
    <NSN>999</NSN>
    <ADD>ujk</ADD>
    <NAS>666</NAS>
    <NMN>ddd</NMN>
    <NOP>999</NOP>
    <PCC>MMM</PCC>
  </Table1>
  <Table1>
    <ID>3</ID>
    <NSN>000</NSN>
    <ADD>NNN</ADD>
    <NAS />
    <NMN />
    <NOP />
    <PCC />
  </Table1>
  <Table1>
    <ID>4</ID>
    <NSN>444</NSN>
    <ADD>IKK</ADD>
    <NAS />
    <NMN />
    <NOP />
    <PCC />
  </Table1>
</Root>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
Thank You.
0
vcharlesAuthor Commented:
Thank You.
0
Fernando SotoRetiredCommented:
Not a problem Victor, glad I was able to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

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.