Parse non-standard SQL server XML column with c#



I'm trying to parse a xml column in Sql server the code kinda works but when the code iterates through the xmlnodelist it will write the first node multiple times depending on how many nodes there are which would be 2 based on this xml example. When I put a watch on the XmlNodeList there is the right amount of results in the array and all have the correct results.


Is this the correct way to do this or is there another way? and why is this just getting the first node and rewriting it multiple times?


I have this xml column stored in SQL Server and it will insert 2 of the same first node, sorry for all the code and the xml mess. This is the way the program stores it.



<Compatibilities>
<Compatibility>
<NameValue>
<Name>Make</Name>
<Value>Pontiac</Value>
</NameValue>
<NameValue>
<Name>Model</Name>
<Value>Grand Am</Value>
</NameValue>
<NameValue>
<Name>Year</Name>
<Value>1999</Value>
</NameValue>
<NameValue>
<Name>Trim</Name>
<Value>All</Value>
</NameValue>
<NameValue>
<Name>Engine</Name>
<Value>All</Value>
</NameValue>
<Notes>Rear Left</Notes>
</Compatibility>
<Compatibility>
<NameValue>
<Name>Make</Name>
<Value>Pontiac</Value>
</NameValue>
<NameValue>
<Name>Model</Name>
<Value>Grand Am</Value>
</NameValue>
<NameValue>
<Name>Year</Name>
<Value>2005</Value>
</NameValue>
<NameValue>
<Name>Trim</Name>
<Value>All</Value>
</NameValue>
<NameValue>
<Name>Engine</Name>
<Value>All</Value>
</NameValue>
<Notes>Rear Left</Notes>
</Compatibility>
</Compatibilities>


and my c# code..while loop on a SqlDataReader



while (itemDataReader.Read())
{
var xmldoc = new XmlDocument();
string sku = itemDataReader[0].ToString();

xmldoc.LoadXml(itemDataReader[1].ToString());
XmlNodeList xnList = xmldoc.SelectNodes("/Compatibilities/Compatibility");

foreach (XmlNode x in xnList)
{

itemList.Add(new Item
{
itemSku = sku,
year = x.SelectSingleNode("//NameValue[Name='Year']/Value").InnerText,
make = x.SelectSingleNode("//NameValue[Name='Make']/Value").InnerText,
model = x.SelectSingleNode("//NameValue[Name='Model']/Value").InnerText,
trim = x.SelectSingleNode("//NameValue[Name='Trim']/Value").InnerText,
engine = x.SelectSingleNode("//NameValue[Name='Engine']/Value").InnerText,
notes = x.SelectSingleNode("//Notes").InnerText,
});
}


}


No comments:

Post a Comment