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.
Load XML file
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Load XML file
Which two and what's different about them from the two that did not load?dsinfo wrote:Currently my xml test file have 4 transactions but my target tables showing only 2 transaction data.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Load XML file
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.
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.
Re: Load XML file
Resolved, my xml file was not good, and had to edit Xpath.
Thanks.
Thanks.