Problem loading from XML to Sequential File

pavans · Post by **pavans** » Fri Mar 14, 2008 2:07 pm

HI
I have a job reading from XML and loading to a Sequential File.
My job design looks like:

FOlder-->XML-->Transformer-->Seq File.

I am able to load only one record.
Multiple records are not getting loaded.
Below is the XML file:
My key column is ReceiptLine ItemID="10000801"
I have enabled Repetion key in enabled......
Please let me what i m missing in the construction.

<Receipt DocumentType="0005" ReceivingNode="Innotrac_H" ReceiptDate="" ReceiptNo="20080204345621" Status="" >
<Shipment EnterpriseCode="Target.com" Status="" OrderNo="Y100000351" ReceivingNode="Innotrac_H" DriverName="67524" />
<ReceiptLines>
<ReceiptLine ItemID="10000801" PrimeLineNo="" CountryOfOrigin="" DispositionCode="" OrderNo="Y100000351" Quantity="5" ReceiptLineNo="" >
<SerialDetail SecondarySerial1="" />
<OrderLine >
<OrderLineTranQuantity ReceivedQty=""/>
</OrderLine>
</ReceiptLine>
<ReceiptLine ItemID="10000901" PrimeLineNo="" CountryOfOrigin="" DispositionCode="" OrderNo="Y100000351" Quantity="5" ReceiptLineNo="" >
<SerialDetail SecondarySerial1="" />
<OrderLine >
<OrderLineTranQuantity ReceivedQty=""/>
</OrderLine>
</ReceiptLine>
</ReceiptLines>
</Receipt>

Thanks

eostic · Post by **eostic** » Fri Mar 14, 2008 3:07 pm

hmm.. Haven't seen one like this in awhile (all real values are attributes).... can't remember if attributes are acceptable for the "Repeating Element" key assignment. Unlikely... try creating a column called:

OrderLineTranQuantity with a varchar datatype and length of say, 100...make it the key instead, and give it an xpath (in the Description) of

/Receipt/ReceiptLines/ReceiptLine/OrderLine/OrderLineTranQuantity/

Later on you'll drop that column, but this appears to be the lowest level repeating element (in your example) and everything else should be treatable as its parent. If I get a chance I'll cut/paste this and try it.

Ernie

eostic · Post by **eostic** » Fri Mar 14, 2008 4:02 pm

just tried it. Worked like a champ. Two rows as expected.

Ernie

pavans · Post by **pavans** » Mon Mar 17, 2008 7:51 am

eostic wrote:just tried it. Worked like a champ. Two rows as expected.

Ernie

Hi Ernie..

Do i need to create a new column in the Transformer stage and pass it to Target.
I could not able to solve the issue.
I tired creating a new column in transformer stage and pass it to sequential file.
But that didnt work............

eostic · Post by **eostic** » Mon Mar 17, 2008 8:07 am

Just follow what I put in the entry above. Try it in a simple test. Have only one column on your output link.... call it "FRED". Make it varchar and length of 300. Put the xpath above "as is" into the Description. Make it the key. Send it anywhere. If your xml is truly as you have above, you will get two rows.

Ernie

pavans · Post by **pavans** » Mon Mar 17, 2008 10:33 am

eostic wrote:Just follow what I put in the entry above. Try it in a simple test. Have only one column on your output link.... call it "FRED". Make it varchar and length of 300. Put the xpath above "as is" into the Description. Make it the key. Send it anywhere. If your xml is truly as you have above, you will get two rows.

Ernie

Tried but...........not successful.

Let me tell you my job design:

Folder Stage-->XML-->Transformer-->Seq File

Folder Stage:
FileName-->Key-->Varchar--->255
Record---->LongVarChar---->200000

XML:
Stage: Repetetion key is enabled here
Input:
XML Source Column : Record
Input:
FileName-->Key-->Varchar--->255
Record---->LongVarChar---->200000

OutPut:
FileName-->Key-->Varchar--->255

Transformer:
I have imported the XML metadata here.

The changes i have made as u said are:

I have made it in XML

Input:

FileName-->Key-->Varchar--->255
Record---->LongVarChar---->200000

