title: How to convert XElement object into a dataset or datatable?
body: <p>I'm accessing a SharePoint(2007) list through a service reference of lists.asmx created in my console application. Console application is created in VS2012 and .Net 4.5 framework.</p>
  
  <pre><code>ListsSoapClient proxy1 = new ListsSoapClient();
  proxy1.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
  proxy1.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;    
  string listName = "List_For_Test";
  DataSet ds1 = new DataSet();
  XElement ndQuery = new XElement("Query", "");
  XElement ndViewFields = new XElement("ViewFields", "");
  XElement ndQueryOptions = new XElement("QueryOptions", "");
  XElement items = proxy1.GetListItems(listName, null, ndQuery, ndViewFields, null, ndQueryOptions, null);
  ds1 = XElementToDataSet(items);
  </code></pre>
  
  <p>I need to convert the XML returned in items XElement to dataset/datatable and pass it to a stored procedure written in SQL Server 2008 R2.</p>
  
  <p>I tried using following code but it gave me error</p>
  
  <pre><code>    XElement setup = (from p in x.Descendants() select p).First();
      foreach (XElement xe in setup.Descendants()) // build your DataTable
      dt.Columns.Add(new DataColumn(xe.Name.ToString(), typeof(string))); 
      // on second iteration gave error that "'{#RowsetSchema}row'" column already existing.
  </code></pre>
  
  <p>This is the XML stream returned from GetListItems() webservice method call</p>
  
  <blockquote>
    <p><code>&lt;</code>listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
    xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"
    xmlns="http://schemas.microsoft.com/sharepoint/soap/"> <code>&lt;</code>rs:data
    ItemCount="2"> <code>&lt;</code>z:row
    ows_ContentTypeId="0x01006B57C3DFF39626458A729755ABF3A370"
    ows_Title="Item#1" ows_ListID="1.00000000000000"
    ows_Loan_x0020_Number="1234.00000000000" ows_ID="2"
    ows_ContentType="Item" ows_Modified="2014-04-01 10:06:38"
    ows_Created="2014-04-01 10:06:38" ows_Author="7017;#, Khushi"
    ows_Editor="7017;#, Khushi" ows_owshiddenversion="1"
    ows_WorkflowVersion="1" ows__UIVersion="512"
    ows__UIVersionString="1.0" ows_Attachments="0"
    ows__ModerationStatus="0" ows_LinkTitleNoMenu="Item#1"
    ows_LinkTitle="Item#1" ows_SelectTitle="2"
    ows_Order="200.000000000000"
    ows_GUID="{2832ED2B-A45D-4950-8E0E-EEB3E31199AF}" ows_FileRef="2;#
    List_For_Test/2_.000" ows_FileDirRef="2;#/List_For_Test"
    ows_Last_x0020_Modified="2;#2014-04-01 10:06:38"
    ows_Created_x0020_Date="2;#2014-04-01 10:06:38" ows_FSObjType="2;#0"
    ows_PermMask="0x7fffffffffffffff" ows_FileLeafRef="2;#2_.000"
    ows_UniqueId="2;#{F3336392-74F7-45C2-A175-EF36DA219812}"
    ows_ProgId="2;#"
    ows_ScopeId="2;#{E9E1B331-7993-4586-93DC-3690EBEBAD9E}"
    ows__EditMenuTableStart="2_.000" ows__EditMenuTableEnd="2"
    ows_LinkFilenameNoMenu="2_.000" ows_LinkFilename="2_.000"
    ows_ServerUrl="/ List_For_Test/2_.000"
    ows_EncodedAbsUrl="Lists/List_For_Test/2_.000" ows_BaseName="2_"
    ows_MetaInfo="2;#" ows__Level="1" ows__IsCurrentVersion="1" /><br>
    <code>&lt;</code>z:row ows_ContentTypeId="0x01006B57C3DFF39626458A729755ABF3A370"
    ows_Title="Item#2" ows_ListID="2.00000000000000"
    ows_Loan_x0020_Number="5678.00000000000" ows_ID="3"
    ows_ContentType="Item" ows_Modified="2014-04-01 10:06:53"
    ows_Created="2014-04-01 10:06:53" ows_Author="7017;#Khushi"
    ows_Editor="7017;#, Khushi" ows_owshiddenversion="1"
    ows_WorkflowVersion="1" ows__UIVersion="512"
    ows__UIVersionString="1.0" ows_Attachments="0"
    ows__ModerationStatus="0" ows_LinkTitleNoMenu="Item#2"
    ows_LinkTitle="Item#2" ows_SelectTitle="3"
    ows_Order="300.000000000000"
    ows_GUID="{8A6C6A66-A795-4054-B793-789B739EA881}" ows_FileRef="3;#
    /List_For_Test/3_.000" ows_FileDirRef="3;# /List_For_Test"
    ows_Last_x0020_Modified="3;#2014-04-01 10:06:53"
    ows_Created_x0020_Date="3;#2014-04-01 10:06:53" ows_FSObjType="3;#0"
    ows_PermMask="0x7fffffffffffffff" ows_FileLeafRef="3;#3_.000"
    ows_UniqueId="3;#{434E5737-5591-4A02-91E5-B2C7A2EFF2B3}"
    ows_ProgId="3;#"
    ows_ScopeId="3;#{E9E1B331-7993-4586-93DC-3690EBEBAD9E}"
    ows__EditMenuTableStart="3_.000" ows__EditMenuTableEnd="3"
    ows_LinkFilenameNoMenu="3_.000" ows_LinkFilename="3_.000"
    ows_ServerUrl="/List_For_Test/3_.000"
    ows_EncodedAbsUrl="Lists/List_For_Test/3_.000" ows_BaseName="3_"
    ows_MetaInfo="3;#" ows__Level="1" ows__IsCurrentVersion="1" /><br>
    <code>&lt;</code>/rs:data>   <code>&lt;</code>/listitems></p>
  </blockquote>