Column with Empty Values not listed in Dataset from GetListItems RRS feed

  • Question

  • I am using GetListItems method of lists.asmx web service.

    I have a few columns which are not mandatory. When I use above method and convert the result to a dataset to access it row by row.

    I get column 'x' error is missing. When I look through Dataset Visualizer, it missing. If I enter a dummy value to column 'x' this column displayed in Dataset and code works fine.

    As I understand XML optimization removes the empty tag. Is there a way to overcome this.


    Monday, May 3, 2010 3:56 PM


  • You can use the following code to create the missing columns for the datatable. The code uses linq to check the view fields to see if they are missing. They will only be missing if none of the return items have a value for that column. The code then populates the datatable without having to iterate through all the records.

    public static DataTable GetListItemAsDatatable()
          string query = "<mylistitemrequest><Query><Where><Contains><FieldRef Name=\"Title\" /><Value Type=\"Text\">up</Value></Contains></Where></Query><ViewFields><FieldRef Name=\"ID\" /><FieldRef Name=\"Title\" /><FieldRef Name=\"Keywords\" Nullable=\"TRUE\"/></ViewFields><QueryOptions/></mylistitemrequest>";
          XmlDocument doc = new XmlDocument();
          listservice.Lists listProxy = new listservice.Lists();
          listProxy.Url = "http://basesmcdev2/sites/tester1/_vti_bin/lists.asmx";
          listProxy.UseDefaultCredentials = true;
          XmlNode queryNode = doc.SelectSingleNode("//Query");
          XmlNode viewNode = doc.SelectSingleNode("//ViewFields");
          XmlNode optionNode = doc.SelectSingleNode("//QueryOptions");
          XmlNode retNode = listProxy.GetListItems("tester2", string.Empty, queryNode, viewNode, string.Empty, optionNode, "e5bc34ff-6cde-4fee-aa4c-356baa57b37b");
          DataSet ds = new DataSet();
          StringReader sr = new StringReader(retNode.OuterXml);
          DataTable dt = ds.Tables["Row"];
          foreach (DataColumn dc in dt.Columns)
            dc.ColumnName = dc.ColumnName.Replace("ows_", string.Empty);
          using (StringReader sqr = new StringReader(query))
            using (XmlTextReader xtr = new XmlTextReader(sqr))
              XElement queryX = XElement.Load(xtr);
              var viewFields = from v in queryX.Elements("ViewFields").Elements("FieldRef").Attributes("Name").Distinct() select new { colname = v.Value };
              var colFields = dt.Columns.OfType<DataColumn>().Select(d => new {colname = d.ColumnName});
              var missingFields = viewFields.Except(colFields);
              foreach (var missingField in missingFields)
                dt.Columns.Add(missingField.colname, typeof(string));
          return dt;

    • Marked as answer by Chengyi Wu Thursday, May 13, 2010 1:51 AM
    Tuesday, May 4, 2010 3:47 AM