Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Convert XML file to ONE DataTable

xml c# datatable

This topic has been archived. This means that you cannot reply to this topic.
5 replies to this topic

#1 Tchpowdog

Tchpowdog

    CC Regular

  • Member
  • PipPipPip
  • 44 posts

Posted 03 December 2014 - 10:58 AM

I'm trying to read through XML files and put the info into a DataTable.  I have no control over the format and schema(or lack thereof) of the XML files.  These XML files are export files from another system in which I can not edit.  The XML files are transactions that occur at a POS device.  Each file is ONE transaction and each transaction can have multiple, or one, line item.  This is an example of an XML file with multiple line items:
 
<?xml version ="1.0" encoding="ISO-8859-1" standalone="no"?>
 <NAXML-POSJournal version="3.3">
  <TransmissionHeader>
   <StoreLocationID>300</StoreLocationID>
   <VendorName>Gilbarco-VeederRoot</VendorName>
   <VendorModelVersion>10.00.23.01K</VendorModelVersion>
  </TransmissionHeader>
  <JournalReport>
   <JournalHeader>
    <ReportSequenceNumber>805</ReportSequenceNumber>
    <PrimaryReportPeriod>2</PrimaryReportPeriod>
    <SecondaryReportPeriod>0</SecondaryReportPeriod>
    <BeginDate>2014-12-03</BeginDate>
    <BeginTime>02:04:54</BeginTime>
    <EndDate>2100-01-01</EndDate>
    <EndTime>00:00:00</EndTime>
   </JournalHeader>
   <SaleEvent>
    <EventSequenceID>1</EventSequenceID>
    <TrainingModeFlag value="no"/>
    <CashierID>4741</CashierID>
    <RegisterID>2</RegisterID>
    <TillID>3681</TillID>
    <OutsideSalesFlag value="no"/>
    <TransactionID>2792</TransactionID>
    <EventStartDate>2014-12-03</EventStartDate>
    <EventStartTime>12:24:27</EventStartTime>
    <EventEndDate>2014-12-03</EventEndDate>
    <EventEndTime>12:25:00</EventEndTime>
    <BusinessDate>2014-12-03</BusinessDate>
    <ReceiptDate>2014-12-03</ReceiptDate>
    <ReceiptTime>12:25:00</ReceiptTime>
    <OfflineFlag value="no"/>
    <SuspendFlag value="no"/>
    <TransactionDetailGroup>
     <TransactionLine status="normal">
     <ItemLine>
      <ItemCode>
       <POSCodeFormat format="upcA"></POSCodeFormat>
       <POSCode>99100400000</POSCode>
       <POSCodeModifier name="pc">1</POSCodeModifier>
      </ItemCode>
      <Description>44/OZ FOUNTAIN</Description>
      <EntryMethod method="manual"></EntryMethod>
      <ActualSalesPrice>1.49</ActualSalesPrice>
      <MerchandiseCode>26</MerchandiseCode>
      <SellingUnits>1</SellingUnits>
      <RegularSellPrice>1.49</RegularSellPrice>
      <SalesQuantity>1</SalesQuantity>
      <SalesAmount>1.49</SalesAmount>
      <ItemTax>
       <TaxLevelID>102</TaxLevelID>
      </ItemTax>
      <SalesRestriction>
       <SalesRestrictFlag value="no"  type="other"></SalesRestrictFlag>
      </SalesRestriction>
     </ItemLine>
    </TransactionLine>
    <TransactionLine status="normal">
    <TransactionTax>
     <TaxLevelID>102</TaxLevelID>
     <TaxableSalesAmount>8.17</TaxableSalesAmount>
     <TaxCollectedAmount>.74</TaxCollectedAmount>
     <TaxableSalesRefundedAmount>0</TaxableSalesRefundedAmount>
     <TaxRefundedAmount>0</TaxRefundedAmount>
     <TaxExemptSalesAmount>0</TaxExemptSalesAmount>
     <TaxExemptSalesRefundedAmount>0</TaxExemptSalesRefundedAmount>
     <TaxForgivenSalesAmount>0</TaxForgivenSalesAmount>
     <TaxForgivenSalesRefundedAmount>0</TaxForgivenSalesRefundedAmount>
     <TaxForgivenAmount>0</TaxForgivenAmount>
    </TransactionTax>
   </TransactionLine>
   <TransactionLine status="normal">
   <ItemLine>
    <ItemCode>
     <POSCodeFormat format="upcA"></POSCodeFormat>
     <POSCode>01070080561</POSCode>
     <POSCodeModifier name="pc">1</POSCodeModifier>
    </ItemCode>
    <Description>SUPER BUBBLE 3C</Description>
    <EntryMethod method="manual"></EntryMethod>
    <ActualSalesPrice>.6</ActualSalesPrice>
    <MerchandiseCode>6</MerchandiseCode>
    <SellingUnits>1</SellingUnits>
    <RegularSellPrice>.05</RegularSellPrice>
    <SalesQuantity>12</SalesQuantity>
    <SalesAmount>.6</SalesAmount>
    <ItemTax>
     <TaxLevelID>101</TaxLevelID>
    </ItemTax>
    <SalesRestriction>
     <SalesRestrictFlag value="no"  type="other"></SalesRestrictFlag>
    </SalesRestriction>
   </ItemLine>
  </TransactionLine>
  <TransactionLine status="normal">
  <TransactionTax>
   <TaxLevelID>101</TaxLevelID>
   <TaxableSalesAmount>.6</TaxableSalesAmount>
   <TaxCollectedAmount>.02</TaxCollectedAmount>
   <TaxableSalesRefundedAmount>0</TaxableSalesRefundedAmount>
   <TaxRefundedAmount>0</TaxRefundedAmount>
   <TaxExemptSalesAmount>0</TaxExemptSalesAmount>
   <TaxExemptSalesRefundedAmount>0</TaxExemptSalesRefundedAmount>
   <TaxForgivenSalesAmount>0</TaxForgivenSalesAmount>
   <TaxForgivenSalesRefundedAmount>0</TaxForgivenSalesRefundedAmount>
   <TaxForgivenAmount>0</TaxForgivenAmount>
  </TransactionTax>
 </TransactionLine>
 <TransactionLine status="normal">
 <ItemLine>
  <ItemCode>
   <POSCodeFormat format="upcA"></POSCodeFormat>
   <POSCode>99865300000</POSCode>
   <POSCodeModifier name="pc">1</POSCodeModifier>
  </ItemCode>
  <Description>CHICKEN BY THE PIECE THIGH</Description>
  <EntryMethod method="scan"></EntryMethod>
  <ActualSalesPrice>1.69</ActualSalesPrice>
  <MerchandiseCode>20</MerchandiseCode>
  <SellingUnits>1</SellingUnits>
  <RegularSellPrice>1.69</RegularSellPrice>
  <SalesQuantity>1</SalesQuantity>
  <SalesAmount>1.69</SalesAmount>
  <ItemTax>
   <TaxLevelID>102</TaxLevelID>
  </ItemTax>
  <SalesRestriction>
   <SalesRestrictFlag value="no"  type="other"></SalesRestrictFlag>
  </SalesRestriction>
 </ItemLine>
