FlatFile xs:Date field with an empty or NULL value

I have seen this issue a couple of times and thought of recording this extract from one of blog.

Exception:

After generating a flat file schema with optional elements(minoccurs =0) of type date,int,decimal etc., which does not accept a ‘ ‘ value, we might endup with an error as follows.

error BEC2004: The ‘JoinDate’ element is invalid – The value ” is invalid according to its datatype ‘http://www.w3.org/2001/XMLSchema:date’ – The string ” is not a valid XsdDateTime value.

Cause:

This exception is perfectly valid from the compiler’s perspective. Here comes the example.

Suppose my flatfile schema is generated using the following imput and is demilited by ‘|’ .

1|Name|2010-02-03|Description

As the date field(highnlighted in red) is optional in our scenario, the following input is also valid.

1|Name||Description

But when BizTalk validates this instance against the schema, it just picks the value between the two ‘|’ symbols for the date field which is ”. Thus it throws the error when it checks if the value ” is valid for a date field. This will occur for any datatype which does not accept ” value.

Solution:

The Solution is to logically let BizTalk know priorly that the datefield is optional and it has to ignore if any blank values occur.

This can be done by setting the “Suppress Empy Nodes” Property to “No” at the Schema Level(By Clicking on the word ”Schema” above the root node of the flatfile).

This will ensure that BizTalk knows that the field might have empty value and it should ignore it. This will get your validation successful.

------------------------------------------------------------------------------

public string ParseDate(string parameter1)

    {
          if (!(System.String.IsNullOrEmpty(parameter1)))
    {
          System.DateTime dt;
                if(DateTime.TryParse(parameter1, out dt)){
                return dt.ToString("s");
                }else{ return "";}
     }
else
{
return parameter1;
}
}

----------------------------------------------------------------------------------------------

xsl:if test="$paramDate != ''">