TimeXtender REST 6.1.0.0: NULLs become empty strings

I thought this was made an idea already, but couldn’t find it.

Hi @Thomas Lind

Do you have any updates here?

It’s causing problems every time a non-varchar column gets a new row containing a null value. The REST connector returns an empty string and we get a data type error. Essentially, all nullable columns must be varchar to avoid this error, but we are not able to change data types manually in ODX and have to wait for the null value to occur. This is of course not good for the stability of the jobs that are running in production.

Hi @sigsol 

Do you use version 6.3.0.0 when you experience this?

@Thomas Lind  Yes, happening on 6.3.0.0 as well

Hi @sigsol 

OK, it was just to confirm that it wasn’t already made. I will update you once I know more.

Thanks!

Hi @sigsol 

I got a answer for a sort of similar issue. I wasn’t sure it could be a solution to this, but the following could be a workaround.

There is an immediate fix they can do by manually editing their XSLT to remove the element if it is NULL or empty string. In the XSLT that is generated there will be a list of elements that look like this:

<xsl:element name="title">
   <xsl:value-of select="title" />
</xsl:element>

if they change this to have an If around them:

<xsl:if test="title != ''">
   <xsl:element name="title">
      <xsl:value-of select="title" />
   </xsl:element>
</xsl:if>

If you add this to the table flattening, does it make a difference?

Hi @Thomas Lind 

Thanks, we have tried something like this and I believe it could work in principle. Unfortunately, we have so many REST sources that it’s not feasible to do this for all nullable columns in the dwh and also maintain all the XSLT’s when new columns appear etc. 

Having the same issue, keen to see this solved in a new release of the TX Rest API source

If it helps anyone I created a RegEx expression to scan through an entire XLST and replace all the element definitions with the Null test encapsulation as above.

RegEx Find:
<xsl:element name="(\w+)">\s*<xsl:value-of select="\1"\s*/>\s*</xsl:element>
RegEx Replace:
<xsl:if test="$1 != ''">\n\t<xsl:element name="$1">\n\t\t<xsl:value-of select="$1"/>\n\t</xsl:element>\n</xsl:if>

Hi ​@Paul McLeod Bespoke Analytics 

• The TimeXtender REST data source provider now handles empty property names, property names that start or end with a colon, and property names with more than one colon.

What version are you in where you need to use these to make it work, or do these conversions go beyond what has been changed in the newest release?

Hi Thomas, we are using Ingestion instance 6766.1 and the 7.1.0.0 TX Rest API datasource.
The challenge we are having is that the API passes a value of “null” to represent an empty value.

so below you can see that numeric values such as “aggregate_limit” are retained in the JSON output, but instead of having a “0” have a null when empty. The API also does this for empty string nodes in the JSON (i.e. “customer_name”: null)

So at the moment if I set the numeric fields to number data type override they are getting errors on load when a record has an empty value.

If I leave them as nvarchar(500) as the default is set to then I have to manually handle setting datatypes when loading the transformation stage. would be nice to have these recognised as null values and not a string. Or have a way to indicate the fact that a particular string represents a null value.

                "aggregate_limit": null,
                "attritional_lr": 0.5069,
                "business_details": {
                    "cob": "Directors Officers Liability",
                    "lob": "Various in COB"
                },
                "cat_lr": 0.0,
                "ceded": 1.0,
                "claim_trend": "UK FinPro - Medium",
                "cov": 0.33851752392730355,
                "default_claim_trend": null,
                "event_limit": null,
                "excess": null,
                "fixed_rate": 0.02,
                "large_lr": 0.0,
                "lr_cap": null,
                "lr_cap_impact": null,
                "orca_layer": "04829",
                "rate_change": "Directors Officers Liability - International",
                "risk_limit": 25000000,
                "section": "UK D&O",
                "selected_lr": 0.6596975107327213,
                "subject_premium": 32668013.0,
                "total_lr": 0.5069

Hi all

This option was added in the 6892.1 release. TX REST version 7.0.0.0 and forward.

Hi Thanks Thomas!

Yes we have seen and are making extensive use of the feature!