Question Parsing xml nodes with attributes

[EmbersFire]

I need some help figuring out how to parse an XML file in VB.net so I can dump the contents into a database.

Basically all the data in the XML node below needs to be read into an object.

<Products>
 <Product ID="523233" UserTypeID="Property" ParentID="523232">
	<Name>My Property Name</Name>                     
	<AssetCrossReference AssetID="173501" Type=" Non Print old">
	  </AssetCrossReference>
	  <AssetCrossReference AssetID="554740" Type=" Non Print old">
	  </AssetCrossReference>
	  <AssetCrossReference AssetID="566495" Type=" Non Print old">
	  </AssetCrossReference>
	  <AssetCrossReference AssetID="553014" Type="Non Print">
	  </AssetCrossReference>
	  <AssetCrossReference AssetID="553015" Type="Non Print">
	  </AssetCrossReference>
	  <AssetCrossReference AssetID="553016" Type="Non Print">
	  </AssetCrossReference>
	  <AssetCrossReference AssetID="553017" Type="Non Print">
	  </AssetCrossReference>
	  <AssetCrossReference AssetID="553018" Type="Non Print">
	  </AssetCrossReference>

	<Values>
	  <Value AttributeID="5115">Section of main pool</Value>
	  <Value AttributeID="5137">114 apartments, four floors, no lifts</Value>
	  <Value AttributeID="5170">Property location</Value>
	  <Value AttributeID="5164">2 key</Value>
	  <Value AttributeID="5134">A comfortable property, the apartment is set on a pine-covered hillside - a scenic and peaceful location.</Value>
	  <Value AttributeID="5200">PROPERTY_ID</Value>
	  <Value AttributeID="5148">facilities include X,Y,Z</Value>
	  <Value AttributeID="5067">Self Catering. </Value>
	  <Value AttributeID="5221">Frequent organised daytime activities</Value>
	</Values>
  </Product>
<Products>

I've had some success playing around with some Linq code, but I'm not able to figure out a way to traverse the sub nodes, so I can read them into an object.

   Dim productsXML As XElement = XElement.Load("C:\properties.XML)
        Dim ParentNode As XElement

        Dim Query = From p In productsXML...<Value> _
                    Where p.Value = "PROPERTY_ID"
        ParentNode = Query.FirstOrDefault()

 

[MattP]

This seems to do the trick.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim xdoc As XDocument = XDocument.Parse(IO.File.ReadAllText("C:\Temp\Products.xml"))
        Dim prods = From prod In xdoc...<Product> _
                     Select New With { _
                         .ID = prod.Attribute("ID").Value, _
                         .UserTypeID = prod.Attribute("UserTypeID").Value, _
                         .ParentID = prod.Attribute("ParentID").Value, _
                         .Name = prod.<Name>.Value, _
                         .AssetCrossReference = (From acr In prod.<AssetCrossReference> _
                                                 Select New With { _
                                                    .AssetID = acr.Attribute("AssetID").Value, _
                                                    .AssetType = acr.Attribute("Type").Value _
                                                }), _
                         .Values = (From vals In prod.<Values>.<Value> _
                                    Select New With { _
                                        .AttributeID = vals.Attribute("AttributeID").Value, _
                                        .Description = vals.Value _
                                    }) _
                        }
    End Sub

 

[EmbersFire]

Thanks for your response. That looks very close to what I need. Is there a way to filter it down to one individual node e.g. extract a property based on a particular PROPERTY_ID?

<Value AttributeID="5200">PROPERTY_ID</Value>

 

[MattP]

I'm a little confused as to what you're asking for. You just need the whole Product node when one of the Value nodes has a value of PROPERTY_ID?

 

[EmbersFire]

I just need to retrieve a single Product node, with all it's sub nodes, that has a particular PROPERTY_ID

 

[MattP]

There's a couple of ways that you can do this. I would recommend the 2nd.

        Dim propertyID = "PROPERTY_ID"
        Dim searchedProd1 = (From prod In xdoc...<Value> _
                             Where prod.Value = propertyID).FirstOrDefault.Parent.Parent

        Dim searchedProd2 = xdoc...<Product> _
                           .Where(Function(p) p...<Value> _
                                      .Any(Function(prop) CStr(prop) = propertyID)).FirstOrDefault

Edit: I would recommend checking out LINQ To XML Samples - Query as there is some really good information there.

 

[EmbersFire]

Thanks for the link. I clearly need to do some more reading on this. You wouldn't happen to know how I would iterate through the child nodes to get the various property details?

 

[MattP]

Thanks for the link. I clearly need to do some more reading on this. You wouldn't happen to know how I would iterate through the child nodes to get the various property details?

Here I'm combining the first method I showed you on how to create an anonymous type with all of the relevant stuff from the Product node and combined it with the 2nd part where only the node with the PROPERTY_ID is included.

        Dim propertyID = "PROPERTY_ID"
        Dim theProd = (From prod In xdoc...<Product> _
                      .Where(Function(p) p...<Value> _
                                 .Any(Function(prop) CStr(prop) = propertyID)) _
                      Select New With { _
                          .ID = prod.Attribute("ID").Value, _
                          .UserTypeID = prod.Attribute("UserTypeID").Value, _
                          .ParentID = prod.Attribute("ParentID").Value, _
                          .Name = prod.<Name>.Value, _
                          .AssetCrossReferences = (From acr In prod.<AssetCrossReference> _
                                                  Select New With { _
                                                      .AssetID = acr.Attribute("AssetID").Value, _
                                                      .AssetType = acr.Attribute("Type").Value _
                                                  }), _
                          .Values = (From vals In prod.<Values>.<Value> _
                                     Select New With { _
                                         .AttributeID = vals.Attribute("AttributeID").Value, _
                                         .Description = vals.Value _
                                     }) _
                         }).FirstOrDefault

Just simply writing the details to a file so you can see how easy it is to loop through the AssetCrossReferences and Values collections created in the 1st step.

        Using sw As New IO.StreamWriter("C:\temp\parsedItem.txt")
            sw.WriteLine("ID: " & theProd.ID)
            sw.WriteLine("User Type ID: " & theProd.UserTypeID)
            sw.WriteLine("Parent ID: " & theProd.ParentID)
            sw.WriteLine("Name: " & theProd.Name)
            For Each acr In theProd.AssetCrossReferences
                sw.WriteLine(ControlChars.Tab & "Asset ID: " & acr.AssetID)
                sw.WriteLine(ControlChars.Tab & "Asset Type: " & acr.AssetType)
            Next
            For Each v In theProd.Values
                sw.WriteLine(ControlChars.Tab & "Attribute ID: " & v.AttributeID)
                sw.WriteLine(ControlChars.Tab & "Description: " & v.Description)
            Next
        End Using

 

[EmbersFire]

That's awesome. Thanks very much for your help. I just changed "FirstOrDefault" to "LastOrDefault" and now it gives me exactly what I want.

 

[MattP]

You're welcome.

Working with XML is much cleaner when working with LINQ and anonymous types (though you could make a typed class to store the information).

Make sure you check out the examples I linked to earlier in the thread.

I'd suggest editing your 1st post to make the root node <Products> so others viewing the thread aren't dealing with invalid XML.

 

[EmbersFire]

Will do. That will be useful study material as I'll probably be doing a lot of xml parsing in the near future.