DSXchange

Hi,
I need to load a xml file which contains multiple transactions from POS with transaction detail into respective tables (e.g. TransactionHeader, RetailTransaction, RetailTransactionDetail, Tendor, Tax ..etc.)
In my job I am using External Source Stage ->XMLInput->TF->multiple ODBCconnector stages for different tables. From transformer stage I am spiltting fields to corresponding tables. I used XML Meta Data Importer to import metadata from xml file.
Issues:
Currently my xml test file have 4 transactions but my target tables showing only 2 transaction data.
I am not sure if I have correct field in XMLInputStage Output as key, currently I have marked LineItem.SequenceNumber as key.
I cannot try Duke's best practice jobs because we are parallel job only shop. Please suggest me how to design correctly this jobs.


<POSLog>
<Transaction>
<RetailStoreID>HighStreet</RetailStoreID>
<WorkstationID>POS5</WorkstationID>
<SequenceNumber>2540</SequenceNumber>
<BusinessDayDate>2001-08-13</BusinessDayDate>
<OperatorID>John</OperatorID>
<RetailTransaction Version="2.2">
<LineItem>
<SequenceNumber>1</SequenceNumber>
<Sale ItemType="Stock">
<POSIdentity>
<POSItemID>01234</POSItemID>
</POSIdentity>
<ExtendedAmount>1.63</ExtendedAmount>
<Quantity>1</Quantity>
<Tax TaxType="Sales">
<SequenceNumber>2</SequenceNumber>
<TaxableAmount TaxIncludedInTaxableAmountFlag="false">1.63</TaxableAmount>
<Amount>0.00</Amount>
<Percent>0.00</Percent>
<TaxOverride>
<CustomerOverrideID />
<OriginalPercent>10.00</OriginalPercent>
<OriginalTaxAmount>0.16</OriginalTaxAmount>
<NewTaxPercent>0.00</NewTaxPercent>
<NewTaxAmount>0.00</NewTaxAmount>
<ReasonCode>FoodStamps</ReasonCode>
</TaxOverride>
</Tax>
</Sale>
</LineItem>
<LineItem>
<SequenceNumber>3</SequenceNumber>
<Sale ItemType="Stock">
<POSIdentity>
<POSItemID>9444</POSItemID>
</POSIdentity>
<Description>Chocolate Bar</Description>
<ActualSalesUnitPrice>1.63</ActualSalesUnitPrice>
<ExtendedAmount>3.26</ExtendedAmount>
<Quantity>2</Quantity>
</Sale>
</LineItem>
<LineItem>
<SequenceNumber>4</SequenceNumber>
<Tax TaxType="Sales">
<TaxableAmount TaxIncludedInTaxableAmountFlag="false">3.26</TaxableAmount>
<Amount>0.32</Amount>
<Percent>10.00</Percent>
</Tax>
</LineItem>
<LineItem>
<SequenceNumber>5</SequenceNumber>
<Tender TenderType="FoodStamps" TypeCode="Sale">
<Amount>1.63</Amount>
<FoodStamps>
<FederalID>123132-012</FederalID>
<SerialNumber>1234568</SerialNumber>
</FoodStamps>
</Tender>
</LineItem>
<LineItem>
<SequenceNumber>6</SequenceNumber>
<Tender TenderType="Cash" TypeCode="Sale">
<Amount>3.58</Amount>
</Tender>
</LineItem>
</RetailTransaction>
</Transaction>
<Transaction>
<RetailStoreID>LowStreet</RetailStoreID>
<WorkstationID>POS6</WorkstationID>
<SequenceNumber>4294</SequenceNumber>
<BusinessDayDate>2001-08-13</BusinessDayDate>
<OperatorID>Paul</OperatorID>
<RetailTransaction Version="2.2">
<LineItem>
<SequenceNumber>7</SequenceNumber>
<Sale ItemType="Stock">
<POSIdentity>
<POSItemID>01234567890124</POSItemID>
</POSIdentity>
<ExtendedAmount>10.63</ExtendedAmount>
</Sale>
</LineItem>
<LineItem>
<SequenceNumber>8</SequenceNumber>
<Tender TenderType="ManufacturerCoupon" TypeCode="Sale">
<Amount>2.00</Amount>
<Coupon>
<Quantity>1</Quantity>
<PrimaryLabel />
<ManufacturerID />
<FamilyCode />
<ExpirationDate>2003-12-31</ExpirationDate>
</Coupon>
</Tender>
</LineItem>
<LineItem>
<SequenceNumber>9</SequenceNumber>
<Tender TenderType="Cash" TypeCode="Sale">
<Amount>8.63</Amount>
</Tender>
</LineItem>
</RetailTransaction>
</Transaction>
<Transaction>
<RetailStoreID>DownStreet</RetailStoreID>
<WorkstationID>POS7</WorkstationID>
<SequenceNumber>9672</SequenceNumber>
<BusinessDayDate>2001-08-13</BusinessDayDate>
<OperatorID>JohnP</OperatorID>
<RetailTransaction Version="2.2">
<LineItem>
<SequenceNumber>10</SequenceNumber>
<Sale ItemType="Stock">
<POSIdentity>
<POSItemID>01234567890123</POSItemID>
</POSIdentity>
<ExtendedAmount>10.00</ExtendedAmount>
</Sale>
</LineItem>
<LineItem>
<SequenceNumber>11</SequenceNumber>
<Tender TenderType="Cash" TypeCode="Sale">
<Amount>11.00</Amount>
</Tender>
</LineItem>
<LineItem>
<SequenceNumber>12</SequenceNumber>
<Tax TaxType="Sales">
<TaxableAmount TaxIncludedInTaxableAmountFlag="false">10.00</TaxableAmount>
<Amount>1.00</Amount>
<Percent>10.00</Percent>
</Tax>
</LineItem>
</RetailTransaction>
</Transaction>
<Transaction>
<RetailStoreID>UpStreet</RetailStoreID>
<WorkstationID>POS5</WorkstationID>
<SequenceNumber>7295</SequenceNumber>
<BusinessDayDate>2001-08-13</BusinessDayDate>
<OperatorID>John</OperatorID>
<RetailTransaction Version="2.2">
<LineItem>
<SequenceNumber>13</SequenceNumber>
<Sale ItemType="Stock">
<POSIdentity>
<POSItemID>01234567890123</POSItemID>
</POSIdentity>
<ExtendedAmount>4.89</ExtendedAmount>
<RetailPriceModifier MethodCode="Promotion">
<SequenceNumber>14</SequenceNumber>
<PromotionID>a234</PromotionID>
<ReasonCode>Coupon</ReasonCode>
</RetailPriceModifier>
</Sale>
</LineItem>
<Customer>
<CustomerID>A23BD</CustomerID>
<AccountNumber>4567567</AccountNumber>
</Customer>
</RetailTransaction>
</Transaction>
</POSLog>


Thanks and appreciate your help.

Have you nominated a repeating element?

dsinfo wrote:Currently my xml test file have 4 transactions but my target tables showing only 2 transaction data.

Which two and what's different about them from the two that did not load?

Thanks for looking into this.
The field 'Poslog1.LineItemSequenceNumber' is marked as key in Output of XMLInput stage, I think that is the repeating element, I don't know if there is any other way to choose repeating elements.

The last two transaction data is not loading into the tables, I dont see any difference except the very last transaction have customer data.

Looking forward to hearing from you.

Thanks.

Resolved, my xml file was not good, and had to edit Xpath.

Thanks.