jonathana jonathana - 1 year ago 100 Question

converting xml of sharepoint list to dataset

its been more than one week and i still cant figure out what is the problem here. hope you could help me. i am successfully retrieving xml from share point server using SOAP web service, then i am converting the xml to dataset object, i am getting the dataset successfully but its "damaged" - there are few columns that has a missing values from the xml. here is the code for importing the xml using SOAP:

private void button2_Click(object sender, EventArgs e)
oportal.Lists list = new oportal.Lists();
list.Credentials = System.Net.CredentialCache.DefaultCredentials;
list.Url = "http://xxx/xxx/xxx/xxx/_vti_bin/Lists.asmx";

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>TRUE</IncludeMandatoryColumns>" +

ndViewFields.InnerXml = @"<FieldRef Name='שם לקוח' />
<FieldRef Name='שם מתל'/>";

XmlNode ndListItems = list.GetListItems("{DD1CF626-62E1-4E36-BF2B-C7D08EA73674}",null, ndQUery, ndViewFields, "14000", ndQueryOptions, null);

dataGridView1.DataSource = ConverttYourXmlNodeToDataSet(ndListItems).Tables[1];
catch(System.Web.Services.Protocols.SoapException ex) {
MessageBox.Show(ex.Message + Environment.NewLine + ex.Detail.InnerText + Environment.NewLine + ex.StackTrace);



the xml i am getting looks ok, the column (field) names are in hebrew language but the xml shows them in HTML Entity (Hexadecimal) - maybe thats the root of the problem?

after i am getting the xml i am converting it into dataset with ConverttYourXmlNodeToDataSet() function here is the code:

public static DataSet ConverttYourXmlNodeToDataSet(XmlNode xmlnodeinput)
DataSet dataset = null;
if (xmlnodeinput != null)
XmlTextReader xtr = new XmlTextReader(xmlnodeinput.OuterXml, XmlNodeType.Element,null);
dataset = new DataSet();

return dataset;

i am getting the dataset succefuly but like i mentioned its damaged because of the missing values, they exist in the xml but not in the dataset (the columns exist but not the values).

please have a look at this screen shoot:

Dataset of sharepoint list xml

iv`e surrounded with red color one of the columns that dont get their value from the XML. here is a screen shoot of the xml and the missing value that should be in the dataset surronded with red color:

XML of sharepoint list

also tryed to convert the xml to dataset like that but the results are the same:

public static DataSet read(XmlNode x) {

DataSet ds = new DataSet();

XmlReader r = new XmlNodeReader(x);

return ds;


hope someone can help me here. tnx.


ok i have not solved it yet but i discovered few things that may lead to the solution:

  1. i have noticed that all the columns that appears without values in the dataset are the columns that filled by the user in the website controls, and guess what? all the captions for those columns are in hebrew language , hence the columns that appears with values on the dataset are sharepint default columns, and their captions are in English, and they dont have HTML Entity (Hexadecimal)! name (look at the xml). So it makes me suspect that the problem is related to the HTML Entity (Hexadecimal) column names that related to the Hebrew captions... my assumption is that the dataset cant interpret this HTML Entity (Hexadecimal) encode. another clue is that the column name as its spelled in the dataset (for example look at the screen shoot of the datagridview above - column 4 from the left side (index 3)) is not interpreted right, the column name should be 'שם מתל' and thats all - as you can see (you don`t have to understand Hebrew for that) only half of this Hebrew string is there and concatenated to it part of the encoded HTML Entity (Hexadecimal).

  2. i have noticed that when im sorting columns in share point website the requested url using the hexadecimal html entity of the column and not the hebrew name of the column:


and i expected to see something like:

http://xxx/xxx/xxx/xxx/Lists/1/view9.aspx?View={c2538b95-efae-453b-b536-aad6f98265ed}&SortField=_'שם מתל'=Desc

so i made a change in my code in order to explicitly declare the column names in the encoded HTML Entity (Hexadecimal) and i did this (the original code is above):

ndViewFields.InnerXml = @"<FieldRef Name='_x05d0__x05d9__x05e9__x05d5__x05' />
<FieldRef Name='_x05e9__x05dd__x0020__x05de__x05'/>";

now the result i was getting in the dataset has changed! the change was that the columns i explicitly declared them moved to the first column indexes of the dataset but still there aren`t any values in those columns.

so, to summery all of that digging, here are my assumptions:

*. the problem is the interpreter between the xml and the dataset

*. the interpreter is defective because he cant interpret ecoded HTML Entity (Hexadecimal) properly

*. column captions written in HTML Entity (Hexadecimal) because their captions are in hebrew

*. solution can be or making columns captions to plain hebrew (in xml) or doing something that will make the interpreter between xml and dataset work properly (maybe using XmlParserContext class?? - tryed a little with no success or other class that can manipulate encoded xml text).

Answer Source

finally, after i have accomplish to solve this. the solution i found was super simple.

i have been searching and struggling some time for a solution, never found one and then this simple solution crossed my mind.

only one line of code was needed:

  String s = xmlnodeinput.OuterXml.Replace("ows__x05e9__x05dd__x0020__x05de__x05",

just replacing the hex value and the dataset loads properly.

also i checked to see that there are no time proccessing issues(replacing string takes less then a second):

start reading 12000 rows: 26/03/2016 17:18:00 start replacing strings: 26/03/2016 17:18:04 load xml string to dataset: 26/03/2016 17:18:04 finish loading dataset: 26/03/2016 17:18:04

the complte converstion from xml to dataset function:

 public static DataSet ConverttYourXmlNodeToDataSet(XmlNode xmlnodeinput)
        //declaring data set object
        DataSet dataset = null;
        if (xmlnodeinput != null)
            NameTable nt = new NameTable();
            XmlNamespaceManager nsmgr = new XmlNamespaceManager(nt);
            XmlParserContext context = new XmlParserContext(nt, null, "heb",null, null, null, null, null, XmlSpace.None,Encoding.Unicode);

            String s = xmlnodeinput.OuterXml.Replace("ows__x05e9__x05dd__x0020__x05de__x05", "AccountManager");

            XmlTextReader xtr = new XmlTextReader(s, XmlNodeType.Element,context);

            dataset = new DataSet();


        return dataset;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download