How to select data from XML files

[ma701ss]

Hi,

I have the following XML file (sample):

<CATEGORY name="cat1" id="1">
---<PRODUCT ITEM="100">
------<WEIGHT>0.15</WEIGHT>
------<NAME>A Product</NAME>
------<PRICE>10.00</PRICE>
---</PRODUCT>
</CATEGORY>
<CATEGORY name="cat2" id="2">
---<PRODUCT ITEM="200">
------<WEIGHT>0.15</WEIGHT>
------<NAME>Another Product</NAME>
------<PRICE>20.00</PRICE>
---</PRODUCT>
</CATEGORY>

I use the following code to load the XML into a dataset and build a table of "products":

Dim ds As New DataSet
Dim streamRead As New System.IO.FileStream(AppSettings.Item("SECURED_ASSETS_PATH") & "App_Data/products.xml", System.IO.FileMode.Open)
ds.ReadXml(streamRead)
streamRead.Close()
dt = ds.Tables("product")

My question, how can I load the XML into a dataset but only select products (or load products) from the category named "cat1"? I'd like to either load into the dataset all products in all categories and then be able to select products from a particular category, otherwise load just the products from one category that I choose.

Thanks in advance for helping.

 

[Menthos]

Take a look at the DataTable.Select method - it should do the trick.

There's likely a LINQ way of achieving what you're after too.

 

[ma701ss]

Hi,

Thanks for the help. I have now figured out using XPath how to load an XML file and select only child nodes ("products") from a category with an attribute ("id") matching whatever I want. I can then loop through the child nodes and assign to variables. What I cannot do is loop through the "products" nodes and assign the attribute "item" to a variable. Hopefully you can understand what I mean but here is my code:

Dim doc As XPathDocument
Dim nav As XPathNavigator
Dim iter As XPathNodeIterator
Dim count As Integer = 0

doc = New XPathDocument(AppSettings.Item("SECURED_ASSETS_PATH") & "App_Data/products3.xml")
nav = doc.CreateNavigator
iter = nav.Select("//CATEGORY[@id='163']/PRODUCT")

While iter.MoveNext

'This is the variable I am trying to assign the attribute "ITEM" in node "PRODUCT" to
ProductID = iter.Current.Name

Dim iterNews As XPathNodeIterator
Dim lstNav = iter.Current
iterNews = lstNav.SelectDescendants(XPathNodeType.Element, False)

While iterNews.MoveNext
If iterNews.Current.Name = "NAME" Then
ProductName = iterNews.Current.Value
End If
If iterNews.Current.Name = "THUMB" Then
ProductImage = "/thumbimages/" + iterNews.Current.Value
End If
If iterNews.Current.Name = "PRICE" Then
ProductCost = iterNews.Current.Value
End If
If iterNews.Current.Name = "DESCRIPTION" Then
ProductDescription = iterNews.Current.Value
End If
End While
End While

Sample XML:

<CATEGORY name="Games" id="163">
<PRODUCT ITEM="446">
<NAME>Product1</NAME>
<THUMB>A.jpg</THUMB>
<PRICE>19.60</PRICE>
<DESCRIPTION>It's a product</DESCRIPTION>
</PRODUCT>
<NAME>Product2</NAME>
<THUMB>B.jpg</THUMB>
<PRICE>20.00</PRICE>
<DESCRIPTION>It's another product</DESCRIPTION>
</PRODUCT>
<CATEGORY>

This has stumped me for the last few days and nothing I've looked up seems to help!

Thanks