none
query data from xml using dataset RRS feed

  • Question

  • I am trying to retrieve some data from an xml file that I loaded using xmlreader and datasets.  For example, I read the data into the dataSet by:

    XmlReader xmlReader = new XmlTextReader(rawReader);

    dataSet = new DataSet("Movie");
    dataSet.ReadXml(xmlReader);

    I then can get the top nodes of the table by going through each row.  The example below shows how i can retrieved the id information of that row from the dataSet.

     foreach (DataRow movieData in tbl_Movie.Rows)
      {
    movieData["id"];
     }


    The problem comes in when i try to retrieve the data of elements that are lower in the tree, such as "releasedate".  "releasedate" is on the same level as the "id" however it has children values that I am trying to retrieve, such as "date".  How can I use the dataSet to reteive this level of information?


    - <movielist>
    - <movie>
          <id>4786</id>
          <index>162</index>
          <format>
              <displayname>DVD</displayname>
              <sortname>DVD</sortname>
      </format>
    -    <country>
              <displayname>USA</displayname>
              <sortname>USA</sortname>
      </country>
          <collectionstatus listid="3">In Collection</collectionstatus>
    -     <language>
                <displayname>English</displayname>
                 <sortname>English</sortname>
      </language>
         <store />
         <series />
         <boxset />
        <purchasedate />
        <owner />
        <title>21 Grams</title>
        <plot>Sean Penn and Benecio Del Toro, two of the most gripping actors around, play wildly different men linked through a grieving woman (Naomi Watts, <I>Mulholland Drive</I>, <I>The Ring</I>) in <I>21 Grams</I>. Del Toro (<I>Traffic</I>, <I>The Usual Suspects</I>) delves deep into the role of an ex-con turned born-again Christian, a deeply conflicted man struggling to set right a terrible accident, even at the expense of his family. Penn (<I>Mystic River</I>, <I>Dead Man Walking</I>) captures a cynical, philandering professor in dire need of a heart transplant, which he gets from the death of Watts' husband. <I>21 Grams</I> slips back in forth in time, creating an intricate emotional web out of the past and the present that slowly draws these three together; the result is remarkably fluid and compelling. The movie overreaches for metaphors towards the end, but that doesn't erase the power of the deeply felt performances. <I>--Bret Fetzer</I></plot>
         <myrating>0</myrating>
         <seenit boolvalue="0">No</seenit>
         <viewingdate />
         <edition />
         <condition />
    -    <releasedate>
                  <displayname>2003</displayname>
         </year>
            <month>12</month>
            <day>26</day>
            <date>12/26/2003</date>
      </releasedate>
    Wednesday, March 5, 2008 2:49 PM

Answers

  •  

    When the DataSet encounters a nested XML element, it creates a new DataTable for that element, and sets up a DataRelation between the parent DataTable and the newly created child DataTable. So the Movie Element will be a DataTable, and each child Element will become a column unless that Element contains children. For example, in your XML file, the id and index will become columns.

     

    <movie>

      <id>4786</id>

      <index>162</index>

      ...

    </movie>

     

    However, the format Element will become a child DataTable, with a column for each child Element, in this case displayname and sortname.

     

    <format>

      <displayname>DVD</displayname>

      <sortname>DVD</sortname>

    </format>

     

    You can see these child DataTables and DataRelations after the DataTable has been loaded.

     

    Thanks,

    Erick

     

    Sunday, March 9, 2008 9:28 PM
    Moderator

All replies

  • As I can see, the XML file is not well formated, so maybe that is the problem
    (off course if this is whole XML file that you posted here)

    greet
    Thursday, March 6, 2008 3:06 PM
  • I have to wonder if a Dataset is actually the best option in your case. Is it possible to use LINQ to XML in your application to accomplish this query? If so, you could do something like the following:

     

    var source = XElement.Load(MyFile)

    var Query = from movie in source.Descendents("movie")

                     where (date)source.Element("releasedate").Element("date").Value > targetDate

                     select new {Id = (int32)movie.Element("id").Value,

                                      title = (string)movie.Element("title").Value}

     

    If you can't use 3.5 and LINQ, an XPath query might be a better option than a dataset.

     

    Jim Wooley

    www.ThinqLinq.com

    www.LinqInAction.net

    Friday, March 7, 2008 3:13 AM
  •  

    When the DataSet encounters a nested XML element, it creates a new DataTable for that element, and sets up a DataRelation between the parent DataTable and the newly created child DataTable. So the Movie Element will be a DataTable, and each child Element will become a column unless that Element contains children. For example, in your XML file, the id and index will become columns.

     

    <movie>

      <id>4786</id>

      <index>162</index>

      ...

    </movie>

     

    However, the format Element will become a child DataTable, with a column for each child Element, in this case displayname and sortname.

     

    <format>

      <displayname>DVD</displayname>

      <sortname>DVD</sortname>

    </format>

     

    You can see these child DataTables and DataRelations after the DataTable has been loaded.

     

    Thanks,

    Erick

     

    Sunday, March 9, 2008 9:28 PM
    Moderator