tags:

views:

579

answers:

1
Q: 

Displaying results of a LINQ TO XML query in Gridview

I am querying an xml document in LINQ and would want to display the results in gridview to allow users to select from the list.

the query is as a result of user input.

Here's a copy of my xml.

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Table">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="ROOM_ID" msprop:OraDbType="111" type="xs:short" minOccurs="0" />
                <xs:element name="SMOKING" msprop:OraDbType="126" type="xs:string" minOccurs="0" />
                <xs:element name="NO_OF_PEOPLE" msprop:OraDbType="111" type="xs:short" minOccurs="0" />
                <xs:element name="ROOM_COST" msprop:OraDbType="108" type="xs:double" minOccurs="0" />
                <xs:element name="ROOM_TYPE" msprop:OraDbType="126" type="xs:string" minOccurs="0" />
                <xs:element name="ROOM_DESC" msprop:OraDbType="126" type="xs:string" minOccurs="0" />
                <xs:element name="BOOKING_ID" msprop:OraDbType="111" type="xs:short" minOccurs="0" />
                <xs:element name="DATE_FROM" msprop:OraDbType="106" type="xs:dateTime" minOccurs="0" />
                <xs:element name="DATE_TO" msprop:OraDbType="106" type="xs:dateTime" minOccurs="0" />
                <xs:element name="HOTEL_AREA" msprop:OraDbType="126" type="xs:string" minOccurs="0" />
                <xs:element name="HOTEL_NAME" msprop:OraDbType="126" type="xs:string" minOccurs="0" />
                <xs:element name="RESTAURANT" msprop:OraDbType="126" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <Table>
    <ROOM_ID>101</ROOM_ID>
    <SMOKING>N</SMOKING>
    <NO_OF_PEOPLE>1</NO_OF_PEOPLE>
    <ROOM_COST>29.990000000000002</ROOM_COST>
    <ROOM_TYPE>SINGLE ROOM</ROOM_TYPE>
    <ROOM_DESC>ONE BED, MIRROR, DESK, COUCH</ROOM_DESC>
    <BOOKING_ID>300</BOOKING_ID>
    <DATE_FROM>2008-01-02T00:00:00+00:00</DATE_FROM>
    <DATE_TO>2008-01-04T00:00:00+00:00</DATE_TO>
    <HOTEL_AREA>WOLVERHAMPTON</HOTEL_AREA>
    <HOTEL_NAME>LEVELS</HOTEL_NAME>
    <RESTAURANT>N</RESTAURANT>
  </Table>
  <Table>
    <ROOM_ID>105</ROOM_ID>
    <SMOKING>Y</SMOKING>
    <NO_OF_PEOPLE>4</NO_OF_PEOPLE>
    <ROOM_COST>59.99</ROOM_COST>
    <ROOM_TYPE>EN-SUITE</ROOM_TYPE>
    <ROOM_DESC>KINGSIZE BED, MIRROR, GARDEN</ROOM_DESC>
    <BOOKING_ID>301</BOOKING_ID>
    <DATE_FROM>2009-01-02T00:00:00+00:00</DATE_FROM>
    <DATE_TO>2009-01-15T00:00:00+00:00</DATE_TO>
    <HOTEL_AREA>STAFFORD</HOTEL_AREA>
    <HOTEL_NAME>HILTON</HOTEL_NAME>
    <RESTAURANT>Y</RESTAURANT>
  </Table>
  <Table>
    <ROOM_ID>111</ROOM_ID>
    <SMOKING>N</SMOKING>
    <NO_OF_PEOPLE>10</NO_OF_PEOPLE>
    <ROOM_COST>49.99</ROOM_COST>
    <ROOM_TYPE>HOSTEL ROOM</ROOM_TYPE>
    <ROOM_DESC>DOUBLE-DECKER BED, DRAWER</ROOM_DESC>
    <BOOKING_ID>302</BOOKING_ID>
    <DATE_FROM>2009-03-02T00:00:00+00:00</DATE_FROM>
    <DATE_TO>2009-06-15T00:00:00+01:00</DATE_TO>
    <HOTEL_AREA>WOLVERHAMPTON</HOTEL_AREA>
    <HOTEL_NAME>LEVELS</HOTEL_NAME>
    <RESTAURANT>N</RESTAURANT>
  </Table>
  <Table>
    <ROOM_ID>113</ROOM_ID>
    <SMOKING>N</SMOKING>
    <NO_OF_PEOPLE>1</NO_OF_PEOPLE>
    <ROOM_COST>29.990000000000002</ROOM_COST>
    <ROOM_TYPE>SINGLE ROOM</ROOM_TYPE>
    <ROOM_DESC>ONE BED, MIRROR, DESK, COUCH</ROOM_DESC>
    <BOOKING_ID>303</BOOKING_ID>
    <DATE_FROM>2009-09-02T00:00:00+01:00</DATE_FROM>
    <DATE_TO>2009-09-05T00:00:00+01:00</DATE_TO>
    <HOTEL_AREA>BASWICH</HOTEL_AREA>
    <HOTEL_NAME>DAYSTAR</HOTEL_NAME>
    <RESTAURANT>Y</RESTAURANT>
  </Table>
  <Table>
    <ROOM_ID>103</ROOM_ID>
    <SMOKING>Y</SMOKING>
    <NO_OF_PEOPLE>2</NO_OF_PEOPLE>
    <ROOM_COST>39.99</ROOM_COST>
    <ROOM_TYPE>DOUBLE ROOM</ROOM_TYPE>
    <ROOM_DESC>DOUBLE BED, MIRROR, SEETEE</ROOM_DESC>
    <BOOKING_ID>304</BOOKING_ID>
    <DATE_FROM>2009-08-21T00:00:00+01:00</DATE_FROM>
    <DATE_TO>2009-08-25T00:00:00+01:00</DATE_TO>
    <HOTEL_AREA>CANNOCK</HOTEL_AREA>
    <HOTEL_NAME>MIDAS</HOTEL_NAME>
    <RESTAURANT>N</RESTAURANT>
  </Table>
  <Table>
    <ROOM_ID>107</ROOM_ID>
    <SMOKING>Y</SMOKING>
    <NO_OF_PEOPLE>10</NO_OF_PEOPLE>
    <ROOM_COST>49.99</ROOM_COST>
    <ROOM_TYPE>HOSTEL ROOM</ROOM_TYPE>
    <ROOM_DESC>DOUBLE-DECKER BED, DRAWER</ROOM_DESC>
    <BOOKING_ID>305</BOOKING_ID>
    <DATE_FROM>2009-08-25T00:00:00+01:00</DATE_FROM>
    <DATE_TO>2009-08-31T00:00:00+01:00</DATE_TO>
    <HOTEL_AREA>STOKE</HOTEL_AREA>
    <HOTEL_NAME>MIVI</HOTEL_NAME>
    <RESTAURANT>Y</RESTAURANT>
  </Table>
  <Table>
    <ROOM_ID>141</ROOM_ID>
    <SMOKING>N</SMOKING>
    <NO_OF_PEOPLE>2</NO_OF_PEOPLE>
    <ROOM_COST>39.99</ROOM_COST>
    <ROOM_TYPE>DOUBLE ROOM</ROOM_TYPE>
    <ROOM_DESC>DOUBLE BED, MIRROR, SEETEE</ROOM_DESC>
    <HOTEL_AREA>BASWICH</HOTEL_AREA>
    <HOTEL_NAME>DAYSTAR</HOTEL_NAME>
    <RESTAURANT>Y</RESTAURANT>
  </Table>
  <Table>
    <ROOM_ID>109</ROOM_ID>
    <SMOKING>Y</SMOKING>
    <NO_OF_PEOPLE>1</NO_OF_PEOPLE>
    <ROOM_COST>19.990000000000002</ROOM_COST>
    <ROOM_TYPE>BED-SIT</ROOM_TYPE>
    <ROOM_DESC>ONE BED, DESK</ROOM_DESC>
    <HOTEL_AREA>CANNOCK</HOTEL_AREA>
    <HOTEL_NAME>MIDAS</HOTEL_NAME>
    <RESTAURANT>N</RESTAURANT>
  </Table>
