Merging a datasets datatables to a single datatable c#

Question

Please assist. I'm struggling a bit with this.This is the xml output of a c# dataset. Its a Quickbooks response loaded into a dataset. I've removed the bulk of data and columns for clarity.

This hierachy, is (table>row>table) relation. I'm trying to get to a single datatable. So table [CustomerRet]+[Adjoining tables] return a single datatable with merged column names.

COLUMNS
ListID
Col1
Col2
BillAddress (another table holding results)
Addr1
Addr2
Col

Will now return table structure and rows (only need the first row of the adjoined table)

ListID, Col1, Col2, BillAddressAddr1, BillAddressAddr2, Col6...etc

<?xml version="1.0" standalone="yes"?>
<QBXML>
  <xs:schema id="QBXML" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="QBXML" msdata:IsDataSet="true" msdata:Locale="en-US">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="QBXMLMsgsRs">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="CustomerQueryRs" minOccurs="0" maxOccurs="unbounded">
                  <xs:complexType>
                    <xs:sequence>
                      <xs:element name="CustomerRet" minOccurs="0" maxOccurs="unbounded">
                        <xs:complexType>
                          <xs:sequence>
                            <xs:element name="ListID" type="xs:string" minOccurs="0" />
                            <xs:element name="BillAddress" minOccurs="0" maxOccurs="unbounded">
                              <xs:complexType>
                                <xs:sequence>
                                  <xs:element name="Addr1" type="xs:string" minOccurs="0" />
                                  <xs:element name="Addr2" type="xs:string" minOccurs="0" />
                                  <xs:element name="Addr3" type="xs:string" minOccurs="0" />
                                </xs:sequence>
                              </xs:complexType>
                            </xs:element>                            
                          </xs:sequence>
                        </xs:complexType>
                      </xs:element>
                    </xs:sequence>                   
                  </xs:complexType>
                </xs:element>
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <QBXMLMsgsRs>
    <CustomerQueryRs statusCode="0" statusSeverity="Info" statusMessage="Status OK">
      <CustomerRet>
        <ListID>20000-1197738925</ListID>
        <BillAddress>
          <Addr1>Cristina Andres</Addr1>
          <Addr2>4242 Cypress Hill Rd</Addr2>
        </BillAddress>
      </CustomerRet>
    </CustomerQueryRs>
  </QBXMLMsgsRs>
</QBXML>

 //something like
                DataTable dtFinal = new DataTable();
                //Load the Qb Dataset    
                DataSet dsQb = new DataSet();
                dsQb.ReadXml(fileName, XmlReadMode.Auto);
                dtFinal = dsQb.Tables[2].Clone();

                foreach(DataRow dr in dsQb.Tables[2].Rows)
                {
                    //Add the missing columns...?

                }
                //Import the rows

Anyone have any pointers? Maybe linq?

score 0 · Answer 1 · answered Jan 03 '16 at 14:31

The DataSet.ReadXml() doesn't work well when the number of levels of xml children gets too large. Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq; 

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENANE = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            XDocument doc = XDocument.Load(FILENANE);

            var customerQueryRs = doc.Descendants("CustomerQueryRs").Select(x => new {
                statusCode = x.Attribute("statusCode").Value,
                statusSeverity = x.Attribute("statusSeverity").Value,
                statusMessage = x.Attribute("statusMessage").Value,
                listID = x.Descendants("ListID").FirstOrDefault().Value,
                addresses = x.Descendants("BillAddress").FirstOrDefault().Descendants().Select(y => new {
                    address = y.Value
                }).ToList()
            }).ToList();
        }
    }
}

Thank you :) mmm. Looks interesting. I'll give it a try. My current approach is a bit messy :( and partially works. I'm sure it's easier to mess around with the node list than play inside that dataset. Some of them can get huge. The one prob is, I won't know the name of the columns, but i think i can work around that. — user2185210, Jan 03 '16 at 14:40
You may ant to looks at the recursive approach in following posting : http://stackoverflow.com/questions/28976601/recursion-parsing-xml-file-with-attributes-into-treeview-c-sharp — jdweng, Jan 03 '16 at 14:51

Merging a datasets datatables to a single datatable c#

1 Answers1