none
XML to Dataset - Get the child rows RRS feed

  • Question

  • I'm trying to read an XML file into a Dataset object.  Like this.  dataset.readxml(books.xml);

    I can't figure out how to iterate the datatable to the get all the child rows?  What is the inferred relationship when you call Readxml?  I see that I have three tables.  But what is the relation inferred?  Help!

    table1 - books
    table2 - comments
    table3 - userComment


    <books>
        <book ISBN="10-000000-001" title="The Iliad and The Odyssey" price="12.95">
            <comments>
                <userComment rating="4" comment="Best translation I've read." />
                <userComment rating="2" comment="I like other versions better." />
            </comments>
        </book>
    </books>
    Saturday, July 7, 2007 1:56 AM

All replies

  • You can get at the relationships using the ChildRelations property of one of the DataTables..

    http://msdn2.microsoft.com/en-us/library/zb0sdh0b(vs.80).aspx

     

    Code Snippet

    int childRelations = dsBooks.Tables[0].ChildRelations.Count;

     

    ChildRelations:

    http://msdn2.microsoft.com/en-us/library/system.data.datatable.childrelations.aspx

     

    Using the DataRelation to get child rows:

    http://msdn2.microsoft.com/en-us/library/0k21zcyx(VS.71).aspx

    Saturday, July 7, 2007 4:12 AM
  • Hi Evan,

    Thanks for the reply.  I still am not clear on this.  The count is 1.

    int childRelations = ds.books.Tables[0].ChildRelations.Count;

    How do I use the .GetChildRows(relation) method?  I really need a sample.   Do I need an array of rows?  What is the best practice?





    Saturday, July 7, 2007 2:01 PM
  • You have to do it on a row by row basis using the DataRow's "GetChildRows" method...

    http://msdn2.microsoft.com/en-us/library/hbt8xha8.aspx

     

    Code Snippet

    DataRelation relation = ds.books.Tables[0].ChildRelations.Item(0);

    foreach (DataRow parentRow in ds.books.Tables[0].Rows)

    {

       DataRow[] childRows = parentRow.GetChildRows(relation);

    }

     

     

    Saturday, July 7, 2007 5:29 PM
  • Help.  I'm still not getting this.
    Saturday, July 7, 2007 11:39 PM
  • What specifically are you not getting?  If you give me a more specific question, I can give you a more specific answer.
    Saturday, July 7, 2007 11:53 PM
  • This is what I need to do.  Read this xml file into a dataset object.  I see there are three tables in this xml file when you use the .ReadXML method.

    book
    comments
    userComment

    I need an example on how to iterate the book table to get all the  userComment rows.  I need sample code to make this clear to me.  I have been working on this for days.  I am confused by the comments table, which is the 2nd table.  How are the relations set up in the dataset to xml?


    ds.ReadXml(Server.MapPath(@"App_Data/books.xml")); 


    XML --------------------------

    <?xml version="1.0" encoding="utf-8"?>
    <books>
        <book ISBN="10-000000-001" title="The Iliad and The Odyssey" price="12.95">
            <comments>
                <userComment rating="4" comment="Best translation I've read." />
                <userComment rating="2" comment="I like other versions better." />
            </comments>
        </book>
        <book ISBN="10-000000-999" title="Anthology of World Literature" price="24.95">
            <comments>
                <userComment rating="3" comment="Needs more modern literature." />
                <userComment rating="4" comment="Excellent overview of world literature." />
            </comments>
        </book>
        <book ISBN="11-000000-002" title="Computer Dictionary" price="24.95">
            <comments>
                <userComment rating="3" comment="Dictionary is a valuable resource" />
            </comments>
        </book>
        <book ISBN="11-000000-003" title="Cooking on a Budget" price="23.95">
            <comments>
                <userComment rating="4" comment="Delicious!" />
            </comments>
        </book>
        <book ISBN="11-000000-004" title="Great Works of Art" price="29.95">
            <comments>
                <userComment rating="4" comment="Comment 1" />
                <userComment rating="10" comment="Comment 2." />
                <userComment rating="5" comment="Comment 3." />
                <userComment rating="3" comment="Comment 4." />
            </comments>
        </book>
        <book ISBN="13-000000-001" title="Underworld" price="24.99">
            <comments>
                <userComment rating="10" comment="Loved Underworld DeLillo is a Genious">
                </userComment>
            </comments>
        </book>
    </books>
    Sunday, July 8, 2007 2:06 PM
  • Well, we can see looking at the Xml that for every book there is a corresponding "comments" entry, and for each "comments" there is a group of "userComment" items.. so you have 3 levels (which gives you 3 datatables)..

     

    so, using the code above, go from the books table and for each book record in the books table, get its child rows using its DataRelation (see code above).  That will give you the "comments".  Next, use the DataRelation from the "comments" datatable to get its child rows.  Those child rows will be the userComment items for the book (which we started with at the top level).

     

    book -> comments -> userComments

    Sunday, July 8, 2007 8:58 PM
  • Can someone else help on this? 

     

    I don't understand the relationship between these tables.  I can see the columns in book, ISBN, title, price.  The colums in userComment are rating and comment.

    What about the comments table?  Are there foreign keys?  Again, can anyone provide sample code so I can understand what is going on when you read an XML file into a dataset object?

     

    Thanks in advance.

    Sunday, July 8, 2007 10:12 PM