Help with appending data elememt from xmlfile1 to xmlfil2 based on identical IDs

vcharles
vcharles used Ask the Experts™
on
Hi,


If file1.xml contains

<Table1>
<SN>10411</SN>
<NSC>ITEMA</NSC>
</Table1>
<Table1>
<SN>10412</SN>
<NSC>ITEMB</NSC>
</Table1>
<Table1>
<SN>10421</SN>
<NSC>ITEMC</NSC>
</Table1>
<Table1>
<SN>10521</SN>
<NSC>ITEMD</NSC>
</Table1>
<Table1>
<SN>10611</SN>
<NSC>ITEME</NSC>
</Table1>


and file2.xml contains

<Table1>
<SN>10411</SN>
<NSD>ITEMY</NSD>
</Table1>
<Table1>
<SN>10412</SN>
<NSD>ITEMV</NSD>
</Table1>
<Table1>
<SN>10421</SN>
<NSD>ITEMW</NSD>
</Table1>

How do I append NSD IN file1.xml where N have identical values using VB.NET?

Victor
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fernando SotoRetired
Distinguished Expert 2017

Commented:
The file1.xml does NOT have a node with NSD in it. Also please show what you want the resulting file2.xml to look like after the operation is complete.

Author

Commented:
Hi,

Just read your post. Based on the example I gave, File3.xml should look as follows:

<Table1>
<SN>10411</SN>
<NSC>ITEMA</NSC>
<NSD>ITEMY</NSD>
</Table1>
<Table1>
<SN>10412</SN>
<NSC>ITEMB</NSC>
<NSD>ITEMV</NSD>
</Table1>
<Table1>
<SN>10421</SN>
<NSC>ITEMC</NSC>
<NSD>ITEMV</NSD>
</Table1>
<Table1>
<SN>10521</SN>
<NSC>ITEMD</NSC>
</Table1>
<Table1>
<SN>10611</SN>
<NSC>ITEME</NSC>
</Table1>
Fernando SotoRetired
Distinguished Expert 2017

Commented:
OK, having the new file1.xml and the old file2.xml what should the new file2.xml look like.?
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Carl TawnSenior Systems and Integration Developer

Commented:
You need something along the lines of the following:
        '// assuming "doc1" has file1 loaded and "doc2" has file2 loaded

        Dim nodes As XmlNodeList = doc2.SelectNodes("//Table1")
        Dim copyNode As XmlNode
        Dim targetNode As XmlNode

        For Each node As XmlNode In nodes

            targetNode = doc1.SelectSingleNode("//Table1[SN='" & node("SN").InnerText & "']")
            If Not targetNode Is Nothing Then
                copyNode = targetNode.OwnerDocument.ImportNode(node("NSD"), True)
                targetNode.AppendChild(copyNode)
            End If

        Next

Open in new window

Author

Commented:
Hi,

Once I combine the data element from file2 with file1 to create a new file, I don't need file2.

Thanks,

Victor
Carl TawnSenior Systems and Integration Developer

Commented:
OK, so ditch it then. The code i posted appends the nodes to file1, you just need to call Save() on the XmlDocument object to commit it to disk. You can do what you like with file2 after that point.

Author

Commented:
Hi,

I will try it and get back to you.

Thank You.

Victor
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Victor;

It looks like the two XML files are NOT well formed, they are both missing a root node and therefore cannot be loaded into an XML Document. Does the actual documents have a root node and if not is there an issue of adding one?

Author

Commented:
Hi,

What I initially posted was just an example, I forgot to include the <Root> </Root> nodes. Yes, please inlcude them.

Thanks,

Victor
Retired
Distinguished Expert 2017
Commented:
Hi Victor;

This should give you what you are looking for.

' 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") _
              Join n2 In f2.Descendants("Table1") On n1.Element("SN").Value Equals 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

Author

Commented:
Thank You. I will try it and get back to you.

Author

Commented:
Hi,

How do I declare XDocument? tried to declare it XMLDocument but that didn't work.

V.
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Imports System.Xml.Linq

and is found in the dll System.Xml.Linq.dll

Author

Commented:
Hi,

It worked perfectly.

Thanks,

Victor
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Not a problem, glad to help.

Author

Commented:
Hi Fernando,

I am receiving error message  "Object reference not set to an instance of an object." on line:
 Join n2 In f2.Descendants("Table1") On n1.Element("SN").Value Equals n2.Element("SN").Value
and n2.Element("SN").Value  is highlighted as specific cause of error.

which I think is because some records don't have any matching values, for example file1 may have an SN where there is no matching SN in file 2.

How do I avoid this error?

Thanks,

Victor
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Victor;

Please open a new question for this. You can post here the link to the new post and I will have a look. In your new post please post sample xml files that cause the specific error.

Thanks.

Author

Commented:
OK. Will do.

Thanks,

Victor

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial