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
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.

MlandaTCommented:
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

0
vcharlesAuthor Commented:
I forgot to include them, will try your code  and get back to you.
 Thanks.
0
Fernando SotoRetiredCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

vcharlesAuthor Commented:
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
1
MlandaTCommented:
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?
0
MlandaTCommented:
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

0
vcharlesAuthor Commented:
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
0
vcharlesAuthor Commented:
Help!
0
MlandaTCommented:
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?
0
vcharlesAuthor Commented:
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
0
MlandaTCommented:
Here you go.
Dim file1 as XmlDocument = new XmlDocument()
Dim file2 as XmlDocument = new XmlDocument()

file1.Load ("c:\temp\file1.txt")
file2.Load ("c:\temp\file2.txt")
'file1.LoadXml ("xml_string")
'file2.LoadXml ("xml_string")

Dim f3_stringbuilder As new StringBuilder()
dim settings As new XmlWriterSettings() With {.Indent = true}
Dim f3 As XmlWriter = XmlWriter.Create(f3_stringbuilder, settings)

Dim nodeOrder as new List(of string)
nodeOrder.Add("ID")
for each node as XmlNode in file1.DocumentElement.ChildNodes(0).ChildNodes
	nodeOrder.Add(node.Name)
next

f3.WriteStartDocument()
f3.WriteStartElement("Root")

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

	f3.WriteStartElement("table")

	Dim f2_record As XmlNode = file2.DocumentElement.ChildNodes(f2_record_ix)
	
	For each name as string in nodeOrder
	
		'Dim f2_node As XmlNode = f2_record.SelectSingleNode(name) 'if you want to reorganise both nodes and values
		Dim f2_node As XmlNode = f2_record.ChildNodes(nodeOrder.IndexOf(name)) 'if you want to reorganise nodes, but not the values (as in your example)
		f3.WriteElementString(name, f2_node.InnerText)
		
	Next
	
	f3.WriteEndElement()
	
Next

f3.WriteEndDocument()
f3.Flush()
f3.Close()

f3_stringbuilder.ToString().Dump()

Open in new window

0
vcharlesAuthor Commented:
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.
0
MlandaTCommented:
.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.
0
Fernando SotoRetiredCommented:
Hi Victor;

This should give you what you need. Please please review you code when posting to remove errors.
'' Load both XML documents into memory
Dim xdocFile1a = XDocument.Load("C:\Working Directory\VictorFile1a.xml")
Dim xdocFile2a = XDocument.Load("C:\Working Directory\VictorFile2a.xml")

'' Get a list of node names in the order to order the nodes
Dim sortOrder As List(Of String) = (From n In xdocFile1a.Descendants("Table1").Elements()
                                    Select n.Name.LocalName).ToList()

'' Get a reference to all the Table1 nodes
Dim results = (From n In xdocFile2a.Descendants("Table1")
               Select n.Elements().ToList()).ToList()

'' Create the new XML document to have the sorted nodes
Dim xdocFile3a = New XDocument(New XElement("Root"))
'' Go through all the Table1 nodes and order there children and add to new document
For Each item As List(Of XElement) In results
    Dim table As New XElement("Table1")
    table.Add(item.Find(Function(n) n.Name.LocalName = "ID"))
    For Each sortName As String In sortOrder
        table.Add(item.Find(Function(n) n.Name.LocalName = sortName))
    Next
    xdocFile3a.Root.Add(table)
Next    

xdocFile3a.Save("Path and filename to save to")

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:
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,
0
MlandaTCommented:
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.
0
vcharlesAuthor Commented:
Thank You for both solutions.
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.