Output:
FRED---Key--->VArchar---300-
In Description:
/Receipt/ReceiptLines/ReceiptLine/OrderLine/OrderLineTranQuantity/

Am i going the correct way.

jatayl · Post by **jatayl** » Mon Mar 17, 2008 11:36 am

Why are you bringing all your data in through the folder stage? Why not just the filepath, and then chose url/File Path radio button with the filepath as your input field in the xml stage?

Did you try making ReceiptLine your repetitive key?

[/quote]

pavans · Post by **pavans** » Mon Mar 17, 2008 12:34 pm

jatayl wrote:Why are you bringing all your data in through the folder stage? Why not just the filepath, and then chose url/File Path radio button with the filepath as your input field in the xml stage?

Did you try making ReceiptLine your repetitive key?

[/quote]

I have redesigned my job....

Folder Stage---XML Input----Sequnetial File

I am able to export two rows..........under "FRED" column.

After that..........i m not getting what to do.
Can you please help me............

jatayl · Post by **jatayl** » Mon Mar 17, 2008 1:00 pm

pavans wrote:
Below is the XML file:
My key column is ReceiptLine ItemID="10000801"
I have enabled Repetion key in enabled......
Please let me what i m missing in the construction.

<Receipt DocumentType="0005" ReceivingNode="Innotrac_H" ReceiptDate="" ReceiptNo="20080204345621" Status="" >
<Shipment EnterpriseCode="Target.com" Status="" OrderNo="Y100000351" ReceivingNode="Innotrac_H" DriverName="67524" />
<ReceiptLines>
<ReceiptLine ItemID="10000801" PrimeLineNo="" CountryOfOrigin="" DispositionCode="" OrderNo="Y100000351" Quantity="5" ReceiptLineNo="" >
<SerialDetail SecondarySerial1="" />
<OrderLine >
<OrderLineTranQuantity ReceivedQty=""/>
</OrderLine>
</ReceiptLine>
<ReceiptLine ItemID="10000901" PrimeLineNo="" CountryOfOrigin="" DispositionCode="" OrderNo="Y100000351" Quantity="5" ReceiptLineNo="" >
<SerialDetail SecondarySerial1="" />
<OrderLine >
<OrderLineTranQuantity ReceivedQty=""/>
</OrderLine>
</ReceiptLine>
</ReceiptLines>
</Receipt>

Are you not loading the xpaths from your xml input stage? Sounds like you need to re-import the xml table definitions. You have to define /Receipt/ReceiptLines/ReceiptLine as a field.
You can use that as your repetitive key. If you use the xml import tool, you can pick and choose what fields you want.
See if that works for you.

pavans · Post by **pavans** » Mon Mar 17, 2008 3:12 pm

jatayl wrote:
pavans wrote:
Below is the XML file:
My key column is ReceiptLine ItemID="10000801"
I have enabled Repetion key in enabled......
Please let me what i m missing in the construction.

<Receipt DocumentType="0005" ReceivingNode="Innotrac_H" ReceiptDate="" ReceiptNo="20080204345621" Status="" >
<Shipment EnterpriseCode="Target.com" Status="" OrderNo="Y100000351" ReceivingNode="Innotrac_H" DriverName="67524" />
<ReceiptLines>
<ReceiptLine ItemID="10000801" PrimeLineNo="" CountryOfOrigin="" DispositionCode="" OrderNo="Y100000351" Quantity="5" ReceiptLineNo="" >
<SerialDetail SecondarySerial1="" />
<OrderLine >
<OrderLineTranQuantity ReceivedQty=""/>
</OrderLine>
</ReceiptLine>
<ReceiptLine ItemID="10000901" PrimeLineNo="" CountryOfOrigin="" DispositionCode="" OrderNo="Y100000351" Quantity="5" ReceiptLineNo="" >
<SerialDetail SecondarySerial1="" />
<OrderLine >
<OrderLineTranQuantity ReceivedQty=""/>
</OrderLine>
</ReceiptLine>
</ReceiptLines>
</Receipt>

Are you not loading the xpaths from your xml input stage? Sounds like you need to re-import the xml table definitions. You have to define /Receipt/ReceiptLines/ReceiptLine as a field.
You can use that as your repetitive key. If you use the xml import tool, you can pick and choose what fields you want.
See if that works for you.

It worked now..............

Thanku.......

pavans · Post by **pavans** » Mon Mar 17, 2008 3:21 pm

eostic wrote:Just follow what I put in the entry above. Try it in a simple test. Have only one column on your output link.... call it "FRED". Make it varchar and length of 300. Put the xpath above "as is" into the Description. Make it the key. Send it anywhere. If your xml is truly as you have above, you will get two rows.

Ernie

Hi Ernie...
The previous XML file worked fine.
I have another XML File......with the below structure.
I have designed the same way which u said.
Two rows are getting loaded but.....the same data coming twice.
i.e., first recorditself is getting repeated.
I have taken a new field "FRED"---
XPATH is /Order/OrderLines/OrderLine/OrderLineTranQuantity/

But its not solving the issue..
Please help me on the same.

<?xml version="1.0" encoding="UTF-8" ?>
- <Order EnterpriseCode="Target.com" DocumentType="Purchase Order" OrderType="TTR" BuyerOrganizationCode="Target.com" SellerOrganizationCode="116472" OrderNo="5000311" Status="Draft" ReqShipDate="" ReqCancelDate="" ReqDeliveryDate="" SearchCriteria1="1" CustomerPONo="12" OrderDate="" Action="CREATE" ShipNode="Hebron" ShipToID="WHSE_5" ReceivingNode="021" Createuserid="TargetTTR" CreateUserName="TagetTTR" Modifyts="">
- <OrderLines>
- <OrderLine PrimeLineNo="1" OrderedQty="3" Action="CREATE" Status="Draft Order Created" PersonalizeFlag="Y">
<OrderLineTranQuantity ReceivedQty="3" />
- <Extn ExtnIsForceRevieved="Y" ExtnASIN="Y" ExtnDPCI="N" ExtnVendorStyleNo="13213" ExtnTCBI="N" ExtnCasePackQuantity="12" ExtnForceRecievedQuantity="1" ExtnTotalRetail="1233.000" ExtnPOReadiness="N" ExtnTariffNo1="2222" ExtnTariffNo2="" ExtnTariffNo3="" ExtnTariffNo4="" ExtnTariffNo5="" ExtnIsAssortmentItem="N" ExtnIsKitItem="Y" ExtnMinOrderQuantity="2" ExtnPrepCode="Y" ExtnVisaNo1="1233" ExtnVisaNo2="" ExtnVisaNo3="" ExtnVisaNo4="" ExtnVisaNo5="" ExtnColor="green" ExtnSize="45">
- <Documents>
<Document Name="" Value="" />
</Documents>
</Extn>
<Item ItemID="1" UnitOfMeasure="EACH" ItemShortDesc="abc" UPCCode="123456789125" />
<LinePriceinfo PricingUOM="EACH" UnitCost="100.000" RetailPrice="100.000" />
<LineOverallTotals LineTotal="100.000" />
- <OrderDates>
<OrderDate CommittedDate="" />
</OrderDates>
- <Notes>
<Note NoteText="" />
</Notes>
</OrderLine>
- <OrderLine PrimeLineNo="2" OrderedQty="3" Action="CREATE" Status="Draft Order Created" PersonalizeFlag="Y">
<OrderLineTranQuantity ReceivedQty="2" />
- <Extn ExtnIsForceRevieved="Y" ExtnASIN="Y" ExtnDPCI="N" ExtnVendorStyleNo="13213" ExtnTCBI="N" ExtnCasePackQuantity="12" ExtnForceRecievedQuantity="1" ExtnTotalRetail="1233.000" ExtnPOReadiness="N" ExtnTariffNo1="2222" ExtnTariffNo2="" ExtnTariffNo3="" ExtnTariffNo4="" ExtnTariffNo5="" ExtnIsAssortmentItem="N" ExtnIsKitItem="Y" ExtnMinOrderQuantity="2" ExtnPrepCode="Y" ExtnVisaNo1="1233" ExtnVisaNo2="" ExtnVisaNo3="" ExtnVisaNo4="" ExtnVisaNo5="" ExtnColor="green" ExtnSize="45">
- <Documents>
<Document Name="" Value="" />
</Documents>
</Extn>
<Item ItemID="2" UnitOfMeasure="EACH" ItemShortDesc="abc" UPCCode="123456789125" />
<LinePriceinfo PricingUOM="EACH" UnitCost="100.000" RetailPrice="100.000" />
<LineOverallTotals LineTotal="100.000" />
- <OrderDates>
<OrderDate CommittedDate="" />
</OrderDates>
- <Notes>
<Note NoteText="" />
</Notes>
</OrderLine>
</OrderLines>
<Extn ExtnOrderSubType="Special" ExtnHotPO="N" ExtnReservePO="Y" ExtnRetransmit="None" ExtnTotalRetail="1234.000" ExtnPromiseDate="" ExtnShipFromID="021" ExtnContactEmail="rayg@1908studios.com" ExtnContactPhone="3458445" ExtnVendorName="1908 STUDIOS" ExtnTerms="term" ExtnIsEDI860Compliant="Y" ExtnIsEDI850Compliant="N" ExtnGMSVendorID="12" ExtnVendorNotes="This is reservePO" ExtnVendorActive="Y" ExtnImportVendor="N" ExtnPaymentType="" ExtnFOBTypeCode="" ExtnFOBCountry="US" ExtnFOBState="WS" ExtnFOBCity="New York" ExtnFOBZipCode="23423" ExtnFreightTerm="Term" ExtnVendorContactFirstName="Manohar" ExtnVendorContactAddressLine1="33 South" ExtnVendorContactAddressLine2="Downtown" ExtnVendorContactAddressLine3="" ExtnVendorContactCity="Minneapolis" ExtnVendorContactState="Minesotta" ExtnVendorContactZipCode="4354444444" ExtnVendorContactCountry="mn" ExtnVendorContactEmail="Manohar.Chakravarthy@target.com" ExtnVendorContactDayPhone="4343444" />
<OverallTotalsInEnterpriseCurrency LineSubTotal="10.000" />
- <Notes>
<Note NoteText="notes" />
</Notes>
<PersonInfoBillTo FirstName="Target Corporation" AddressLine1="33 South" AddressLine2="6th Street" City="MS CC-2325" ZipCode="55402" State="Minneapolis" Country="MN" />
<PersonInfoShipTo Company="Savannah" AddressLine1="756 Third St." AddressLine2="ABC" City="New York" ZipCode="23423" State="WS" Country="US" />
<PersonInfoMarkFor Company="Amazon" AddressLine1="12 East" AddressLine2="Block" City="Houston" ZipCode="12321" State="WW" Country="US" />
</Order>

