Help with creating new XML file where data field in file1 match data field in file 2 using VB.NET

Hi,

If file1 contains the following data:

<Root>
<Table>
<ID>1</ID>
<NAS>XXXX</NAS>
</Table>
<Table>
<ID>2</ID>
<NAS>YYYY</NAS>
</Table>
<Table>
<ID>3</ID>
<NAS>ZZZZ</NAS>
</Table>
</Root>

How do I create File3.xml with data from File 2 for all NAS values in file1 matching NAS values in File2?

File 2:

<Root>
<Table>
<ID>1</ID>
<NAS>XXXX</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
<Table>
<ID>2</ID>
<NAS>YYYY</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
<Table>
<ID>3</ID>
<NAS>ZZZZ</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
<Table>
<ID>4</ID>
<NAS>ZZZZ</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
<Table>
<ID>5</ID>
<NAS>OOOO</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
<Table>
<ID>6</ID>
<NAS>NNNN</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
</Root>

Based on NAS values in File1 the results in File3.xml should be as follows:


<Root>
<Table>
<ID>1</ID>
<NAS>XXXX</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
<Table>
<ID>2</ID>
<NAS>YYYY</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
<Table>
<ID>3</ID>
<NAS>ZZZZ</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
<Table>
<ID>4</ID>
<NAS>ZZZZ</NAS>
<ADG>VVVVV<AGD>
<AKS>UUUUU<AKS>
</Table>
</Root>


Thanks,

Victor
vcharlesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

it_saigeDeveloperCommented:
This is what you are after, I believe:
Imports System.Xml
Imports System.Xml.Serialization
Imports System.Runtime.CompilerServices
Imports System.IO

Module Module1
	Private source As New Root
	Private primer As New Root
	Private target As New Root

	Sub Main()
		source = New FileInfo("Source.xml").ToObject(Of Root)()
		primer = New FileInfo("Key.xml").ToObject(Of Root)()

		target.Table.AddRange(From src In source.Table _
			 From key In primer.Table _
			 Where key.NAS.Equals(src.NAS)
			 Select src)

		target.ToXml(New FileInfo("Final.xml"))
	End Sub
End Module

<Serializable()> _
Public Class Root
	<XmlElement("Table")> Public Property Table() As New List(Of Table)
End Class

<Serializable()> _
Public Class Table
	Public Property ID() As Integer
	Public Property NAS() As String
	Public Property ADG() As String
	Public Property AKS() As String
End Class

Module Extensions
	<Extension()> Public Sub ToXml(Of T)(ByVal source As T, ByVal file As FileInfo)
		Dim serializer As New XmlSerializer(GetType(T))
		Dim ns As New XmlSerializerNamespaces(New XmlQualifiedName() {New XmlQualifiedName("", "")})
		Using writer As New StreamWriter(file.FullName)
			serializer.Serialize(writer, source, ns)
		End Using
	End Sub

	<Extension()> Public Function ToObject(Of T)(ByVal file As FileInfo) As Object
		Dim resultObject As Object
		Dim xRoot As New XmlRootAttribute() With {.ElementName = "Root"}
		Dim serializer As New XmlSerializer(GetType(T), xRoot)
		Using reader As New StreamReader(file.FullName)
			resultObject = serializer.Deserialize(reader)
		End Using
		Return resultObject
	End Function
End Module

Open in new window

With these XML Files -Key.xmlSource.xmlProduces the following output -Final.xml-saige-
0
vcharlesAuthor Commented:
Hi,

Thanks for the code. I receive error message:

Type list is not defined on line

Public Class Root
    <XmlElement("Table")> Public Property Table() As New List(Of Table)
End Class

How do I remove this error?

Victor
0
it_saigeDeveloperCommented:
List is in System.Collections.Generic.  You can either:

A.  Select the word List and press Ctrl + . (thats the Control key and the Period key) in order to resolve the reference.
B.  Add this to the top of your code file:
Imports System.Collections.Generic

Open in new window


Otherwise ensure that the spelling is correct, it is case sensitive List as opposed to list.

New code file
Imports System.Collections.Generic
Imports System.Xml
Imports System.Xml.Serialization
Imports System.Runtime.CompilerServices
Imports System.IO

Module Module1
	Private source As New Root
	Private primer As New Root
	Private target As New Root

	Sub Main()
		source = New FileInfo("Source.xml").ToObject(Of Root)()
		primer = New FileInfo("Key.xml").ToObject(Of Root)()

		target.Table.AddRange(From src In source.Table _
			 From key In primer.Table _
			 Where key.NAS.Equals(src.NAS)
			 Select src)

		target.ToXml(New FileInfo("Final.xml"))
	End Sub
End Module

<Serializable()> _
Public Class Root
	<XmlElement("Table")> Public Property Table() As New List(Of Table)
End Class

<Serializable()> _
Public Class Table
	Public Property ID() As Integer
	Public Property NAS() As String
	Public Property ADG() As String
	Public Property AKS() As String
End Class

Module Extensions
	<Extension()> Public Sub ToXml(Of T)(ByVal source As T, ByVal file As FileInfo)
		Dim serializer As New XmlSerializer(GetType(T))
		Dim ns As New XmlSerializerNamespaces(New XmlQualifiedName() {New XmlQualifiedName("", "")})
		Using writer As New StreamWriter(file.FullName)
			serializer.Serialize(writer, source, ns)
		End Using
	End Sub

	<Extension()> Public Function ToObject(Of T)(ByVal file As FileInfo) As Object
		Dim resultObject As Object
		Dim xRoot As New XmlRootAttribute() With {.ElementName = "Root"}
		Dim serializer As New XmlSerializer(GetType(T), xRoot)
		Using reader As New StreamReader(file.FullName)
			resultObject = serializer.Deserialize(reader)
		End Using
		Return resultObject
	End Function
End Module

Open in new window


-saige-
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

vcharlesAuthor Commented:
Thanks, will try it and get back to you.
0
Fernando SotoRetiredCommented:
Hi Victor;

This should do what you are looking for.
'' Load XML files into memory
Dim xdoc1 = XDocument.Load("C:\Working Directory\FILE1.xml")
Dim xdoc2 = XDocument.Load("C:\Working Directory\FILE2.xml")

'' Get the values of all the NAS nodes to be allowed in FILE3.xml
Dim xdocNAS As List(Of String) = (From node In xdoc1.Descendants("NAS")
                                  Select node.Value).ToList()

'' Get all Table nodes that contain a NAS node that do not match the values in xdocNAS list
Dim xdocSelect As List(Of XElement) = (From node In xdoc2.Descendants("NAS")
                                       Where Not xdocNAS.Contains(node.Value)
                                       Select node).ToList()

'' Remove all nodes in the xdocSelect List
For Each node In xdocSelect
    node.Parent.Remove()
Next

'' Save the modified xdoc2 as the new FILE3.xml file
xdoc2.Save("C:\Working Directory\FILE3.xml")

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
Fernando SotoRetiredCommented:
Not a problem Victor, as always glad 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.