I am using xmlExtract component to read an XML which has a date tag.
<cover>
<country>US</country>
<pack>2093780</pack>
<date>20110604</date>
</cover>
I am trying to read the date tag (which is a string) as a ‘date’ type by changing the type in metadata to ‘date’. So initially when I had not set the right format I, understandably, got the below error
date (date) cannot be set to "20110609" - doesn't match defined format "yyyy-MM-dd"; value: '20110609'
But when I set the format to ‘yyyymmdd’ then I got some error like this
date (date) cannot be set to "Sun Jan 09 00:04:00 EST 2011" - doesn't match defined format "yyyymmdd"; value: 'Sun Jan 09 00:04:00 EST 2011'
{This component connect to DBOutputTable but I don’t think that would matter}
So my question is what is the right (and the best) way to read a string as date and save in DB. (I don’t think I need a ‘Reformat’ component for this (I think metadata should be able to handle it)
Hi,
Which version of CloverETL do you have, please? I tried it for myself and was able to read the XML.
However, there is a fact you should be aware of. There is a difference between “yyyyMMdd” and “yyyymmdd”. (months vs. minutes) I was able to use both these formats but both mean different date and time when used with string “20110604”.
First, please try to read your XML and save the data with UniversalDataWriter (UDR). Only after you are sure the date has the correct value and format, replace UDR with DBOutputTable.
Regards,
Thank you for looking into my query
Version: community version 3.5.0.058
I have connected the xmlExtract component now to ‘Trash’ and ensured that I am using ‘yyyyMMdd’ but I am still getting error.
date (date) cannot be set to "[b]Thu Jun 09 00:00:00 EDT 2011[/b]" - doesn't match defined format "yyyyMMdd"; value: 'Thu Jun 09 00:00:00 EDT 2011'
My mapping is also very simple
<Mapping element="date" useParentRecord="true"
xmlFields="../{}date"
cloverFields="date">
</Mapping>
And in the metadata, I didn’t change anything except for changing the type to ‘date’ and format to ‘yyyyMMdd’
Is there anything else that I can share to help debug this issue?
Some piece of information is still missing, some setting is still different between our two Designers. To speed things up, could you please send me your graph and input file?
Thanks,
Here is the XML
<?xml version="1.0"?>
<catalog>
<book id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer's Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>20110609</publish_date>
<description>An in-depth look at creating applications
with XML.</description>
</book>
</catalog>
And here is the Graph
<?xml version="1.0" encoding="UTF-8"?>
<Graph author="AMITG" created="Fri Apr 11 18:58:31 EDT 2014" guiVersion="3.5.0" id="1397265773941" licenseType="Community" modified="Fri Apr 11 21:26:35 EDT 2014" modifiedBy="AMITG" name="Test" revision="1.3" showComponentDetails="true">
<Global>
<Metadata id="Metadata0" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter="|" name="recordName1" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">
<Field name="id" type="string"/>
<Field format="yyyyMMdd" label="publish_date" name="publish_date" type="date"/>
</Record>
</Metadata>
<GraphParameters>
<GraphParameterFile fileURL="workspace.prm"/>
</GraphParameters>
<Dictionary/>
</Global>
<Phase number="0">
<Node enabled="enabled" guiName="Trash" guiX="482" guiY="162" id="TRASH0" type="TRASH"/>
<Node enabled="enabled" guiName="XMLExtract" guiX="243" guiY="162" id="XMLEXTRACT" schema="${META_DIR}\catalog.xsd" sourceUri="C:/Users/amitg/Desktop/Catalogue.xml" type="XML_EXTRACT">
<attr name="mapping"><![CDATA[<Mappings>
<Mapping element="catalog">
<Mapping element="book" outPort="0"
xmlFields="{}id"
cloverFields="id">
<Mapping element="publish_date" useParentRecord="true"
xmlFields="../{}publish_date"
cloverFields="publish_date">
</Mapping>
</Mapping>
</Mapping>
</Mappings>
]]></attr>
</Node>
<Edge debugMode="true" fromNode="XMLEXTRACT:0" guiBendpoints="" guiRouter="Manhattan" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="TRASH0:0"/>
</Phase>
</Graph>
Error
------------------------------------------------------------------- Error details ---------------------------------------------------------------------
Component [XMLExtract:XMLEXTRACT] finished with status ERROR. (Out0: 0 recs)
publish_date (date) cannot be set to "Thu Jun 09 00:00:00 EDT 2011" - doesn't match defined format "yyyyMMdd"; value: 'Thu Jun 09 00:00:00 EDT 2011'
Unparseable date: "Thu Jun 09 00:00:00 EDT 2011" at position 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
{I hope it’s not a bug because that will be very disappointing}
To be honest, there IS a bug but just a minor one. You set the XMLExtract mapping in an unexpected way and that showed us the bug located elsewhere. However, if you set it properly, everything will work ok.
Please see the following graph. publish_date is not populated into a parent record anymore, it is mapped directly as a child of the element book. This is the correct way in this case.
<?xml version="1.0" encoding="UTF-8"?>
<Graph author="AMITG" created="Fri Apr 11 18:58:31 EDT 2014" guiVersion="3.5.0" id="1397265773941" licenseType="Community" modified="Wed Apr 16 15:02:48 CEST 2014" modifiedBy="user" name="Test" revision="1.19" showComponentDetails="true">
<Global>
<Metadata id="Metadata0" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter="|" name="recordName1" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">
<Field name="id" type="string"/>
<Field format="yyyyMMdd" label="publish_date" name="publish_date" type="date"/>
</Record>
</Metadata>
<GraphParameters>
<GraphParameterFile fileURL="workspace.prm"/>
</GraphParameters>
<Dictionary/>
</Global>
<Phase number="0">
<Node enabled="enabled" guiName="Trash" guiX="482" guiY="162" id="TRASH0" type="TRASH"/>
<Node enabled="enabled" guiName="XMLExtract" guiX="243" guiY="162" id="XMLEXTRACT" schema="${META_DIR}\catalog.xsd" sourceUri="${DATAIN_DIR}/in.xml" type="XML_EXTRACT">
<attr name="mapping"><![CDATA[<Mappings>
<Mapping element="catalog">
<Mapping element="book" implicit="false" outPort="0"
xmlFields="{}id;{}publish_date"
cloverFields="id;publish_date">
</Mapping>
</Mapping>
</Mappings>
]]></attr>
</Node>
<Edge debugMode="true" fromNode="XMLEXTRACT:0" guiBendpoints="" guiRouter="Manhattan" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="TRASH0:0"/>
</Phase>
</Graph>
By the way, I am going to report the bug to our developers to prevent any confusion in the future. Thank you for the report.
Regards,
Well I am glad I could help in discovery of a bug. Plus I learnt another way of mapping which works for me.
Thanks for helping