[RESOLVED] [2008] XML advice, please

[penguin5000]

Hello,
I've been given a sample XML file in a structure that I haven't handled in VB.NET before (see image below), and would appreciate some guidance.

When I read the xml file in with:

Dim ds As DataSet = New DataSet("dataset")

ds.ReadXml("prod2.xml")

With DataGridView1
        .DataSource = ds
        .DataMember = ds.Tables(2).TableName
End With

I can see that I've got 3 x tables ... "Interface", "Product", "Value".

What I'm wondering is how do I get the "Value" table linked to "Product"? I was expecting (rightly or wrongly) to see some inherited values from product displayed in my DataGridView. Looking at the structure of the XML file, it seems to imply that there should be some inheritance???

[wild_bill]

Can you open your file in notepad, and paste the data between code tags, rather than posting the image?

[penguin5000]

Thanks for your reply.
I'm attaching the actual xml file ...

[mendhak]

Value applies to each product. It'd be a property/column of a product, not a separate table. What makes you say it's another table?

[penguin5000]

When I loop through the DataSet tables to display each TableName, it confirms that it has been broken up into the tables I mentioned, and when I think about the way the company operates, it would make sense for them to provide a history of product value changes for each product (the XML file is a database product update file).

[mendhak]

That may unnecessarily complicate things. You're better off using XPathNavigator or XmlDocument to read each product and its corresponding value , price, currency, etc.

[penguin5000]

Ahhhh - ok, thanks!

[penguin5000]

Hi, I'm really struggling with this. Looking at MSDN, it seems to me that the XMLReader is perhaps the best one to use as I don't need to write back to the file.

The structure of the xml file seems very awkward to work with.

For example, I can get it to read each attribute of this line:

<Interface CompanyCode="COMP01" CreatedOn="2007-05-27T10:20:00" CreatedBy="PRODUCT" CreatorKey="PROD" SequenceNo="1" CreatorReference="NEWPRODUCT">

By using the following code:

Dim reader As XmlReader = XmlReader.Create("prod2.xml")
reader.Read()
reader.MoveToFirstAttribute()
For x As Integer = 0 To reader.AttributeCount - 1
    Console.WriteLine(reader.Name & " : " & reader.Item(x).ToString & vbCrLf)
    reader.MoveToNextAttribute()
Next

But then it seems that I have to switch to some other way of continuing to read the file because the attributes aren't all along the same line.

It can't be this difficult, surely?

[mendhak]

The XPathNavigator is also a cursor-like model for reading XML which is why I suggested it. You can use the XPathNavigator's Select() method to specify a node or attribute you want. Example:

navigator.Select("/root/interface/@CreatedBy")

[wild_bill]

The data set will have relationship object after you read in the xml, then you can grab the child value rows for each product row using the relationship:

Dim ds As New DataSet
ds.ReadXml("C:\testdata.xml")
For Each productRow As DataRow In ds.Tables("Product").Rows
    Console.Write(productRow("productcode").ToString & " " & productRow("description").ToString)
    For Each valueRow As DataRow In productRow.GetChildRows("Product_Value")
        Console.Write(" " & valueRow("Price").ToString & " " & valueRow("currency").ToString)
    Next
    Console.WriteLine()
Next

[penguin5000]

Thank you very much for the code snippet. It is really appreciated. I've been battling with this all day, and was getting myself ready for a long night!