Load XML file

[dsinfo]

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.

[ray.wurlod]

Have you nominated a repeating element?

[chulett]

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?

[dsinfo]

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.

[dsinfo]

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

Thanks.