</TransactionLine>
<TransactionLine status="normal">
<ItemLine>
 <ItemCode>
  <POSCodeFormat format="upcA"></POSCodeFormat>
  <POSCode>99820600000</POSCode>
  <POSCodeModifier name="pc">1</POSCodeModifier>
 </ItemCode>
 <Description>3 CHICKEN TENDERS SNACK</Description>
 <EntryMethod method="scan"></EntryMethod>
 <ActualSalesPrice>4.99</ActualSalesPrice>
 <MerchandiseCode>20</MerchandiseCode>
 <SellingUnits>1</SellingUnits>
 <RegularSellPrice>4.99</RegularSellPrice>
 <SalesQuantity>1</SalesQuantity>
 <SalesAmount>4.99</SalesAmount>
 <ItemTax>
  <TaxLevelID>102</TaxLevelID>
 </ItemTax>
 <SalesRestriction>
  <SalesRestrictFlag value="no"  type="other"></SalesRestrictFlag>
 </SalesRestriction>
</ItemLine>
</TransactionLine>
<TransactionLine status="cancel">
<ItemLine>
 <ItemCode>
  <POSCodeFormat format="upcA"></POSCodeFormat>
  <POSCode>99865300000</POSCode>
  <POSCodeModifier name="pc">1</POSCodeModifier>
 </ItemCode>
 <Description>CHICKEN BY THE PIECE THIGH</Description>
 <EntryMethod method="scan"></EntryMethod>
 <ActualSalesPrice>0</ActualSalesPrice>
 <MerchandiseCode>20</MerchandiseCode>
 <SellingUnits>1</SellingUnits>
 <RegularSellPrice>1.69</RegularSellPrice>
 <SalesQuantity>1</SalesQuantity>
 <SalesAmount>0</SalesAmount>
 <ItemTax>
  <TaxLevelID>102</TaxLevelID>
 </ItemTax>
 <SalesRestriction>
  <SalesRestrictFlag value="no"  type="other"></SalesRestrictFlag>
 </SalesRestriction>
