DateFormat conversion in tXMLMap - Document type

[Anonymous]

I have a XML file that needs to be converted to CSV. I am using tfileinputXML --> tXMLMap --> tFileoutput

But 

When I try to map the date in output it is showing as :

ShipDate

########

I tried to convert the date to string and pass the value as string "TalendDate.formatDate("YYYY/MM/dd",[row2.Shipment:/Shipment/Header/ShipmentHeader/ShipDate]) "

. But I am getting date as

ShipDate

0019/09/07

The input file xml has the date: <ShipDate>2018-03-14</ShipDate>.

Please let me know if there is a work around.

Thanks

[akumar2301]

What if you map it as String not as Date ?

[Anonymous]

It is throwing an error cannot convert from Date to string.

[Anonymous]

Hi,

    I would suggest to parse the data as string from XML file (and making the output also as string) and check the results in a tlogrow. I believe the data is not coming correctly for the column or the format is changing for some records of the input XML.

    Once you are absolutely sure that parsing of XML is perfect and there are not format issues, then you can plan for the date conversion. Either you can do it in same step by converting the column to date or you can do the date conversion in a followup step so that you can check the changes happening in each step.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Hi Nikhil,

I am not able to parse the column as string. It is throwing an error saying cannot convert from date to string.

Thanks

SK

[Anonymous]

Hi,

   Could you please share a sample XML record for testing?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

<?xml version="1.0" encoding="utf-8"?>
<Shipments xmlns="http://www.spscommerce.com/RSX">
<Shipment>
<Header>
<ShipmentHeader>
<TradingPartnerId>SPSALLTESTID</TradingPartnerId>
<ShipmentIdentification>Sh123546-1</ShipmentIdentification>
<ShipDate>2018-03-14</ShipDate>
<TsetPurposeCode>00</TsetPurposeCode>
<ShipNoticeDate>2018-03-12</ShipNoticeDate>
<ShipNoticeTime>14:22:14</ShipNoticeTime>
<ASNStructureCode>0001</ASNStructureCode>
<BillOfLadingNumber>123546879</BillOfLadingNumber>
<CarrierProNumber>123546879</CarrierProNumber>
<AppointmentNumber>12354</AppointmentNumber>
<CurrentScheduledDeliveryDate>2018-03-15</CurrentScheduledDeliveryDate>
<CurrentScheduledDeliveryTime>16:22:14</CurrentScheduledDeliveryTime>
</ShipmentHeader>
<Dates>
<DateTimeQualifier>002</DateTimeQualifier>
<Date>2018-03-15</Date>
<Time>16:22:14</Time>
</Dates>
<References>
<ReferenceQual>AN</ReferenceQual>
<ReferenceID>5987-1</ReferenceID>
<Description>Customer Contract Number "Typically return whatever References are received on PO"</Description>
</References>
<Notes>
<NoteCode>GEN</NoteCode>
<Note>This will only be sent when you have notes</Note>
</Notes>
<Contacts>
<ContactTypeCode>IC</ContactTypeCode>
<ContactName>SPS Commerce Buyer</ContactName>
<PrimaryPhone>612-435-9400</PrimaryPhone>
<PrimaryFax>612-435-9468</PrimaryFax>
<PrimaryEmail>info@spscommerce.com</PrimaryEmail>
</Contacts>
<Address>
<AddressTypeCode>SF</AddressTypeCode>
<LocationCodeQualifier>91</LocationCodeQualifier>
<AddressLocationNumber>123546</AddressLocationNumber>
<AddressName>Warehouse</AddressName>
<Address1>1 Address Way</Address1>
<Address2>Suite A</Address2>
<City>Minneapolis</City>
<State>MN</State>
<PostalCode>55402</PostalCode>
<Country>USA</Country>
</Address>
<Address>
<AddressTypeCode>ST</AddressTypeCode>
<AddressName>SPS Commerce</AddressName>
<Address1>333 S 7th Street</Address1>
<Address2>Suite 1000</Address2>
<City>Minneapolis</City>
<State>MN</State>
<PostalCode>55402</PostalCode>
<Country>USA</Country>
</Address>
<CarrierInformation>
<StatusCode>CL</StatusCode>
<CarrierTransMethodCode>P</CarrierTransMethodCode>
<CarrierAlphaCode>UPSN</CarrierAlphaCode>
<CarrierRouting>UPS Ground</CarrierRouting>
</CarrierInformation>
<QuantityAndWeight>
<PackingMedium>CTN</PackingMedium>
<PackingMaterial>25</PackingMaterial>
<LadingQuantity>1</LadingQuantity>
<WeightQualifier>G</WeightQualifier>
<Weight>5</Weight>
<WeightUOM>LB</WeightUOM>
</QuantityAndWeight>
<FOBRelatedInstruction>
<FOBPayCode>CC</FOBPayCode>
<FOBLocationQualifier>DE</FOBLocationQualifier>
<FOBLocationDescription>FOB Destination</FOBLocationDescription>
</FOBRelatedInstruction>
<QuantityTotals>
<QuantityTotalsQualifier>SQT</QuantityTotalsQualifier>
<Quantity>1</Quantity>
<QuantityUOM>EA</QuantityUOM>
</QuantityTotals>
</Header>
<OrderLevel>
<OrderHeader>
<PurchaseOrderNumber>PO584616-1</PurchaseOrderNumber>
<ReleaseNumber>456</ReleaseNumber>
<PurchaseOrderDate>2016-03-12</PurchaseOrderDate>
<Department>SPS</Department>
<Vendor>VEND6174</Vendor>
<CustomerOrderNumber>98745612</CustomerOrderNumber>
</OrderHeader>
<QuantityAndWeight>
<PackingMedium>CTN</PackingMedium>
<PackingMaterial>25</PackingMaterial>
<LadingQuantity>1</LadingQuantity>
<WeightQualifier>G</WeightQualifier>
<Weight>5</Weight>
<WeightUOM>LB</WeightUOM>
</QuantityAndWeight>
<PackLevel>
<Pack>
<PackLevelType>P</PackLevelType>
<ShippingSerialID>00007774760000006929</ShippingSerialID>
<CarrierPackageID>00007774760000006929</CarrierPackageID>
</Pack>
<PhysicalDetails>
<PackQualifier>OU</PackQualifier>
<PackValue>1</PackValue>
<PackSize>1</PackSize>
<PackUOM>EA</PackUOM>
<PackingMedium>CTN</PackingMedium>
<PackingMaterial>25</PackingMaterial>
</PhysicalDetails>
<ItemLevel>
<ShipmentLine>
<LineSequenceNumber>1</LineSequenceNumber>
<BuyerPartNumber>SPSBuyerItem1</BuyerPartNumber>
<VendorPartNumber>SPSSupplierHD-20</VendorPartNumber>
<ConsumerPackageCode>859563003863</ConsumerPackageCode>
<GTIN>10859563003863</GTIN>
<UPCCaseCode>496099966745</UPCCaseCode>
<ProductID>
<PartNumberQual>MG</PartNumberQual>
<PartNumber>9565452</PartNumber>
</ProductID>
<OrderQty>1</OrderQty>
<OrderQtyUOM>EA</OrderQtyUOM>
<PurchasePrice>20</PurchasePrice>
<ItemStatusCode>IA</ItemStatusCode>
<ShipQty>1</ShipQty>
<ShipQtyUOM>EA</ShipQtyUOM>
<ProductSizeCode>S-800</ProductSizeCode>
<ProductSizeDescription>Small</ProductSizeDescription>
<ProductColorCode>C-999</ProductColorCode>
<ProductColorDescription>Fire Truck Red</ProductColorDescription>
<ProductMaterialDescription>Faux Fabric</ProductMaterialDescription>
<NRFStandardColorAndSize>
<NRFColorCode>533</NRFColorCode>
<NRFSizeCode>10185</NRFSizeCode>
</NRFStandardColorAndSize>
</ShipmentLine>
<PhysicalDetails>
<PackQualifier>IN</PackQualifier>
<PackValue>1</PackValue>
<PackSize>1</PackSize>
<PackUOM>EA</PackUOM>
</PhysicalDetails>
<PriceInformation>
<PriceTypeIDCode>RTL</PriceTypeIDCode>
<UnitPrice>60</UnitPrice>
</PriceInformation>
<ProductOrItemDescription>
<ProductCharacteristicCode>08</ProductCharacteristicCode>
<ProductDescription>Sample Description1</ProductDescription>
</ProductOrItemDescription>
<Dates>
<DateTimeQualifier>037</DateTimeQualifier>
<Date>2018-03-12</Date>
<Time>14:22:14</Time>
</Dates>
<References>
<ReferenceQual>MR</ReferenceQual>
<ReferenceID>MerchandiseTypeCode</ReferenceID>
</References>
<Notes>
<NoteCode>GEN</NoteCode>
<Note>Gift Message: Happy Birthday!</Note>
</Notes>
</ItemLevel>
</PackLevel>
</OrderLevel>
<Summary>
<TotalLineItemNumber>1</TotalLineItemNumber>
</Summary>
</Shipment>
</Shipments>

[Anonymous · Accepted solution]

Hi,

    This helped to find the issue.

     Please change the date pattern under tree schema editor to your desired pattern and it should resolve your issue. The issue happened because it was in the default format.

If the answer has resolved, please spend a second to mark the topic as resolved 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Super.. Thank you so much Nikhil. It has worked. 

Thanks Again.