</NewDataSet>
+1  A: 

You should be able to do this. Here's a few samples:

http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/thread/149dd49d-27c3-4a92-9715-95c2cd624222

http://vstoolsforum.com/blogs/vbnet/archive/2008/08/26/linq-to-xml-with-asp-gridview-in-vb.aspx

It looks like your xml is from a dataset that was persisted to XML. If this is the case you could also reload the dataset using the info from this link:

http://msdn.microsoft.com/en-us/library/fx29c3yd.aspx

From there you can filter the dataset results with a dataview.

You can also linq over the dataset:

http://www.hookedonlinq.com/LINQtoDatasets.ashx

klabranche  2009-08-22 16:52:45
Thanks so much klabranche, I can view the results in grid view now, but cant seem to to change the <DataSet> root element and yes its generated directly from an oracle db.And pls any suggestions as to how to convert the retrieved figures to 2 decimal places?Thanks in advance
 2009-08-22 17:11:55
What do you mean you can't change the root element? What are you trying to do?
klabranche  2009-08-22 17:40:46
As for the formatting. Since you are binding to the grid and letting the grid generate the columns you will need to do the formatting in code. here's a sample on SO that shows how to do this. The code formats to a date but you can format it how you wish. Just look into the formatting strings.http://stackoverflow.com/questions/1176865/unable-to-format-date-in-dataset-column-gridview
klabranche  2009-08-22 17:42:27
If you set your gridview to NOT auto generate the columns then you would set your columns up in the grid yourself, naming each column the same as the dataset's column name coming in from code and then formatting the column in the designer.
klabranche  2009-08-22 17:43:37
Thanks so much klabranche but I forgot to tell you I just started learning Visual Studio a couple of weeks ago and everything is way over my head. Can you please give me a copy of the code I'd use to format both a date column and a price column?I am also not seem to be getting the gridview to show the checkbox.I'm stuck pls help. Thanks in advance. I am using visual studio 2008 and would want to pull out ROOM_COST, DATE_TO, ROOM_TYPE, LOCATION, SMOKING, RESTAURANT AND NO_OF_PEOPLE COLUMNS from the xml file. A user should now be able to select which ever meets their requirements. Thanks
 2009-08-24 06:25:13