</ItemLine>
</TransactionLine>
<TransactionLine status="normal">
<TenderInfo>
 <Tender>
  <TenderCode>creditCards</TenderCode>
  <TenderSubCode>generic</TenderSubCode>
 </Tender>
 <TenderAmount>9.53</TenderAmount>
 <ChangeFlag value="no"/>
 <Authorization>
  <PreAuthorizationFlag/>
  <RequestedAmount/>
  <AuthorizationResponseCode>773617</AuthorizationResponseCode>
  <AuthorizationResponseDescription/>
  <ApprovalReferenceCode>081479</ApprovalReferenceCode>
  <ReferenceNumber/>
  <ProviderID>Mastercard</ProviderID>
  <AuthorizationDate>2014-12-03</AuthorizationDate>
  <AuthorizationTime>12:24:27</AuthorizationTime>
  <HostAuthorizedFlag/>
  <AuthorizationApprovalDescription/>
  <AuthorizingTerminalID/>
  <ForceOnLineFlag/>
  <ElectronicSignature/>
  <AuthorizedChargeAmount>9.53</AuthorizedChargeAmount>
 </Authorization>
 <AccountInfo>
  <AccountID>XXXXXXXXXXXXXXXX</AccountID>
  <AccountName>Mastercard</AccountName>
  <AccountFirstName/>
  <AccountMiddleName/>
  <AccountLastName/>
 </AccountInfo>
</TenderInfo>
</TransactionLine>
</TransactionDetailGroup>
<TransactionSummary>
 <TransactionTotalGrossAmount>8.77</TransactionTotalGrossAmount>
 <TransactionTotalNetAmount>9.53</TransactionTotalNetAmount>
 <TransactionTotalTaxSalesAmount>.76</TransactionTotalTaxSalesAmount>
 <TransactionTotalTaxExemptAmount>0</TransactionTotalTaxExemptAmount>
 <TransactionTotalTaxNetAmount>.76</TransactionTotalTaxNetAmount>
 <TransactionTotalGrandAmount direction="Collected">9.53</TransactionTotalGrandAmount>
</TransactionSummary>
</SaleEvent>
</JournalReport>
</NAXML-POSJournal>
 
It's a mess, I know... I tried "Dataset.ReadXML()" to no success.  When I do this, the dataset contains 20+ tables in it and there's no efficient way of piecing them together.  Does anyone know of C# tools or methods I can use to make this less frustrating??  I've attached the .XML file as well.  Thanks!

Attached Files



#2 Blimp

Blimp

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 199 posts

Posted 04 December 2014 - 01:38 AM

It's not going to work because you're got nested arrays in there. You can't do it through this approach.

 

Better to go through the XmlDocument method and construct it manually. It'll be a pain, but you'll have a lot more control over the data.



#3 Tchpowdog

Tchpowdog

    CC Regular

  • Member
  • PipPipPip
  • 44 posts

Posted 04 December 2014 - 10:20 AM

These XML files are a PCATS standard.  I've taken a random sample of about 15 different XML files and out of the 15, there were 7 different size datasets returned - by number of tables:  14, 16, 20, 22, 25, 28, 31...  I have no idea how I'm going to extract this data efficiently.  Whoever designed this is an idiot... Or, hopefully, I'm just the idiot and someone can explain the best way to extract this data...


Edited by Tchpowdog, 04 December 2014 - 10:21 AM.


#4 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 04 December 2014 - 11:10 AM

For the example you shared, it looks like it should NOT go into a single table, for starters. I would structure it in a Journal table, a Sale table, and a Transaction table. You could, if you really want to, put it all in a Transaction table, but that seems like a really bad idea. It is also suggested that you should have some sort of an Item table, as well.

 

The data doesn't look like a mess to me, at all. It's well-structured. I have to ask, why do you want it in a single table? What sort of table structure do you envision?


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#5 Tchpowdog

Tchpowdog

    CC Regular

  • Member
  • PipPipPip
  • 44 posts

Posted 04 December 2014 - 11:24 AM

One table would duplicate a lot of data, I understand that.  I'm envisioning two tables. The first is a Transaction table which contains all of the Transaction details (i.e. BusinessDate, Time, TenderAmount, Account Info, TransactionID, etc.).  And the second, a LineItem table, which contains all of the Items associated with a common TransactionID.  The two tables would be linked at TransactionID.  Each file is one transaction.  The problem is, each file varies SO MUCH because of the many different TYPES of items(i.e. Fuel, In-store Item, Carwash, Lottery, Cash-out... there's several more).  Also, one transaction can contain any combination of these different item types...

 

It's just aggravating to sort through!  I was hoping to simplify it into one or two tables so I don't have to write 20-30 different blocks of code to process it.


Edited by Tchpowdog, 04 December 2014 - 11:24 AM.


#6 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 04 December 2014 - 01:52 PM

If you can identify ALL possible fields for the types of Items, then you can create a table that only populates those that are present, and leaves the rest null.


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/





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