none
Columns missing when using Sharepoint Webservices RRS feed

  • Question

  • Hi All,

    I am using webservices to fetch data from Sharepoint List.
    I have observed that if a List-Column is not having any value in all the rows , then that column is not returned at all by the webservice.

    The code is below

     

    DataSet dsDeliverableInfo = new DataSet();
    XmlDocument xmlDoc = new System.Xml.XmlDocument();
    XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
    XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");
    XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");
    ndQueryOptions.InnerXml =
    "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" + "<DateInUtc>TRUE</DateInUtc>";

    ndViewFields.InnerXml =
    "<FieldRef Name=\"Deliverable_x0020_ID\" /><FieldRef Name=\"Source\" /><FieldRef Name=\"Name\" /><FieldRef Name=\"Description\" /><FieldRef Name=\"Role_x0020__x0028_Accountable_x0\" />";
    ndViewFields.InnerXml = ndViewFields.InnerXml +
    "<FieldRef Name=\"Role_x0020__x0028_contributing_x\" /><FieldRef Name=\"Predessor_x0020_Deliverables\" />";
    ndViewFields.InnerXml = ndViewFields.InnerXml +
    "<FieldRef Name=\"One_x0020_off_x0020__x002f__x002\" /><FieldRef Name=\"Accountability\" /><FieldRef Name=\"Life_x0020_Cycle\" /><FieldRef Name=\"Exit_x0020_Criteria\" />";

    ndQuery.InnerXml =

    "<Where><Eq><FieldRef Name=\"Deliverable_x0020_ID\" /><Value Type=\"Lookup\">" + deliverableId + "</Value></Eq></Where>";

     

    XmlNode xmlDeliverables = smartList.GetListItems("Deliverable", null, ndQuery, ndViewFields, "100000", ndQueryOptions);

     

    XmlTextReader readerDeliverables = new XmlTextReader(xmlDeliverables.OuterXml, XmlNodeType.Document, null);

    dsDeliverableInfo.ReadXml(readerDeliverables);

    When i get the result set it is not having "Life Cycle" and "Exit Criteria" columns as it is not having any value in the sharepoint list.
    How can I ensure that the columns are returned in all the cases.

    Regards
    Vikas

    Wednesday, June 10, 2009 12:52 PM

Answers

All replies

  • Hi,

    It is by design and helps that the size of the resulting XML being smaller.

    You should check the existence of the attribute related to a field first, before trying to read its value:

    XmlNamespaceManager nsmgr = new XmlNamespaceManager(items.OwnerDocument.NameTable);
    nsmgr.AddNamespace("z", "#RowsetSchema");
    nsmgr.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");

    XmlNodeList itemNodeList = items.SelectNodes("rs:data/z:row", nsmgr);

    foreach (XmlNode itemNode in itemNodeList)
    {
        XmlAttribute attr = itemNode.Attributes["ows_Memo"];
        if (attr != null)
        {
            Console.WriteLine(attr.Value);
        }
    }

    Why do you need to have all the columns in the XML?

    If you plan to use an XSL transformation to display results, then you should handle this case there too.

    Peter

    • Edited by Peter Holpar Wednesday, June 10, 2009 2:22 PM Formatting reply
    Wednesday, June 10, 2009 2:22 PM
  • OK, I see you would like to load the response into a DataSet. Well, that is rather problematic. One workaround to iterate through the results (rows and fields) and add the missing fields from code, but it do not perform well, and I think is a dirty solution. :-(

    Peter
    Wednesday, June 10, 2009 2:35 PM
  • Hi,

    I understand that by default it wont give you those columns to reduce the xml size , but is there any workaround to still get those columns.

    I am using XML to get a dataset and bind controls using dataset. If I dont get the columns everytime then it will throw me an exception.

    Regards
    Vikas

    Wednesday, June 10, 2009 3:40 PM
  • Hi,

    I've already read about this issue in other forums but no solution was suggested for it. If you now the fields you expect (as you set them in the ViewFields) than my suggested workaround (adding missing atributes to row nodes) can be applied, but it is really a dirty solution. I will post a code snippet for that soon.

    Peter

    P.S. You double-posted your last message, so one of them can be deleted.
    Wednesday, June 10, 2009 4:54 PM
  • Hi Peter,

    Please post the code , I will compare it with the other solution you have told earlier and will use the best one.

    Thanks
    Vikas

    Wednesday, June 10, 2009 10:01 PM
  • Hi Vikas,

    I wrote a blog post about that issue. It contains the code for adding the missing attributes for the columns.

    You can read it here:
    http://www.sharepointblogs.com/pholpar/archive/2009/06/11/columns-missing-when-using-the-lists-getlistitems-sharepoint-webservice.aspx

    It worked for me, I hope you can use it too.

    Peter
    Wednesday, June 10, 2009 10:42 PM
  • Hi Peter,

    Even I was not able to get the results directly , so I added the column directly to the dataset if it is missing.
    Changing the xml nodes will be more costly if result set contains more records.

    Thanks for your help , but I hope there will be some solution which returns all the columns to avoid doing dirty work.

    Regards
    Vikas
    Thursday, June 11, 2009 4:44 PM
  • Hi Vikas,

    I don't think you will find a solution out of the box. You can implement your own web service to achive that.
    Walkthrough: Creating a Custom Web Service
    http://msdn.microsoft.com/en-us/library/ms464040.aspx
    Creating a Custom Web Service for SharePoint
    http://it.toolbox.com/blogs/sharepoint-blog/creating-a-custom-web-service-for-sharepoint-13553

    Peter
    • Proposed as answer by Moss-dever Wednesday, June 17, 2009 10:14 AM
    • Marked as answer by Aaron Han - MSFT Friday, June 19, 2009 6:33 AM
    Thursday, June 11, 2009 7:39 PM
  • You can avoid having to loop through all the results in order to add the missing column. The following solution is very fast. The column will not be created in the datatable only if none of the items do not contain a value for that column. So this solution will use linq to check which columns are missing based on your view fields you requested in your request and add it.

    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();
                doc.LoadXml(query);
    
    
                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);
                ds.ReadXml(sr);
    
                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;
                
    }

    certdev.com
    Friday, June 12, 2009 9:34 PM
  • Hi,
    I did something similar and was able to do a workaround, but to get all the columns directly we need to create a custom webservice as suggested by Peter.

    Regards
    Vikas
    Friday, June 12, 2009 11:06 PM
  • Vikas,

    Can you please tell me how you solved your problem?  I have same issue I am trying to get data from sharePointlist to reporting services 2005 report.
    Sorry I am new in XML / reporting services please tell me the easiest way.

    Thank you
    simam
    Wednesday, June 17, 2009 8:32 PM
  • Simam,

    Don't forget that the easiest way not allways the best / optimal solution.

    If you would like to use the SharePoint data from RS 2005, probably you would need to create a custom web service that contains the data necesseary for your report.
    See the links in my earlier post (Thursday, June 11, 2009 7:39 PM).

    Peter
    Monday, June 22, 2009 7:59 AM
  • Is there another web method better suited? What is the point of setting the ViewFields XMLNode if it's going to be totally ignored for attritbutes with no value. Seems kind of weak. There should at least be some sort of option or parameter on the web method.
    Friday, December 11, 2009 7:10 AM