Here's a link that shows you a few different ways to do this in code and the kinds of formats:http://geekswithblogs.net/michelotti/archive/2006/03/30/73896.aspxHere's the MSDN article for the DataFormatString property and the formatting strings.http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx
klabranche  2009-08-24 14:32:47

related questions

Merge XML files in a XDocument
LINQ to XML optional element query
convert XmlNode to XNode?
Validating and Extracting XML record by record into Database
Save attribute value of xml element with single quotes using linq to xml.
Linq to XML 'Where not in' syntax problem
How can I combine this code into one or two LINQ queries?
How do I do a deep copy of an element in LINQ to XML?
How do I access the XML data in my column using LINQ to XML?
How can I extract specific tags using linq to xml in F# ?
How do you handle arbitrary namespaces when querying over Linq to XML?
LINQ to XML gets no data with schema set
Is there a way to retrieve elements using only local names in a Linq-to-XML query?
How to get the position() of an XElement?
How to get Single XElement object using Linq to Xml ?
LINQ to XML for a small appliction can it replace a small database?
Issue with XDocument and the BOM (Byte Order Mark)
I need a LINQ expression to find an XElement where the element name and attributes match an input node.
How can I get an XElement's innertext in Linq to XML?
Linq to XML for KML?
LINQ to XML Newbie Question: Returning More Than One Result
LINQ to XML Newbie Question
Do namespaces propagate to children in XElement objects?
How can I extract a part of a xaml object graph via linq to xml?
IntelliSense for XElement objects with XML schema