Link to home
Start Free TrialLog in
Avatar of Victor  Charles
Victor CharlesFlag for United States of America

asked on

Help with reorganizing data elements in File2 based on File1 using VB.NET

Hi,

How do I rearrange data element in File2 based on the way they are arranged in File1 using VB.NET. File1 has not data in the data elements, it's being used to reformat File2.

For example using only one record, if file1contains the following format.

<Root>
<table>
<NSC><NSC>
<AGD><AGD>
<NSN><NSN>
<UAD><UAD>
</table>
</Root>

and file2 contains 1 record:

<Root>
<table>
<UAD>aa<UAD>
<NSN>bb<NSN>
<AGD>cc<AGD>
<NSC>dd<NSC>
</table>
</Root>

File3 should have the following format:

<Root>
<table>
<NSC>aa<NSC>
<AGD>bb<AGD>
<NSN>cc<NSN>
<UAD>dd<UAD>
</table>
</Root>

Actual file2 has multiple records.

Thanks,

Victor
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

Please note that your XML samples are not well-formed. There are issues with the closing tags (missing a '/')
Dim file1 as XmlDocument = new XmlDocument()
Dim file2 as XmlDocument = new XmlDocument()

file1.Load ("file1.xml")
file2.Load ("file2.xml")

For f2_record_ix As Integer = 0 to file2.DocumentElement.ChildNodes.Count - 1

	Dim f2_record As XmlNode = file2.DocumentElement.ChildNodes(f2_record_ix)
	
	For f2_node_ix As Integer = 0 to f2_record.ChildNodes.Count - 1
	
		Dim f2_node As XmlNode = f2_record.ChildNodes(f2_node_iX)
		
		If f2_record_ix <= file1.DocumentElement.ChildNodes.Count - 1 Then 'just make sure we have a corresponding index
		
			Dim f1_record As XmlNode = file1.DocumentElement.ChildNodes(f2_record_ix)
			
			If f2_node_iX <= f1_record.ChildNodes.Count - 1 Then
			
				Dim f1_node As XmlNode = f1_record.ChildNodes(f2_node_iX)
				f1_node.InnerText = f2_node.InnerText

			End If
		
		End If
		
	Next

Next

file1.Save("file3.xml")

Open in new window

Avatar of Victor  Charles

ASKER

I forgot to include them, will try your code  and get back to you.
 Thanks.
Hi Victor;

From my understanding of your question please answer the following.

1. File1 will only have one table node in the document and no more?
2. Knowing that XML documents are case sensitive is the node name table or something else like Table1?
3. File2 may / will have multiple table or maybe Table1 nodes which need to be sorted in the order shown in file1?
4. Please note that in your example File3, the resulting XML document, that the nodes have been sorted but also that the values in the original nodes have also be re-assigned to different nodes. Is this what you really want to do?

Please answer all questions. Thank you.
1. File1 will only have one table node in the document and no more?
Yes
 2. Knowing that XML documents are case sensitive is the node name table or something else like Table1?
Yes, it is "Table1" for both files

 3. File2 may / will have multiple table or maybe Table1 nodes which need to be sorted in the order shown in file1?

File2 will have the same table name with multiple records.

 4. Please note that in your example File3, the resulting XML document, that the nodes have been sorted but also that the values in the original nodes have also be re-assigned to different nodes. Is this what you really want to do?

My mistake, file3 should be

<Root>
 <table>
 <NSC>dd<NSC>
 <AGD>cc<AGD>
 <NSN>bb<NSN>
 <UAD>aa<UAD>
 </table>
 </Root>

Thanks,

Victor
Oh dear!  It certainly appeared as if you wanted to reassign the values by node index/position and you wanted to support multiple <table> nodes. That said, it should be trivial to alter how I find the f1_node to use XPath to find a similarly named node as opposed to using and index.

It helps to be very specific and to provide correct examples of the inputs and outputs you are working with.

File2 will have the same table name with multiple records.
How will this look? How are you then supposed to match these multiple nodes onto the single node in file1?
This version used the name of the node to find the corresponding node in file1. It should be easy to tweak this.
Dim file1 as XmlDocument = new XmlDocument()
Dim file2 as XmlDocument = new XmlDocument()

file1.Load ("data1.xml")
file2.Load ("data2.xml")

