Click here to Skip to main content
15,893,644 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
need a little help to create dataset-object from XML-File. File-content is a response to a request on a server, so I have no control on XML-Structure. Content of file (filename "TestProd5.xml") is:

XML
<?xml version="1.0"?>
<ProductionOrder xmlns="http://www.sap.com/SBO/DIS">
	<AbsoluteEntry>17830</AbsoluteEntry>

		<ProductionOrderLine>
			<LineNumber>0</LineNumber>
			<BatchNumbers/>
		</ProductionOrderLine>

		<ProductionOrderLine>
			<LineNumber>1</LineNumber>
			<BatchNumbers>
				<BatchNumber>
					<BatchNumber>234</BatchNumber>
					<Quantity>1.000000</Quantity>
					<BaseLineNumber>1</BaseLineNumber>
				</BatchNumber>
			</BatchNumbers>
		</ProductionOrderLine>

		<ProductionOrderLine>
			<LineNumber>2</LineNumber>
			<BatchNumbers>
				<BatchNumber>
					<BatchNumber>234</BatchNumber>
					<Quantity>2.000000</Quantity>
					<BaseLineNumber>2</BaseLineNumber>
				</BatchNumber>
			</BatchNumbers>
		</ProductionOrderLine>

		<ProductionOrderLine>
			<LineNumber>3</LineNumber>
			<BatchNumbers/>
		</ProductionOrderLine>
</ProductionOrder>


What I have tried:

I try to load data by:

Dim dsProd As DataSet = New DataSet()
dsProd.ReadXml("TestProd5.xml")


Works quite well, except elements <BatchNumber>234</BatchNumber> is ommitted in dataset. As I assume reason is parent element with same name. I need this value in
dataset-object. What canI do?

Any help is welcome.


Manfred
Posted
Updated 12-Feb-19 5:51am
v2

1 solution

There's few ways to load XML data, using:

1) XmlDocument[^]
2) XDocument[^]
3) XmlSerialization[^] => Examples of XML Serialization | Microsoft Docs[^]

[EDIT]
As i mentioned eariler, you can use XDocument class to read your data into DataSet object. This is an idea:
VB
Function LoadDataIntoDataSet() As DataSet

	Dim xNamespace As XNamespace = "http://www.sap.com/SBO/DIS"
	Dim xDoc As XDocument = XDocument.Parse(GetXmlData())
	Dim ds As DataSet = New DataSet()
	Dim pl As DataTable = New DataTable()
	pl.Columns.Add(New DataColumn("LineNumber", Type.GetType("System.Int32")))
	
	Dim bn As DataTable = New DataTable()
	bn.Columns.Add(New DataColumn("LineNumber", Type.GetType("System.Int32")))
	bn.Columns.Add(New DataColumn("BatchNumber", Type.GetType("System.Int32")))
	bn.Columns.Add(New DataColumn("Quantity", Type.GetType("System.String")))
	bn.Columns.Add(New DataColumn("BaseLineNumber", Type.GetType("System.Int32")))
	
	For Each pol As XElement In xDoc.Descendants(xNamespace + "ProductionOrderLine")
		pl.Rows.Add(New Object(){pol.Element(xNamespace + "LineNumber").Value})
		For Each ban As XElement In pol.Descendants(xNamespace + "BatchNumber").Where(Function(x) x.Descendants.Count>0) 
			bn.Rows.Add(New Object() _
				{ _
					pol.Element(xNamespace + "LineNumber").Value, _
					ban.Element(xNamespace + "BatchNumber").Value, _
					ban.Element(xNamespace + "Quantity").Value, _
					ban.Element(xNamespace + "BaseLineNumber").Value _
				})
		Next
	Next
	
	ds.Tables.Add(pl)
	ds.Tables.Add(bn)
	Return ds

End Function


But, i'd strongly recommend to use serialization (via DataContractSerializer Class (System.Runtime.Serialization) | Microsoft Docs[^]). In this case, you'll need set of definition of classes:

VB.NET
'needs fererence to System.Runtime.Serialization.dll
<DataContract([Namespace] := "http://www.sap.com/SBO/DIS")>
Public Class ProductionOrder
	<DataMember>
	Public AbsoluteEntry As Integer
	<DataMember>
	Public ProductionOrderLines As List(Of ProductionOrderLine)
End Class

<DataContract>
Public Class ProductionOrderLine
	<DataMember>
	Public LineNumber As Integer
	<DataMember>
	Public BatchNumbers As List(Of BatchNumber)
End Class

<DataContract>
Public Class BatchNumber
	<DataMember>
	Public BatchNumber As Integer
	<DataMember>
	Public Quantity As String
	<DataMember>
	Public BaseLineNumber As Integer
End Class


Then you'll be able to convert xml data into objects:
VB
'this method you'll need only first time!!!
Dim sFileName As String = "FullFileName.xml"
Dim xDoc As XDocument = XDocument.Load(sFileName)
Dim xns As XNamespace = "http://www.sap.com/SBO/DIS"

Dim po As ProductionOrder =  New ProductionOrder()
po.AbsoluteEntry = xDoc.Root.Element(xns + "AbsoluteEntry").Value
po.ProductionOrderLines = New List(Of ProductionOrderLine)
For Each pol As XElement In xDoc.Descendants(xns + "ProductionOrderLine")
    Dim pl As ProductionOrderLine = New ProductionOrderLine()
    pl.LineNumber = pol.Element(xns + "LineNumber").Value
    pl.BatchNumbers = New List(Of BatchNumber)
    For Each ban As XElement In pol.Descendants(xns + "BatchNumber").Where(Function(x) x.Descendants.Count>0)
        Dim bn As BatchNumber = New BatchNumber()
        With bn
            .BatchNumber =  ban.Element(xns + "BatchNumber").Value
            .Quantity = ban.Element(xns + "Quantity").Value
            .BaseLineNumber = ban.Element(xns + "BaseLineNumber").Value
        End With
        pl.BatchNumbers.Add(bn)
    Next
    po.ProductionOrderLines.Add(pl)
Next

'now you can write your objects into proper way:
WriteObject(sFileName, po)
'read it again:
po = ReadObject(sFileName)
'gotcha!


The methods to read/write object:

VB
Sub WriteObject(_fileName As String, po As ProductionOrder)
    Using writer As FileStream = New FileStream(_fileName, FileMode.Create)
		Dim knownTypes() AS Type = New Type() {GetType(ProductionOrder), GetType(ProductionOrder), GetType(BatchNumber) }
		Dim dcs As DataContractSerializer = New DataContractSerializer(GetType(ProductionOrder), knownTypes)
	    dcs.WriteObject(writer, po)
	    writer.Close()
	End Using
End Sub

Function ReadObject(_fileName As String) As ProductionOrder 
	Dim po As ProductionOrder = New ProductionOrder()
	
    Using fs As FileStream = New FileStream(_fileName, FileMode.Open)
	    Dim reader As XmlDictionaryReader = XmlDictionaryReader.CreateTextReader(fs, New XmlDictionaryReaderQuotas())
		Dim knownTypes() AS Type = New Type() {GetType(ProductionOrder), GetType(ProductionOrder), GetType(BatchNumber) }
	    Dim dcs As DataContractSerializer = New DataContractSerializer(GetType(ProductionOrder), knownTypes)
	    po = DirectCast(dcs.ReadObject(reader, True), ProductionOrder)
		reader.Close()
	    fs.Close()
	End Using

	Return po
End Function


This is the way you can deal with xml data and custom objects!
 
Share this answer
 
v2
Comments
MaReBo 13-Feb-19 4:54am    
Thanks for your solution! All 3 suggested ways lead to same result: Elements in XML-Docs whose names are not unique are ignored when loading into dataset-object. I think, I have to edit XML-File manually to rename specific nodes. Tried to avoid this workaround.
Maciej Los 13-Feb-19 9:41am    
See updated answer.
MaReBo 13-Feb-19 12:01pm    
Thank you! I think this will work and I'll try this for my production-order-class. Takes a little time to work through this, but at the end it will be a stable solution.
Maciej Los 13-Feb-19 15:03pm    
You're very welcome.
Cheers
Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900