jatayl · Post by **jatayl** » Mon Mar 17, 2008 3:40 pm

I'm not Ernie, but I would use <OrderLine> as your repetitive key.

pavans · Post by **pavans** » Mon Mar 17, 2008 6:32 pm

jatayl wrote:I'm not Ernie, but I would use <OrderLine> as your repetitive key.

Thanku for the reply...........I will try that and reply back soon.....

pavans · Post by **pavans** » Mon Mar 17, 2008 6:46 pm

jatayl wrote:I'm not Ernie, but I would use <OrderLine> as your repetitive key.

I tried but it didnt work out.
same row is repeated twice.
my "FRED" XPath Description is:
/Order/OrderLines/OrderLine/

Is there any other way...........

eostic · Post by **eostic** » Mon Mar 17, 2008 8:57 pm

Ok...first of all, let's make sure that the technique in the "original" issue is fully understood. This is where the column FRED was suggested. The goal is to get you comfortable with the repetition element concept... I hope it was clear. You should have received two rows, as you confirmed.....most of the attributes in your example have identical or blank values --- however, your ItemID's did appear to be unique.

Not sure which columns you are looking at in the XML content below, but for OrderLineTranQuantity there are at least two rows, one with the ReceivedQty of "2" and the other with value "3".

Ernie

DSXchange

Problem loading from XML to Sequential File

Problem loading from XML to Sequential File

Re: Problem loading from XML to Sequential File

Re: Problem loading from XML to Sequential File