Dim f1_record As XmlNode = file1.DocumentElement.ChildNodes(0)

For f2_record_ix As Integer = 0 to file2.DocumentElement.ChildNodes.Count - 1

	Dim f2_record As XmlNode = file2.DocumentElement.ChildNodes(f2_record_ix)
	
	For f2_node_ix As Integer = 0 to f2_record.ChildNodes.Count - 1
	
		Dim f2_node As XmlNode = f2_record.ChildNodes(f2_node_iX)
		
		If f2_record_ix <= file1.DocumentElement.ChildNodes.Count - 1 Then 'just make sure we have a corresponding index
		
			Dim f1_node As XmlNode = f1_record.SelectSingleNode(f2_node.Name)
			If f1_node IsNot Nothing Then
				
				f1_node.InnerText = f2_node.InnerText

			End If
		
		End If
		
	Next

Next

Open in new window

Hi,

The code rearranges the dataelements in file2 but it only returns one record, how do I modify it to work for all the records in file2?

Thanks,

Victor
Help!
File2 will have the same table name with multiple records.
How will this look? How are you then supposed to match these multiple nodes onto the single node in file1?

Can't visualise this without an example. File1 and file3 will always have 1 node... Yes?
Hi,

I will give an example uising multiple records for file2, file1 will always have one record containing the proper order of the dataelements.

if file1contains the following format.

 <Root>
 <table>
 <NSC><NSC>
<AGD><AGD>
<NSN><NSN>
<UAD><UAD>
</table>
 </Root>

 and file2 contains the following 4 records:

 <Root>
 <table>
<ID>1</ID>
 <UAD>aa<UAD>
<NSN>bb<NSN>
<AGD>cc<AGD>
<NSC>dd<NSC>
</table>
<table>
 <ID>2</ID>
 <UAD>aa2<UAD>
<NSN>bb2<NSN>
<AGD>cc2<AGD>
<NSC>dd2<NSC>
</table>
<table>
 <ID>3</ID>
 <UAD>aa3<UAD>
<NSN>bb3<NSN>
<AGD>cc3<AGD>
<NSC>dd3<NSC>
</table>
<table>
 <ID>4</ID>
 <UAD>aa4<UAD>
<NSN>bb4<NSN>
<AGD>cc4<AGD>
<NSC>dd4<NSC>
</table>
 </Root>

 File3 should have the following format:

 <Root>
 <table>
 <ID>1</ID>
 <NSC>aa<NSC>
<AGD>bb<AGD>
<NSN>cc<NSN>
<UAD>dd<UAD>
</table>
<table>
<ID>2</ID>
 <NSC>aa2<NSC>
<AGD>bb2<AGD>
<NSN>cc2<NSN>
<UAD>dd2<UAD>
</table>
<table>
<ID>3</ID>
 <NSC>aa3<NSC>
<AGD>bb3<AGD>
<NSN>cc3<NSN>
<UAD>dd3<UAD>
</table>
<table>
 <ID>4</ID>
 <NSC>aa4<NSC>
<AGD>bb4<AGD>
<NSN>cc4<NSN>
<UAD>dd4<UAD>
</table>
 </Root>

Thanks,

Victor
SOLUTION
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

Thank you, do I need to import a DLL?

I received error message " 'dump' is not a member of 'String'.

on line

f3_stringbuilder.ToString().Dump()

Also is the last line still file1.Save("file3.xml")? I didn't notice it on your last code.
.Dump() Sorry, you can remove that bit. I work on and test code samples using LinqPad (give it a shot too), and it uses a special built .Dump() to show the contents of a variable.

The output, this time I just rather put the XML for File3 into a StringBuilder as opposed to a file.
f3_stringbuilder.ToString() will have the contents of File3. You can write that to a file if you like. Make that last line with the .Dump() a
System.IO.File.WriteAllText("file3.xml", f3_stringbuilder.ToString())

Open in new window

Note that you can also comment/uncomment line 30 and 31 to get slightly different behaviour as well.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

I received the following error:

Object reference not set to an instance of an object.

on line:

f3.WriteElementString(name, f2_node.InnerText)


Any ideas what is causing this error?

Thanks,
The code runs against your most recent file samples. I have not done error checking, like if files have missing nodes or different names. Was just giving a baseline on top of which you could refine things.
Thank You for both solutions.