How to pull out data from xml that is deeply nested...

Question

Simply put, I need to pull out the following data from the below XML:

deviceinfo1

• devicestatus: working
• flowrate: 25

deviceinfo2

• devicestatus: defective
• flow rate: 37

<testManagementReport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <referenceID>Bin02</referenceID>
  <reportType>subscription</reportType>
  <managementReport>
    <objectBasedReport>
      <objectID>deviceInfo1</objectID>
      <objectBasedReportElement>
        <nameValue>
          <objectAttribute>devicestatus</objectAttribute>
          <attributeValue>working</attributeValue>
        </nameValue>
        <nameValue>
          <objectAttribute>flowrate</objectAttribute>
          <attributeValue>25</attributeValue>
        </nameValue>
    </objectBasedReportElement>
    </objectBasedReport>
    <objectBasedReport>
      <objectID>deviceInfo2</objectID>
      <objectBasedReportElement>
        <nameValue>
          <objectAttribute>devicestatus</objectAttribute>
          <attributeValue>defective</attributeValue>
        </nameValue>
        <nameValue>
          <objectAttribute>flowrate</objectAttribute>
          <attributeValue>37</attributeValue>
        </nameValue>
    </objectBasedReportElement>
    </objectBasedReport>
</managementReport>
</testManagementReport>

Needs to be dynamic because the number of devices (objectBasedReport tags) can be anywhere from 1 - 200.  Any good suggestions or examples to query this?

Answer 1

Using XPath, you can achieve your objective.

All you need is to create XPath Query to locate the the target node..

XPathDocument document = new XPathDocument("Devices.xml");
XPathNavigator navigator = document.CreateNavigator();
XPathNodeIterator nodes = navigator.Select("/testManagementReport/managementReport/objectBasedReport");

For more details, refer http://msdn.microsoft.com/en-us/library/0ea193ac(v=vs.100).aspx

---

It all Happenz Sendil

Answer 2

With .NET 3.5 and later you can use LINQ to XML :

XDocument doc = XDocument.Load("input.xml");

var query = from report in doc.Descendants("objectBasedReport")

                select new {

                 id = (string)report.Element("objectID"),

                 ds = (string)report.Descendants("nameValue").First(n => (string)n.Element("objectAttribute") == "devicestatus").Element("attributeValue"),

                 fr = (int)report.Descendants("nameValue").First(n => (string)n.Element("objectAttribute") == "flowrate").Element("attributeValue")

              };

foreach (var report in query)

{

    Console.WriteLine("Report {0}", report.id);

   Console.WriteLine("Device status: {0}", report.ds);

   Console.WriteLine("Flow rate: {0}", report.fr);

}

---

MVP Data Platform Development My blog

Answer 3

This works great!  One last thing, turns out there will be one additional objectbasedreport at the top, which will display only once.  It just happens to be a bit different than the rest (has no devicestatus or flowrate).  So the last question is, how to modify the query to check if the new objectAttribute value (SaID) exists, and if so, capture it and display.  If not, ignore it and capture the others as normal .  Below is the modified xml with the new addition.  Any quick suggestions for if exists/not exists type of thing, etc?

<testManagementReport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <referenceID>Bin02</referenceID>
  <reportType>subscription</reportType>
  <managementReport>
<objectBasedReport>
      <objectID>SaID</objectID>
      <objectBasedReportElement>
        <nameValue>
          <objectAttribute>SaID</objectAttribute>
          <attributeValue>AR02</attributeValue>
        </nameValue>
      </objectBasedReportElement>
    </objectBasedReport>
    <objectBasedReport>
      <objectID>deviceInfo1</objectID>
      <objectBasedReportElement>
        <nameValue>
          <objectAttribute>devicestatus</objectAttribute>
          <attributeValue>working</attributeValue>
        </nameValue>
        <nameValue>
          <objectAttribute>flowrate</objectAttribute>
          <attributeValue>25</attributeValue>
        </nameValue>
    </objectBasedReportElement>
    </objectBasedReport>
    <objectBasedReport>
      <objectID>deviceInfo2</objectID>
      <objectBasedReportElement>
        <nameValue>
          <objectAttribute>devicestatus</objectAttribute>
          <attributeValue>defective</attributeValue>
        </nameValue>
        <nameValue>
          <objectAttribute>flowrate</objectAttribute>
          <attributeValue>37</attributeValue>
        </nameValue>
    </objectBasedReportElement>
    </objectBasedReport>
</managementReport>
</testManagementReport>

Answer 4

You could change

var query = from report in doc.Descendants("objectBasedReport")

                select new {

                 id = (string)report.Element("objectID"),

                 ds = (string)report.Descendants("nameValue").First(n => (string)n.Element("objectAttribute") == "devicestatus").Element("attributeValue"),

                 fr = (int)report.Descendants("nameValue").First(n => (string)n.Element("objectAttribute") == "flowrate").Element("attributeValue")

              };

to

var query = from report in doc.Descendants("objectBasedReport")
            where (string)report.Element("objectID") != "SaID"

                select new {

                 id = (string)report.Element("objectID"),

                 ds = (string)report.Descendants("nameValue").First(n => (string)n.Element("objectAttribute") == "devicestatus").Element("attributeValue"),

                 fr = (int)report.Descendants("nameValue").First(n => (string)n.Element("objectAttribute") == "flowrate").Element("attributeValue")

              };

That way the query processes only the "objectBasedReport" elements with a different id.

I would then write a second query to extract the data with that particular id e.g.

  var query2 = from report in doc.Descendants("objectBasedReport")

                    where (string)report.Element("ObjectID") == "SaID"

                    select new {

                       sid = (string)report.Descendants("nameValue").First(n => (string)n.Element("objectAttribute") == "SaID").Element("attributeValue")

                   }

---

MVP Data Platform Development My blog

Answer 5

Thanks. The first query works (displays the records and ignores the SaID.  The second query doesn't return any records by explicitly looking for the SaID value, unless I change it to not look for the others (!= "devicestatus1, etc).

But even then, after it returns the one SaID record and value, it blows up with "Sequence contains no matching element".  Should it be doing a select top 1 kind of thing in the query?

Answer 6

I think I used the wrong casing on one element name i.e. instead of "ObjectID" it should be "objectID":

  var query2 = from report in doc.Descendants("objectBasedReport")

                    where (string)report.Element("objectID") == "SaID"

                    select new {

                       sid = (string)report.Descendants("nameValue").First(n => (string)n.Element("objectAttribute") == "SaID").Element("attributeValue")

                   }

Does that help?

---

MVP Data Platform Development My blog

Answer 7

Yes, it was the casing.  Thanks!